Postgress how to query with not in for multiple column

December 12, 2022

I want to select from table C all items ID where column A "item_a_id" and column B "item_b_id" not in result of another query.

currently I use this query twice , i didnt find a way how to query it without the using same query twice.

This is my query:

SELECT * from table_c c
WHERE c.item_a_id NOT IN 
(
 SELECT a.item_id,b.item_id 
 FROM table_a a 
 JOIN table_b b on a.item_id = b.item_id
) 
AND c.item_b_id NOT IN 
(
 SELECT a.item_id,b.item_id 
 FROM table_a a 
 JOIN table_b b on a.item_id = b.item_id
)

and this is how I want to improve it (ofc this is not sql syntax and just an example)

SELECT * from table_c c
WHERE c.item_a_id AND c.item_b_id NOT IN 
(
 SELECT a.item_id,b.item_id 
 FROM table_a a 
 JOIN table_b b on a.item_id = b.item_id
)

>Solution :

You need to enclose the columns on the left hand side in parentheses:

WHERE (c.item_a_id, c.item_b_id) 
      NOT IN (SELECT a.item_id,b.item_id 
              FROM table_a a 
               JOIN table_b b on a.item_id = b.item_id)

But typically NOT EXISTS conditions are faster than NOT IN

WHERE NOT EXISTS (SELECT *
                  FROM table_a a 
                    JOIN table_b b on a.item_id = b.item_id
                  WHERE a.item_id = c.item_a_id 
                    AND b.item_id = c.item_b_id)