Which records aren't in list? Use static list as table or records

[Durumdara <durumdara@xxxxxxxxx>]

Hi!

A very silly question. I have a limited list of identifiers.

I want to know which one IS NOT in a table.

The

select * from theserecords where id not in (1, 5, 12, 33, 55)

isn't listing missing records... because they are missing... :-)

For example, a pseudo:

idlist = (1, 5, 12, 33, 55)

select id from idlist

where id not in (select id from theserecords)

The id list is a static string.

Now I can do this with temporary table - I create one, insert the ID-s and run the select:

select id from temptable where id not in (select id from theserecords)

It would be nice if I can get the missing ID-s.

F.e:

select id from (

   select 1 as id, 

   select 5 as id,   

   ...

) where id not in (select id from theserecords)

or

select id from (
  select split_string_to_records('1,2,3,4,5', ',') as id

) ...

Do you know any simple way to do this without stored proc or temp table?

Thank you!

B.W:

    dd