June 21, 2012 at 12:23 am
Hi All,
I have a basic doubt on using IN/EXISTS.
When to use IN over Exists/Exists over IN?
I heard that if the sub query is returning few records then we should use IN otherwise we should go for EXISTS. Here what is the thumb rule or count of records to use IN?
Please explain with any helpful links.
Thanks
🙂
June 21, 2012 at 12:35 am
Check for known values: in
Eg in ('US','Canada')
select
*
from
table
where
a in (1,2,3)
Check for values retrieved from a different query
Eg in (select CountryName from Countries where Continent = 'North America')
select
*
from
table
whee
a in (select something from other_table where other_table.col = table.col)
Check for existece of matching rows: Exists
Eg exists (select * from Cities where Population > 1000000)
select
*
from
table
where
exists (select * from other_table where other_table.col = table.col)
June 21, 2012 at 1:01 am
Thanks,
But my statements are by considering the performance of queries.
Could you please explain the difference by considering the performance?
Thanks,
🙂
June 21, 2012 at 1:43 am
Regarding performance: I cannot tell. In some cases in performs better, in some other cases exists, in some cases I can use an inner join. Which one performs better depends on the execution plan generated by the query optimizer, which in turn depends on the statistics and parameters (filtering options) and server options. There is really no single correct answer to your question.
June 21, 2012 at 2:08 am
June 21, 2012 at 4:07 am
Whenever I've tried a comparison (which is admittedly only on half a dozen occasions and not in any particularly scientific way) I get an identical execution plan. I've never been able to discern any difference in terms of performance. The only differences I'm aware of are in their logical capabilities rather than their performance, ie.
1. A exists doesn't work across a fixed list of values, only across a genuine sub query
2. An In can only do a comparison on a single field whereas Exists can compare on as many as you like.
June 21, 2012 at 6:25 am
FunkyDexter (6/21/2012)
...
1. A exists doesn't work across a fixed list of values, only across a genuine sub query
...
Hmm, it depends what you call "a genuine" sub query (in SQL2008 and up):
select *
from Mytable t
where exists (select 1 from (values (1),(2),(3),(100)) a(id) where a.id = t.id)
June 21, 2012 at 12:56 pm
Well, that's a query within a query... so I'd call it a sub-query.
The point I was making making was that you couldn't do something like:-
Select * From MyTable Where Exists (1,2,3,4)
It wouldn't make sense. If you want to compare to a fixed list of values you'd have to use an IN or form the values into a sub-query as you've shown.
June 22, 2012 at 3:02 am
FunkyDexter (6/21/2012)
Well, that's a query within a query... so I'd call it a sub-query.The point I was making making was that you couldn't do something like:-
Select * From MyTable Where Exists (1,2,3,4)
It wouldn't make sense. If you want to compare to a fixed list of values you'd have to use an IN or form the values into a sub-query as you've shown.
You cannot do "Select * From MyTable Where Exists (1,2,3,4)" as it's a wrong syntax for EXISTS.
Ok, it's depends on what you call "fixed list of values". 😉
I would say that VALUES (a),(b),(c)... produces fixed list of values, therefore, if you want to compare to a fixed list of values as well as "IN" you can use "EXISTS".
Of cause, if it is a list of single values there is no much point in using "EXISTS". However, if you have fixed list of value-pairs for example, it could come very handy:
...
WHERE
EXISTS (SELECT 1 FROM (VALUES ('a',1),('b',2),('c',3)) list(id, val)
WHERE list.id = t.id AND list.val < t.someval)
June 22, 2012 at 7:08 am
as it's a wrong syntax for EXISTS
Yep, that's why I said you couldn't do it.
Look, we're arguing semantics and really not helping the OP at all.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply