Postgresql - how to find out which words do not match in the IN section?

Question

I have a query like this (in postgresql ):

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

let's say <string1>- IN is some value, but <string2>no. How can I get all the values ​​shown in brackets that are not in somevalue column?

thank;)

Answer 1

For this, you probably need a separate request. If you have PostgreSQL 8.4 or later, you can use unnest:

 SELECT unnest(ARRAY['<string1>','<string2>', ...]) as element
  WHERE element not in
 SELECT somevalue FROM tablexy

Answer 2

This solution will work in earlier versions of PostgreSQL (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;

, somevalue, Pablo, .

:

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

, :

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

, !

Answer 3

I would use an outer join, RIGHT JOIN in this example:

SELECT
    element
FROM
    tablexy 
        RIGHT JOIN (SELECT UNNEST(ARRAY['<string1>','<string2>']) AS element) AS input ON element = somevalue 
WHERE
    somevalue IS NULL;

For performance, you can use the "somevalue" index. Check EXPLAIN to see query and index usage.

Answer 4

SELECT * FROM tablexy WHERE somevalue NOT IN (...)

Does NO mean your needs?