0101 0101 - 2 years ago 95
SQL Question

Return id of a non-existing record

I have an array of IDs:

[1, 2, 3, 4, 5, 6]

To check what records exist in the table I execute this query:

SELECT id FROM table WHERE id in (1, 2, 3, 4, 5, 6);

The query returns an
of every existing record but what if I want to know what records don't exist? For instance if a record with
id: 1
doesn't exist I want this
to be returned. How can I do this?

Answer Source
select idlist.id
from (
  select 1 as id
  union all select 2 
  union all select 3 
  union all select 4 
  union all select 5 
  union all select 6 
) as idlist
left join the_table
  on idlist.id = the_table.id
where the_table.id is null;

SQLFiddle example: http://sqlfiddle.com/#!2/b3a5b/1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download