CODEWITHSUNDEEP
mysqlsql
0101
0101

Reputation: 2707

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?

Upvotes: 2

Views: 873

Answers (3)

Mogana
Mogana

Reputation: 262

select id from table where id not in(1,2,3,4,5,6)

Upvotes: 0

user330315
user330315

Reputation: 

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

Upvotes: 4

Ricky_W
Ricky_W

Reputation: 41

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

The return ids are not exist .

Upvotes: 0

Related Questions