0101 0101 - 7 months ago 8
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
id
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
id
to be returned. How can I do this?

Answer
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

Comments