Postgress how to query with not in for multiple column-CodePudding

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
)

CodePudding user response：

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)

CodePudding user response：

If I understand correctly, you should be able to just do two consecutive left joins to the A and B tables. A valid match, then, is one for which neither table has any join match.

SELECT *
FROM table_c c
LEFT JOIN table_a a
    ON c.item_a_id = a.item_id
LEFT JOIN table_b b
    ON c.item_b_id = b.item_id
WHERE
    a.item_id IS NULL AND
    b.item_id IS NULL;

By the way, the above query is specifically called a left anti-join.