There are two ways to find rows that exist in one table but not another.
The first method is to use a subquery:
SELECT id FROM table1 WHERE id NOT IN (SELECT id FROM table2);
This approach has the advantage of being more obvious to the casual reader, at the cost of portability (you need MySQL version 4.1 to use subqueries).
Alternately, you can use a left join:
SELECT table1.id FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) WHERE table2.id IS NULL;
This approach may execute faster than a subquery, and works in all versions of MySQL. It may lose clarity for those readers not familiar with table joins and null values.
Dig Deeper on Linux servers
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes. Continue Reading
A MySQL expert describes two ways that the multi-master support can be used. Continue Reading
An expert describes where to find information on detection deadlock algorithms in MySQL. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.