Compare two SQL tables and return missing ids?

Question

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

table1:

table2:

id
2
4

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

any ideas? :) TY

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, Nov 03 '11 at 16:01
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.) — , Nov 03 '11 at 16:09

James Hill · Accepted Answer · 2011-11-03T16:14:18.723

42

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

edited Nov 03 '11 at 16:14

answered Nov 03 '11 at 16:00

James Hill

I'd suggest you use table name prefixes in the first query to clarify which ID field you mean, if only for the reason that it would get very confusing to read if you expanded it later. – Polynomial Nov 03 '11 at 16:02

score 2 · Answer 2 · answered Nov 03 '11 at 16:00

2

select t1.*
from table1 t1
left outer join table2 t2 on t1.id = t2.id
where t2.id is null

answered Nov 03 '11 at 16:00

D'Arcy Rittich

1

@Raihan Because the LEFT OUTER may join "no rows" from T2 for a given T1 (and this is the lynchpin for this approach). The missing rows are such rows that are ... missing :-) – Nov 03 '11 at 16:07

score 1 · Answer 3 · answered Nov 03 '11 at 16:01

1

Try this:

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

answered Nov 03 '11 at 16:01

Polynomial

3 Answers3