Rows in a table that have no counterpart in another two tables?

Question

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

The code above works beautifully when you are searching rows from left table without a counterpart in one right table.

But how do I find rows from left table without a counterpart in two right tables?

Answer 1

If I understand correctly, you want to find records in LEFT_TBL that doesn't exist in both of the two other tables? The most readable would be:

SELECT a.*
  FROM LEFT_TBL lt
 WHERE NOT EXISTS(SELECT NULL
                    FROM TABLE_A a
                   WHERE a.id = lt.id)
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE_B b
                   WHERE b.id = lt.id)

Using LEFT JOIN/IS NULL:

   SELECT lt.*
     FROM LEFT_TBL lt
LEFT JOIN TABLE_A a ON a.id = lt.id
LEFT JOIN TABLE_B b ON b.id = lt.id
    WHERE a.id IS NULL
      AND b.id IS NULL

If you want LEFT_TBL records that do not exist in one of the two tables, change the AND in the WHERE clause to OR.