postgresql - how to find out which words did not match for IN condition?

Question

I have a query like that (in postgresql):

SELECT * 
  FROM tablexy 
 WHERE somevalue IN ("<string1>", "<string2>", "<...>", ... )

say, <string1> is IN somevalue but <string2> not. How can I get all the values given in the brackets which are not IN the somevalue column?

thanks ;)

Answer 1

This solution will work in earlier PostgreSQL versions (at least 8.2) and also faster than Pablo's answer:

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
LEFT JOIN tablexy ON (tablexy.somevalue=list.listvalue)
WHERE tablexy.somevalue IS NULL;

In my testing this executes in a millisecond on a table with a million rows if there's an index on somevalue, unlike Pablo's which takes seconds.

This might be faster in some cases:

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
WHERE NOT EXISTS (SELECT 1 FROM tablexy where somevalue=listvalue);

Here's a slow but obvious approach:

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
EXCEPT
SELECT somevalue FROM tablexy;

Hope this helps!