2

I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this:

select A.id from A left join B on B.id_A = A.id where B.id_A is null;

And it works perfectly well. However now I need to do the same query with an additional conditional for the matching, meaning I need to find the ids of all rows in a table A without a matching row on table B whose column c has the value x. If I try something like:

select A.id from A left join B on B.id_A = A.id where B.id_A is null and B.c = x;

It obviously gives me an empty result set.

So far, the only way I figured to do that is using a subquery for B with a "not exists" clause:

select A.id from A where not exists (select id from B where B.id_A = A.id and B.c = x);

Any ideas on how to do this with a join without using subqueries?

2
SELECT A.id 
  FROM A 
  LEFT JOIN B ON B.id_A = A.id AND B.c = x 
 WHERE B.id_A is null;

Moving the test of B.c into the join condition and out of the where clause causes it to be used to only eliminate non-matching B-rows from consideration for the left join rather than eliminating rows from the result.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.