October 22, 2015 at 9:39 am
Don't forget ANY and ALL. Written correctly, they can do what EXISTS does in many cases, and often read better.
e.g.
select a,b,c
from foo
where c = any(select d from bar)
instead of
select a,b,c
from foo
where exists (select 1 from bar where c = d)
Of course with these simple examples you could just as well use IN.
Also, be careful that when you want to exclude rows:
select a,b,c
from foo
where c <> any(select d from bar)
reads well but doesn't do what you think. It expands to:
where c <> d1 OR c <> d2 OR ... OR c <> dn
Note: Can't seem to make the less than/greater than signs render correctly
which is not (likely) what you want and is always true if there are at least two distinct values in the subquery. You can write
select a,b,c
from foo
where c <> all(select d from bar)
which expands to:
where c <> d1 and c <> d2 ...
which works if there are no nulls. Otherwise use:
select a,b,c
from foo
where c <> all(
select d from bar
where d is not null
)
Which is not much different from the NOT EXISTS or NOT IN variants
Gerald Britton, Pluralsight courses
October 22, 2015 at 11:07 am
cyp901 (10/22/2015)
EXISTS is also safer than IN when you're dealing with a nullable column.
That's NOT IN. IN has no behavior differences with NULL, it's negation does.
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
October 22, 2015 at 3:56 pm
peter 82125 (10/22/2015)
Thank you all so much for your informative replies! Jacob, you rock, caffeine or none! Really appreciate it 🙂
I'm glad we could help!
October 23, 2015 at 8:27 am
Thanks for the clarification.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply