Compare two SQL tables and return missing ids?

Question

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

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
– Paul Tomblin, Commented Nov 3, 2011 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.) — user166390
– user166390, Commented Nov 3, 2011 at 16:09

James Hill · Accepted Answer · 2011-11-03 16:14:18Z

44

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 3, 2011 at 16:14

answered Nov 3, 2011 at 16:00

James Hill

62.1k22 gold badges150 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Polynomial Over a year ago

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.

D'Arcy Rittich · Accepted Answer · 2011-11-03 16:00:30Z

2

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

answered Nov 3, 2011 at 16:00

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

1 Comment

user166390 Over a year ago

@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 :-)

Polynomial · Accepted Answer · 2011-11-03 16:01:49Z

1

Try this:

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

answered Nov 3, 2011 at 16:01

Polynomial

28.4k13 gold badges86 silver badges110 bronze badges

Collectives™ on Stack Overflow

Compare two SQL tables and return missing ids?

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Related