LEFT JOIN : display null for the right side-CodePudding

Let's say we have 2 tables

TABLEA           TABLEB

1                 1   01/03/2022
2                 2   01/01/2022
3

This SQL statement:

select id 
from tableA 
left join tableB on tableA.id = tableB.id

displays:

1       1 01/03/2022
2       2 01/01/2022
3       NUll

Unfortunately, when I add a restriction on the right table, the behaviour is not the same.

select id   
from tableA 
left join tableB on tableA.id = tableB.id
where tableB.date > '01/01/2022'

I would like something like this :

1      1 01/03/2022
2      NULL
3      NULL

But I get this:

1      1 01/03/2022

That's all. I was not expecting this behavior. Can someone tell what the query should be ?

CodePudding user response：

 CREATE TABLE #TABLEA 
(
    ID tinyint
);

CREATE TABLE #TABLEB
(
    ID tinyint
    ,Date date
);

INSERT INTO #TABLEA
VALUES(1),(2),(3)

INSERT INTO #TABLEB
VALUES(1,'2022-03-01'),(2,'2022-01-01'),(3,NULL)


SELECT 
    *
FROM 
    #TABLEA t1
LEFT JOIN #TABLEB t2 ON t2.ID= t1.ID

AND t2.Date > '2022-01-01'

DROP TABLE
    #TABLEA
    ,#TABLEB;

When you added the WHERE clause you effectively changed the LEFT JOIN to an INNER JOIN. Putting the condition in the JOIN predicates keeps the LEFT JOIN as intended.