SQL Server Performance Forum – Threads Archive
Problem with left join.
This seems a simple problem, but I can’t understand why. I am not getting the desired results for LEFT join. Here it is: TABLE 1: stationflagTABLE 2: stationflagmap SAMPLE DATA:
SELECT DISTINCT * FROM stationflag StationFlagId StationFlagName
————- —————-
Comedy Comedy
Action Action SELECT DISTINCT TOP 5* FROM stationflagmap ServiceId StationFlagId StationFlagValue
——— ————- —————–
1 Comedy N
10 Action N
10 Action N
100 Comedy N
100 Action N QUERY: SELECT A.StationFlagId,
B.StationFlagId
FROM stationflag A
LEFT JOIN stationflagmap
on A.StationFlagId=B.StationFlagId
where B.serviceId=1 THE OUTPUT I GET IS: stationFlagId StationFlagId
————- ————-
Comedy Comedy
WHY DID I NOT A NULL VALUE IN THE RESULT SET FOR ‘ACTION’ Thanks, StarWarsBigBang
Try:
SELECT A.StationFlagId,
B.StationFlagId
FROM stationflag A
LEFT JOIN stationflagmap
on A.StationFlagId=B.StationFlagId and B.serviceId=1
When you put condition on outer table in where clause you eliminate all rows where outerTableColumn is null because it can’t be equal to any value.
If you’re putting a filtering condition on the outer table (the one -like here – after a left join, or else the one before a right join) then you might as well use an inner join – this usually improves performance. Meanwhile, I’m not 100% sure this is properly normalized – the fact that you’re adding the criteria for the outer table StationFlagMap is kind of a hint that there is a set of columns that have the same set of values depending on just one of these columns.
A LEFT JOIN uses the table on the left and will use a NULL value for the fields on the RIGHT table if they can’t be found. In this case you were looking for a RIGHT OUTER JOIN in the way you put the clause together. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
Actually in rethinking things (after pressing enter) what you were looking for wasn’t going to be resolved with either, let me check on what effect HAVING would have. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
Wow. I just learned something … I created the tables and sure enough Mirko is 100% correct, if you put the condition for the second table as part of the ON clause sure enough you get the NULL for the second table. Sorry for wasting space "guessing."
]]>