In one table, but not the other

[Anon]

I have two tables, one that contains a master list of items and a second table that contains a subset of the master table.

Using mySql, what does the SQL look like for a query that finds the items in the master table that are NOT in the second table?

[Anon]

This has already been asked. Please search the archive before posting the next time.

select n from t1 where n not in (select n from t2);

Won't work if your database does not support nested queries, like MySQL.

[kparker]

Umm, he might well answer "please read before you post" since he quite clearly said it's in regard to mysql.

As far as how to work around the no-nested-query limitations, the only thing I can think of immediately is extremely ugly: create a temporary table, select all the rows from the master table into it, and then delete all the rows that are in the subset table.

Ugly. :-( Hopefully someone else has a better idea.

[[deleted]]

I do! :-)

Manual-man to the resque!

http://www.mysql.com/doc/J/O/JOIN.html

-- quote --
If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You
can use this fact to find records in a table that have no counterpart in another table:

  mysql> select table1.* from table1
             LEFT JOIN table2 ON table1.id=table2.id
             where table2.id is NULL;

-- end quote --

[Anon]

Thanks to all who replied. Believe me, I tried every word combination I could think of in the search engine BEFORE I posted. But to no avail....

[[deleted]]

Well, pooh happens :-)

[Anon]

Way to go Vincent!

[[deleted]]

giggle