Query difference with where condition

Question

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?

Answer 1

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.