February 22, 2012 at 8:43 pm
Version 1
---------
SELECT
FROM Table a
LEFT JOIN Table b ON a.ID= b.ID AND b.BMonth= 2 AND BY= 2022
WHERE a.Col1=1
AND a.ID= 'ABCD'
Version 2
---------
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 AND BY= 2022
As you can see, I have removed the left join in version 1 and put it under where clause. The version 1 returns 1 row but version two doesn't return anything. And this makes sense because there is no record in table b with BMonth = 2 and BY = 2022. Version 2 makes it a inner join and doesn't return anything.
Version1 is what I want. How can I rewrite the version 1, without putting "b.BMonth= 2 AND BY= 2022" in the left join and still get the same result? The issue is they are actually filter and like to put that in the where clause if possible
February 22, 2012 at 10:12 pm
SQL_Surfer (2/22/2012)
Version 1---------
SELECT
FROM Table a
LEFT JOIN Table b ON a.ID= b.ID AND b.BMonth= 2 AND BY= 2022
WHERE a.Col1=1
AND a.ID= 'ABCD'
Version 2
---------
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 AND BY= 2022
As you can see, I have removed the left join in version 1 and put it under where clause. The version 1 returns 1 row but version two doesn't return anything. And this makes sense because there is no record in table b with BMonth = 2 and BY = 2022. Version 2 makes it a inner join and doesn't return anything.
Version1 is what I want. How can I rewrite the version 1, without putting "b.BMonth= 2 AND BY= 2022" in the left join and still get the same result? The issue is they are actually filter and like to put that in the where clause if possible
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'
February 23, 2012 at 2:50 am
I don't understand your question. You already have the solution as version 2. True, you should put all the filter criteria in WHERE clause. There is no point in putting the filter condition just after the ON clause of JOIN. So what is your problem actuly or have I missed something here?
February 23, 2012 at 2:55 am
Divine Flame (2/23/2012)
I don't understand your question. You already have the solution as version 2. True, you should put all the filter criteria in WHERE clause. There is no point in putting the filter condition just after the ON clause of JOIN. So what is your problem actuly or have I missed something here?
For inner join, yes it does not have any impact.
But for Left, it makes lots of difference the filter is with join or where...
February 23, 2012 at 2:58 am
sqlzealot-81 (2/23/2012)
But for Left, it makes lots of difference the filter is with join or where...
Yes, completely changes the meaning of the query. So the question is, which gives the results wanted. Whichever is is, that's the one to use.
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 3:03 am
sqlzealot-81 (2/23/2012)
Divine Flame (2/23/2012)
I don't understand your question. You already have the solution as version 2. True, you should put all the filter criteria in WHERE clause. There is no point in putting the filter condition just after the ON clause of JOIN. So what is your problem actuly or have I missed something here?For inner join, yes it does not have any impact.
But for Left, it makes lots of difference the filter is with join or where...
Thanks, got the point 🙂 So he needs CTE or subquery here.
February 23, 2012 at 3:05 am
Divine Flame (2/23/2012)
sqlzealot-81 (2/23/2012)
Divine Flame (2/23/2012)
I don't understand your question. You already have the solution as version 2. True, you should put all the filter criteria in WHERE clause. There is no point in putting the filter condition just after the ON clause of JOIN. So what is your problem actuly or have I missed something here?For inner join, yes it does not have any impact.
But for Left, it makes lots of difference the filter is with join or where...
Thanks, got the point 🙂 So he needs CTE or subquery here.
No he doesn't. He's got working code, he can just use that.
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 3:06 am
SQL_Surfer (2/22/2012)
Version1 is what I want. How can I rewrite the version 1, without putting "b.BMonth= 2 AND BY= 2022" in the left join and still get the same result?
If version 1 is what you want, then use version 1. No reason to fiddle around for more complex alternatives to something you already have.
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 3:15 am
Gail is right, if you have reuired results stick with the working query.
But, if still want to do:
SQL_Surfer (2/22/2012)
...How can I rewrite the version 1, without putting "b.BMonth= 2 AND BY= 2022" in the left join and still get the same result? The issue is they are actually filter and like to put that in the where clause if possible
you can do it as:
SELECT *
FROM Table a
LEFT JOIN Table b ON a.ID= b.ID
WHERE a.col1=1
AND a.ID= 'ABCD'
AND (b.ID IS NULL OR (b.BMonth= 2 AND b.BY= 2022))
I would suggest you to read about JOINs in BoL again to fully understand why two your versions do not produce the same result.
February 23, 2012 at 7:28 am
I tired that. But this won't return anything.
February 23, 2012 at 7:45 am
Why don't you just use the working query that you have? It does what you want already.
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 7:48 am
Thanks. I will use the working query. Thanks for all of your help.
February 23, 2012 at 7:49 am
SQL_Surfer (2/23/2012)
I tired that. But this won't return anything.
Can you supply ddl of tables involved and some data setup?
February 23, 2012 at 10:44 am
As a left join, you are returning all rows from Table A, but only those in Table B that match (given the specified critera).
In your alternative, by removing the filters from the left join and placing it into the where clause, you have effectively made your left join an inner join. Therefore you will only get results if a match exists on both tables.
If those values are meant to be filters then putting them in the where clause, forcing an inner join, should be what you need. If it is a filter, being you only want rows matching that criteria, then they wouldn't be in the sub-population, but at a total population level.
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)
February 23, 2012 at 11:18 am
rlswisher (2/23/2012)
As a left join, you are returning all rows from Table A, but only those in Table B that match (given the specified critera).In your alternative, by removing the filters from the left join and placing it into the where clause, you have effectively made your left join an inner join. Therefore you will only get results if a match exists on both tables.
If those values are meant to be filters then putting them in the where clause, forcing an inner join, should be what you need. If it is a filter, being you only want rows matching that criteria, then they wouldn't be in the sub-population, but at a total population level.
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 true at all!
DECLARE @leftTable TABLE(Leftid int identity(1,1), firstname varchar(20), lastname varchar(20))
DECLARE @rightTable TABLE(RightId int identity(1,1), leftid int, address varchar(50))
INSERT INTO @leftTable
SELECT 'jared', 'karney'
UNION ALL
SELECT 'john', 'doe'
UNION ALL
SELECT 'karen', 'haas'
UNION ALL
SELECT 'lowden', 'fawkes'
UNION ALL
SELECT 'howard', 'white'
UNION ALL
SELECT 'bonnie', 'sears'
INSERT INTO @rightTable
SELECT 1, '123 fake street'
UNION ALL
SELECT 25, 'I''m still here'
SELECT *
FROM @lefttable lt
LEFT JOIN @righttable rt
ON lt.leftid = rt.leftid
AND lt.leftid < 5
AND rt.address = '123 fake street'
Again, it depends on what you need your results to be, but filtering in the join does not turn it into an inner join...
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply