LEFT JOIN WHERE Clause IS NOT NULL

Question

I have two tables A and B Common column x

SELECT A.*
FROM A
LEFT JOIN B
ON A.x == B.x
WHERE A.z == "Hola" AND B.y IS NOT NULL;

I'm confused as to what output this would produce compared to EXIST or a LEFT JOIN WHERE Clause is null.

If I'm not mistaken this takes What's on A and common between A&B, but excluding what B is null, correct?

Answer 1

This is your query:

SELECT A.*
FROM A LEFT JOIN
     B
     ON A.x = B.x
WHERE A.z = 'Hola' AND B.y IS NOT NULL;

You need to decompose the query.

The result set will have only A.Z = 'Hola'. That is an important filter in the WHERE clause.

Then, B.y IS NOT NULL means two things: both that the match exists in B and that y is not NULL. So, this query is equivalent to:

SELECT A.*
FROM A INNER JOIN
     B
     ON A.x = B.x
WHERE A.z = 'Hola' AND B.y IS NOT NULL;

With an inner join, you could put the WHERE conditions in the ON. That is a matter of style. You cannot do this with a LEFT JOIN.

Then, the "equivalent" EXISTS query would be:

SELECT A.*
FROM A 
WHERE A.z = 'Hola' AND
      EXISTS (SELECT 1 FROM B WHERE A.x = B.x AND B.y IS NOT NULL);

These are not exactly the same. The version with JOIN will return duplicate rows for A if there are multiple matches in B. Because you are only selecting from A, duplicates are probably not desirable, and I would recommend the EXISTS query.