December 15, 2010 at 1:06 pm
Hi, I feel like I'm blowing a tire on this one...Shouldn't my results show 'abc'? What is wrong with that logic?
with cte1 as
(
select 'abc' as aValue
), cte2 as
(
select 'xyz' as aValue
)
select *
from cte1 where not exists
(select * from cte2)
thank you in advance...
December 15, 2010 at 1:19 pm
Ok...so if I run the query using 'not in' instead of 'not exists' it produces the desired result of 'xyz', what is the difference between the two methods?
with cte1 as
(
select 'abc' as aValue
), cte2 as
(
select 'xyz' as aValue
)
select aValue
from cte1 where aValue not in
(select aValue from cte2)
December 15, 2010 at 2:06 pm
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
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
December 15, 2010 at 2:09 pm
Exists checks for a row in the subquery. Not a matching row. A row. Any row. Hence, because your subquery returns a single row, the NOT EXISTS returns false always. If you want to make it equivalent to the IN, you need this:
with cte1 as
(
select 'abc' as aValue
), cte2 as
(
select 'xyz' as aValue
)
select *
from cte1 where not exists
(select * from cte2 WHERE cte2.aValue = cte1.aValue) -- correlate the subquery so that it checks for matching rows.
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
December 15, 2010 at 3:14 pm
Wow...fantastic answer. Thanks so much Gila.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply