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.
| Author |
Topic |
|
ultradiv
Starting Member
41 Posts |
 Posted - 2015-01-25 : 06:28:52
|
[code]-- Why is the left table in a LEFT JOIN limited by the where clause on the right table?
-- eg
DECLARE @LeftTable TABLE (LeftID INT NOT NULL IDENTITY(1, 1), LeftValue INT NULL)
INSERT @LeftTable (LeftValue)
VALUES (111)
INSERT @LeftTable (LeftValue)
VALUES (222)
INSERT @LeftTable (LeftValue)
VALUES (333)
INSERT @LeftTable (LeftValue)
VALUES (444)
DECLARE @RightTable TABLE (RightID INT NOT NULL IDENTITY(1, 1), LeftID INT NOT NULL, RightSelection BIT)
INSERT @RightTable (LeftID, RightSelection)
VALUES (1, 1)
INSERT @RightTable (LeftID, RightSelection)
VALUES (2, 1)
INSERT @RightTable (LeftID, RightSelection)
VALUES (3, 0)
INSERT @RightTable (LeftID, RightSelection)
VALUES (4, 0)
SELECT LT.LeftID, LT.LeftValue, CASE WHEN RT.RightID IS NULL THEN 0 ELSE 1 END isSelectedByRightTable
FROM @LeftTable LT
LEFT OUTER JOIN @RightTable RT ON LT.LeftID = RT.LeftID
WHERE RT.RightSelection = 1
/*##### results ######
LeftID LeftValue isSelectedByRightTable
1 111 1
2 222 1
Where as what I want is
LeftID LeftValue isSelectedByRightTable
1 111 1
2 222 1
3 333 0
4 444 0
######### */[/code] |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2015-01-25 : 08:49:05
|
Because RightSelection field is in the where section.
If you make it part of the join, you will get your desired result:
quote:
Originally posted by ultradiv
SELECT LT.LeftID, LT.LeftValue, CASE WHEN RT.RightID IS NULL THEN 0 ELSE 1 END isSelectedByRightTable
FROM @LeftTable LT
LEFT OUTER JOIN @RightTable RT ON LT.LeftID = RT.LeftID
WHERE AND RT.RightSelection = 1
Edit: Corrected spelling |
 |
|
|
ultradiv
Starting Member
41 Posts |
Posted - 2015-01-25 : 09:16:36
|
Hey thanks bitsmed.
I learn something everyday from you guys  |
|
|
|
|
|
|
understanding LEFT JOIN