February 23, 2012 at 11:32 am
Your example does not do what I was referring to. In the original post V2,
From Table A
Left Join B on ID = ID
Where A.Col = x
and B.Col = Z
This makes the left join act as an inner join.
You example was still using a left join as you have not included a Where clause.
February 23, 2012 at 11:40 am
rlswisher (2/23/2012)
The alternative to that would be change your equal to an OR statement as such.SELECT
FROM Table a
LEFT JOIN Table b ON a.ID= b.ID
WHERE a.col1=1
AND a.ID= 'ABCD'
AND (b.BMonth= 2 or b.BMonth is null)
AND (BY= 2022 or BY is null)
That's not quite equivalent to the left join with the filter in the join.
When the filter is in the join, the filter is done before the results of the join are evaluated. So if a row is eliminated because the month is not 2 and that row is the only one that matches a certain ID from tableA, the row from tableA would join to nulls and be returned.
When the filter is in the where (even with IS NULL checks), it's done on the result of the join, so if a row in table B has a month that's not 2 and it's the only one that matches a certain ID from tableA, the row from tableA would be eliminated completely by the where clause.
Easy to see.
DECLARE @TableA TABLE (
ID CHAR(4)
)
DECLARE @TableB TABLE (
ID CHAR(4),
BMonth SMALLINT
)
INSERT INTO @TableA (ID) VALUES('abcd')
INSERT INTO @TableB (ID, BMonth) VALUES ('abcd',3)
SELECT *
FROM @TableA AS ta
LEFT OUTER JOIN @TableB AS tb ON ta.ID = tb.ID AND tb.BMonth = 2
WHERE ta.ID = 'abcd'
SELECT *
FROM @TableA AS ta
LEFT OUTER JOIN @TableB AS tb ON ta.ID = tb.ID
WHERE ta.ID = 'abcd'
AND (tb.BMonth = 2 OR tb.BMonth IS NULL)
The first returns a row, the second does not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2012 at 11:45 am
rlswisher (2/23/2012)
Your example does not do what I was referring to. In the original post V2,From Table A
Left Join B on ID = ID
Where A.Col = x
and B.Col = Z
This makes the left join act as an inner join.
You example was still using a left join as you have not included a Where clause.
It still does not make it act as an inner join. Maybe you are referring to the OP instead of the several solutions provided, but I assumed you were responding to a more recent post since it doesn't make sense to explain what others already had. Not trying to be a jerk, just explaining my reasoning.
Jared
CE - Microsoft
February 23, 2012 at 11:46 am
You're right. I didn't catch that. Dag nabbit. Thanks!
Even though, it still makes it an inner join in effect.
So the question is, if it is a filter, why would you want results returned for ID = X, if ID = X doesn't exist on table B Where @1 and @2 are so?
February 23, 2012 at 9:50 pm
sqlzealot-81 (2/22/2012)[hr
Write a sub query filtering the values from tableb and then join.Try the below...I dint really try it in SSMS.
SELECT
FROM Table a
LEFT JOIN (Select * From Table where BMonth= 2 AND BY= 2022)b ON a.ID= b.ID
WHERE a.Col1=1
AND a.ID= 'ABCD'
Just curios, did you try this?
February 24, 2012 at 7:35 am
Yes. That one worked. Thanks!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply