understanding 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

understanding LEFT JOIN

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources