CODEWITHSUNDEEP
sql-server
Tjaart van der Walt
Tjaart van der Walt

Reputation: 5179

Left Join containing where clause INSIDE join

Lets say we have the following table structure:

DECLARE @Person TABLE
(
    PersonId INT,
    Name VARCHAR(50)
)

DECLARE @Address TABLE
(
    AddressId INT IDENTITY(1,1),
    PersonId INT
)

And we insert two person records:

INSERT INTO @Person (PersonId, Name) VALUES (1, 'John Doe')
INSERT INTO @Person (PersonId, Name) VALUES (2, 'Jane Doe')

But we only insert a address record for John

INSERT INTO @Address (PersonId) VALUES (1)

If I execute the following queries I get different results

SELECT * 
FROM @Person p
 LEFT JOIN @Address a
    ON p.PersonId = a.PersonId AND a.PersonId IS NULL

 PersonId | Name     | AddressId | PersonId
 1        | John Doe | NULL      | NULL
 2        | Jane Doe | NULL      | NULL

VS

SELECT * 
FROM @Person p
 LEFT JOIN @Address a
    ON p.PersonId = a.PersonId
WHERE a.PersonId IS NULL

 PersonId | Name     | AddressId | PersonId
 2        | Jane Doe | NULL      | NULL

Why are the queries returning different results?

Upvotes: 1

Views: 369

Answers (4)

Jabulani
Jabulani

Reputation: 76

Make it a habit to read your SQL query from the Where condition and then look at your joins, this will give you a clearer meaning/understanding of what is happening or going to be returned.

In this case you said WHERE a.PersonId IS NULL the Select Part must happen and It must Join using the following join criteria.

That is how your query is being read by your machine hence the different sets of results.

And then in contrast, on the condition where there is no where clause, the results on the Left table (p) do not have to exist on (a) but at the same time the results on (a) must be null but already they might not exist. Already at this point your SQL will be confused.

Upvotes: 0

user2361044
user2361044

Reputation: 26

ON clause defines which all matching rows to show from both tables. WHERE clause actually filters the rows.

In the 1st query, it is returning 2 rows because LEFT JOIN returns all the rows from the left table irrespective of match from right table.

2nd query is returning 1 row, because for PersonId=1, @Address table contains a matching record hence a.PersonId is NOT NULL.

Upvotes: 0

xdd
xdd

Reputation: 545

First:

get all records (two) from Person and join 0 records from Address, cos none of address have PersonID = NULL. After that no additional filters applyed. And you see two records from Person

Second:

get all records (two) from Person and one of them joined to Address with ID = 1. After that your WHERE filter applyed and one of records with joined ID = 1 disappears.

Upvotes: 1

SharK
SharK

Reputation: 2215

The first query is not meeting any of your conditions. Hence it is displaying all results from the @Person table (Typical Left join). Where as in the second query, the where clause is applied after the join. Hence it is displaying proper result.

Upvotes: 2

Related Questions