CODEWITHSUNDEEP
mysqlsqlselect
corgrath
corgrath

Reputation: 12275

How to get a list of IDs not present in a (My)SQL table?

I have a MySQL table, that looks like this:

+----+
| id |
+----+
| a  |
| c  |
| e  |
+----+

I want to check which of the following ids a, b, c, d, e does not exist in the table.

Meaning, I want to get a list of the ids b and d back.

Is there a single query I can construct by hand that returns a list of IDs that does not exist in my table, without actually creating a new second permanent SQL table?

Upvotes: 3

Views: 2223

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is usually done using a left join or something like this:

select ids.id
from (select 'a' as id union all select 'b' union all select 'c' union all
      select 'd' union all select 'e'
     ) ids
where ids.id not in (select id from t);

If the list is already in a table, then you can use that table (or subquery) instead of a derived table.

Upvotes: 4

Related Questions