LEFT JOIN gives different data set depending on the position of WHERE condition

Question

See the below 3 Counts, just to give you a brief idea about the table data. All skCitizen in [dbo].[LUEducation] is present in [dbo].[LUCitizen]

SELECT  COUNT(*) FROM   [dbo].[LUCitizen] --115000 ROWS
SELECT  COUNT(*) FROM   [dbo].[LUEducation]  --201846 ROWS

SELECT  COUNT(*) --212695 ROWS
FROM    [dbo].[LUCitizen] C
LEFT JOIN   [dbo].[LUEducation] E
ON      C.skCitizen = E.skCitizen

SELECT  COUNT(*) FROM   [dbo].[LUEducation] WHERE skSchool = 24417 --4 ROWS

---

See the below 2 queries,

SELECT  C.skCitizen,E.skCitizen
FROM    [dbo].[LUCitizen] C
LEFT JOIN   [dbo].[LUEducation] E
ON      C.skCitizen = E.skCitizen
WHERE   E.skSchool = 24417
--4 ROWS

SELECT  C.skCitizen,E.skCitizen
FROM    [dbo].[LUCitizen] C
LEFT JOIN   (SELECT * FROM [dbo].[LUEducation] WHERE skSchool = 24417) E
ON      C.skCitizen = E.skCitizen
--115000 ROWS

In the last 2 queries, the confusing query for me is the 1st one. There i expected 115000 rows, but only 4 rows displayed. According to my understanding, Full rows from [dbo].[LUCitizen] will be displayed, then 4 rows from [dbo].[LUEducation] will be LEFT Joined.

Why is the 2 Queries different?

Pardon me , if this is a duplicate question.

Answer 1

When you do this:

SELECT  C.skCitizen,E.skCitizen
FROM    [dbo].[LUCitizen] C
LEFT JOIN   [dbo].[LUEducation] E
ON      C.skCitizen = E.skCitizen
WHERE   E.skSchool = 24417;

You are turning the left join into an inner join, because E.skSchool is NULL for non-matching rows. The correct way to put a condition on the second table in a left join is to use the on clause:

SELECT  C.skCitizen,E.skCitizen
FROM    [dbo].[LUCitizen] C
LEFT JOIN   [dbo].[LUEducation] E
ON      C.skCitizen = E.skCitizen AND E.skSchool = 24417;

Answer 2

If the left join fails to find a match in E, columns from E receive a null value. Then the where clause:

E.skSchool = 24417

Becomes:

null = 24417

Which is not true. So it will filter out all rows.