LEFT JOIN WHERE Clause IS NOT NULL

user_4192179 0
Viewed 1277

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?

sql
4 Answers

If table contains following values:

**Table1**                  **Table2**    

ID       Values           ID         Values
1        100              1          10
2        200              2          20
3        300              3          30
4        400              4          40
null     2000             null       3000
5        500

Your query will return the following:

with: A.Id, A.table1_values, B.Table2_values

ID       Table1_Values  Table2_Values
1        100            10
2        200            20
3        300            30
4        400            40
5        500            null

Final output for a.*:

ID       Table1_Values  
1        100            
2        200            
3        300            
4        400            
5        500
null     2000

Copied data from here: left outer join with null values

user_25491100

Very simple but good question.

Let's make transformations and find answer

Step 1: Move WHERE predicate on B.y to JOIN-clause

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

Now we can:

  1. Transform LEFT JOIN into UNION ALL of JOIN- and NOT EXISTS- expressions

Step 2: Transformation LEFT JOIN -> JOIN + NOT EXISTS

SELECT A.*
    FROM A
    JOIN B ON A.x == B.x
        AND B.y IS NOT NULL 
WHERE 
    A.z == "Hola" 
UNION ALL
SELECT A.*
    FROM A
WHERE 
    A.z == "Hola"
    AND NOT EXISTS(
        SELECT * FROM B 
            WHERE A.x = B.x                
            AND B.y IS NOT NULL
    )

Looking at this expressions we can see that:

  1. In general case it could not be simplified more
  2. In case when all fields of table B defined with not NOT NULL - lower part of UNION ALL becames redundant
user_11682120

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.

user_11440350

you may try something like this:

select a.*
from a
where a.z = "Hola"
and exists (select 1
            from b
            where b.y is not null
            and b.x = a.x);
user_97995090
Related