PUZZLED LEFT JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PUZZLED LEFT JOIN

Author

Topic

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-03-26 : 21:26:24

/*just run the query by pasting the whole thing into the sql management studio opening a query window*/
/*and please someone help me going through the last 2 queries marked 1) and 2),
why the cause of the 2 different results, when selected b.classid = 0 it returns nothing and when it is selected b.classid = 1 it returns a set*/

DECLARE @tab1 TABLE(
id INT,
namea varchar(max),
classid int
)

DECLARE @tab2 TABLE(
id INT,
mamea varchar(max),
classid int
)

INSERT INTO @tab1
VALUES (1,'fasas',0)

INSERT INTO @tab1
VALUES (2,'duplessis',0)

INSERT INTO @tab1
VALUES (3,'character',0)

INSERT INTO @tab1
VALUES (4,'fusis',0)

INSERT INTO @tab1
VALUES (5,'sssssss',0)

INSERT INTO @tab2
( id, mamea, classid )
SELECT a.*
FROM @tab1 a LEFT JOIN @tab2 b
ON a.id = b.id --AND b.classid=1
WHERE b.classid IS NULL

SELECT * FROM @tab2

/*id mamea classid
1 fasas 0
2 duplessis 0
3 character 0
4 fusis 0
5 sssssss 0*/

/*please see the below query when the b.classid = 1 (or any number other than 0) it returns below*/

/*1)*/SELECT b.classid,a.*,b.*
FROM @tab1 a LEFT JOIN @tab2 b
ON a.id = b.id AND b.classid=1
WHERE b.classid IS NULL

/*classid id namea classid id mamea classid
NULL 1 fasas 0 NULL NULL NULL
NULL 2 duplessis 0 NULL NULL NULL
NULL 3 character 0 NULL NULL NULL
NULL 4 fusis 0 NULL NULL NULL
NULL 5 sssssss 0 NULL NULL NULL*/

/*please see the below query when the b.classid = 0 it returns nothing*/

/*2)*/SELECT b.classid,a.*,b.*
FROM @tab1 a LEFT JOIN @tab2 b
ON a.id = b.id AND b.classid=0
WHERE b.classid IS NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-26 : 21:40:43

First, remove the "WHERE b.classid IS NULL" from both query and run. You will see that in Q1 it cannot find matching records in @tab2, but in Q2, there are matching records.

So when you apply the filter "WHERE b.classid IS NULL", in Q1, you want to see the result without matching record in @tab2 and you did. But in Q2 there isn't any not-matching records so you get nothing return

KH
[spoiler]Time is always against us[/spoiler]

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-03-26 : 21:57:20

Thanks kt for the instant reply, this is a query which am working with currently, will you let me know the reason for putting that filter on joining clause ‘ON a.id = b.id AND b.classid=1’ which is mentioned in italic under quotes, and please state me why that filter is not put under the where clause, this is query which is there for loading purpose is what I have learnt from the senior developer

Thanks,

Ben.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-03-26 : 22:01:30

please discard the immediate post from me above, as it does not show up the italic font though I have mentioned

Thanks kt for the instant reply, this is a query which am working with currently, will you let me know the reason for putting that filter on joining clause ‘ON a.id = b.id AND b.classid=1’ which is mentioned under quotes 'b.classid = 1', and please state me why that filter is not put under the where clause, this is query which is there for loading purpose is what I have learnt from the senior developer, am not referring about the 'ON a.id = b.id' am only referring about the 'b.classid=1'

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-26 : 22:04:03

Outer join is a bit confusing for new one and easy to make a mistake for others, even experienced developers. Definition of this join is a bunch on web, but to understand it, long explanation is required. Just keep in my : for outer join, ON and WHERE clause is not process at the same phase, logically. Where clause is filter AFTER the join based on ON clause. khtan posts a good example

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-26 : 22:10:05

ON a.id = b.id AND b.classid=0

am not referring about the 'ON a.id = b.id' am only referring about the 'b.classid=1'

AND/OR .... go with ON clause

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-03-26 : 22:17:34

thanks namman, my doubt was like, to ask, why does a join have an sql statement as 'b.classid=0', 'b.classid = 1', isnt it always, when you join 2 tables, you join like
a.id = b.id, where you relate 2 fileds from the tables you are relating..

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-26 : 23:39:33

Other may have better explanation ... Outer join are asked repeatedly on this forum, let's me try one by example.

SELECT b.classid,a.*,b.*
FROM @tab1 a LEFT JOIN @tab2 b
ON a.id = b.id AND b.classid=0 + other conditions (if need)
WHERE b.classid IS NULL + other conditions (if need)

Logically, this has 3 phases

1 -
SELECT b.classid,a.*,b.*
FROM @tab1 a INNER JOIN @tab2 b
ON a.id = b.id AND b.classid=0 + other conditions (if need)

return temporary resultset

2 -
Add rows, which do NOT satisfy condition in phase 1, of the left table (left join) into the resultset.For those rows, all columns of the other table are NULL.

3 -
Select rows in the resultset based on condition
WHERE b.classid IS NULL + other conditions (if need)

Come back with your original queries:

Query 1

SELECT b.classid,a.*,b.*
FROM @tab1 a LEFT JOIN @tab2 b
ON a.id = b.id AND b.classid=1
WHERE b.classid IS NULL

After phase 1, you have this:
empty resultset
(because no row satisfy condition ON a.id = b.id AND b.classid=1)

After phase 2, you have this:
NULL 1 fasas 0 NULL NULL NULL
NULL 2 duplessis 0 NULL NULL NULL
NULL 3 character 0 NULL NULL NULL
NULL 4 fusis 0 NULL NULL NULL
NULL 5 sssssss 0 NULL NULL NULL
(because no row satisfy, so all rows in left table are added)

After phase 3, you have this:
NULL 1 fasas 0 NULL NULL NULL
NULL 2 duplessis 0 NULL NULL NULL
NULL 3 character 0 NULL NULL NULL
NULL 4 fusis 0 NULL NULL NULL
NULL 5 sssssss 0 NULL NULL NULL
(because all rows satisfy condition WHERE b.classid IS NULL )

Query 2

SELECT b.classid,a.*,b.*
FROM @tab1 a LEFT JOIN @tab2 b
ON a.id = b.id AND b.classid=0
WHERE b.classid IS NULL

After phase 1, you have this:
0 1 fasas 0 1 fasas 0
0 2 duplessis 0 2 duplessis 0
0 3 character 0 3 character 0
0 4 fusis 0 4 fusis 0
0 5 sssssss 0 5 sssssss 0
(because all rows satisfy condition ON a.id = b.id AND b.classid=0

After phase 2, you have this:
0 1 fasas 0 1 fasas 0
0 2 duplessis 0 2 duplessis 0
0 3 character 0 3 character 0
0 4 fusis 0 4 fusis 0
0 5 sssssss 0 5 sssssss 0
(because all rows satisfy, so no row is added)

After phase 3, you have this:
empty resultset
(because no row satisfy condition WHERE b.classid IS NULL

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-03-27 : 00:28:14

vow, beautiful explanation, namman, cant require a better explanation than this..

Ben

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources