how to get the missing values in SQL query when using in clause

Question

Suppose I have the following query :

select value from table where value in ('abc','cde','efg');

If only 'abc' is populated in the table, I want to be able to see which value is missing in the result set, so the results looks like :

cde
efg

Answer 1

You can use UNION ALL to get a resultset with all the values that you want:

SELECT 'abc' AS value FROM dual UNION ALL
SELECT 'cde' FROM dual UNION ALL
SELECT 'efg' FROM dual

(you may omit FROM dual depending on your database).
And with NOT EXISTS get all the values from the above resultset that do not appear in the table:

SELECT u.*
FROM (
  SELECT 'abc' AS value FROM dual UNION ALL
  SELECT 'cde' FROM dual UNION ALL
  SELECT 'efg' FROM dual 
) u
WHERE NOT EXISTS (SELECT 1 FROM tablename t WHERE t.value = u.value)