Compare two SQL tables and return missing ids?

11
votes

I have two simple tables: (here only the "id" column)

table1:

id
1
2
3
4

table2:

id
2
4

the sql query should compare the two tables for missing "id" in table2 and return: 1,3

any ideas? :) TY

3
mysqlsqlexcept
So far, all the answers only show what ids in table1 are missing in table2. Do you have any need for a query that would return all the ones in table2 that are missing in column1 as well? – Paul Tomblin
hi Paul, no - one way only is what i need. – MilMike
The keyword is EXCEPT. MySQL does not support this operation, but using it in searches will return lots of results for work-abouts and additional approaches. (This question comes up from time to time.) – user166390

3 Answers

38
votes

There are several ways to skin this cat:

SELECT    table1.ID
FROM      table1
WHERE     table1.ID NOT IN(SELECT table2.ID FROM table2)

Or you could use a left outer join:

SELECT          table1.ID
FROM            table1
LEFT OUTER JOIN table2 ON table1.ID = table2.ID
WHERE           table2.ID IS NULL
2
votes
select t1.*
from table1 t1
left outer join table2 t2 on t1.id = t2.id
where t2.id is null
1
votes

Try this:

SELECT    table1.id
FROM      table1
WHERE     table1.id NOT IN(SELECT table2.id FROM table2)