All Forums Database
Ibnfarooq 1 post Joined 06/15
03 Jun 2015
SQL query to find given IDs not in table

Consider there is table with some ids. I want to search the ids that are not available in that table.
For eg: The table
id
--------
1991
1992
 
As of now, am writing this query
"Select * from table_name where id in (1991,1992,1993)"
From the result, I am manually finding out that 1993 is missing.
Is there any query to achieve it? That query should directly give me 1993.

dnoeth 4628 posts Joined 11/04
03 Jun 2015

You need to split the list of values into rows:

WITH cte AS   
 (
   SELECT CAST(token AS INT) AS id
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '1991,1992,1993', ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(20) CHARACTER SET UNICODE)
              ) AS d 
 )
SELECT * FROM cte
WHERE NOT EXISTS 
 ( SELECT * FROM tab AS t WHERE t.id = cte.id)

 

Dieter

You must sign in to leave a comment.