March 19, 2015 at 9:51 am
i have 2 tables table1 and Table2. I need all records from table1 which does not have any matching records in table2
I was able to complete this by below script
SELECT A.* FROM Table1 A WHERE A.USERID NOT IN(
SELECT B.USERID FROM Table1 B
inner JOIN Table2 C
ON B.USERID=C.UserID)
But i would like to do the same with left outer join.
Would you please help me on this?
March 19, 2015 at 9:54 am
If the NOT IN works, why do you want to use a LEFT OUTER JOIN?
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
March 19, 2015 at 10:06 am
Thanks for looking on this. Yes, not in clause works as expected; but i think join gives better execution plan than not in especially the table size is huge. Thanks for your help on this
March 19, 2015 at 10:08 am
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
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
March 19, 2015 at 10:09 am
it is my understanding that we could do the same with left/right join. if not please advice what other option we have, if any
March 19, 2015 at 10:21 am
thanks for the links. As per link the is null clause on the where clause for the lookup columns worked fine. But this works only if is null clause returns no. In case if there is one null value in Table2 then this method will not returns the value that i need
March 19, 2015 at 10:36 am
ichayan2003 (3/19/2015)
it is my understanding that we could do the same with left/right join. if not please advice what other option we have, if any
Did you read the articles?
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
March 19, 2015 at 10:40 am
ichayan2003 (3/19/2015)
As per link the is null clause on the where clause for the lookup columns worked fine. But this works only if is null clause returns no. In case if there is one null value in Table2 then this method will not returns the value that i need
No, the articles definitely don't say that left join ... is null only works if there are no nulls. It's NOT IN that behaves badly in the presence of nulls which is why I prefer NOT EXISTS
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
March 19, 2015 at 10:40 am
SELECT A.*
FROM Table1 A
LEFT OUTER JOIN (
SELECT B.USERID
FROM Table1 B
INNER JOIN Table2 C ON B.USERID=C.UserID
) AS D ON D.USERID = A.USERID
WHERE
D.USERID IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 19, 2015 at 10:55 am
Thanks GilaMonster, i didnt read the entire content in the link. When i see that part of the script that using is null i tried out myself on the script and seems worked ok. But ddint work out well with the result set when i modified the table to add one null value. Will read this link in detail
March 19, 2015 at 11:00 am
Thanks SScrazy for your input on this. But i am trying to avoid Is Null as there is a chance for null value on my tables.
As GilaMonster suggests, i think, not exist would be a better choice. But i still think, perhaps, we can achieve this by inner and left join together. Can you shed some light?
March 19, 2015 at 11:16 am
ichayan2003 (3/19/2015)
Thanks SScrazy for your input on this. But i am trying to avoid Is Null as there is a chance for null value on my tables.As GilaMonster suggests, i think, not exist would be a better choice. But i still think, perhaps, we can achieve this by inner and left join together. Can you shed some light?
On a LEFT JOIN, you use IS NULL to check for a "NOT FOUND"/"NOT EXISTS". SQL is setting the column to NULL, the NULL is not being read from the table. Any NULL(s) you have the table can't be selected by the query because it has INNER JOIN with "=" on that column, and NULL can never be "=" to anything, so NULL can never match the JOIN and thus will never be in the result.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 19, 2015 at 12:10 pm
If you want to test for existance / non-existances I think EXISTS is better than LEFT JOIN, since it's more clear what you're doing and LEFT JOIN can at least in theory lead to duplicates of data potentially returned if you mess up the WHERE condition
March 19, 2015 at 12:52 pm
Thanks every one. All your inputs and advises are absolutely correct. You guys are really helpful
March 19, 2015 at 1:31 pm
siggemannen (3/19/2015)LEFT JOIN can at least in theory lead to duplicates of data potentially returned if you mess up the WHERE condition
Not in the specific case where you're only looking to pull rows that did not find a match: by definition that won't duplicate any data, since no rows will match. Only multiple matching rows could potentially duplicate date.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply