Which values are missing in SQL from a list?

Question

I have a list of values, say 1,2,3,4,5,6. I have a SQL table with a field storing these values but only three rows, say, 2,4,5. What sort of query will return three rows 1,3,6 for me? (storing all six in another table and left join'ing is cheating.)

Answer 1

You didn't state your DBMS, so this is the ANSI SQL Version:

with the_values (id) as ( 
  values (1),(2),(3),(4),(5),(6)
)
select v.id 
from the_values v
  left join the_real_table t on t.id = v.id
where t.id is null;