which is more suitable IN or EXIST in SQL?

which is more suitable IN or EXIST in SQL?

Hi
EXIST is fast compare to IN

[quote]which is more suitable in or exist in sql?[/quote]
[color="blue"]IN [/color]clause is generally used to return values which are used as parameters for selecting another set of records. As follows
SELECT emp_name FROM employees where emp_id IN ( select emp_id FROM another table WHERE some_condition )

And [color="blue"]EXISTS[/color] is used as a decision making statement as follows

IF EXISTS ( select emp_name from emp where emp_id=1)
BEGIN
do something;
END
ELSE
BEGIN
do another thing;
END

So these statements are basically used for two different purposes. One may want to ask is [color="blue"][quote]"is join better than in?"[/quote][/color] Answer is yes. Joins are always faster than IN. So the first qry can be written using JOIN as follows :

select e.emp_name from employees e join another_table a on e.emp_id = a.emp_id and a.some_col = something

Above statement executes much faster than IN.

Now coming to the second statement [color="blue"]EXISTS[/color] the catch is ; always use
IF EXITS ( select emp_name from emp where emp_id=1)
BEGIN
do something;
END
over following statement

IF EXITS ( select count(*) from emp where emp_id=1)
BEGIN
do something;
END
because second [color="blue"] EXISTS [/color] statement does a table scans to get the count of rows and first statemet returns true as soon as it finds first match.


HTH :)
Regards,

[QUOTE=vcyogi;13136]Hi
EXIST is fast compare to IN[/QUOTE]

Thank you very much

Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset (nested loops, merge, whatever). If the table in the subquery is
small, and the table in the main query is large, then IN usually makes
sense.

Using "EXISTS", the optimizer generally runs the main query first, then
applies that dataset to the subquery. If the table in the subquery is
large, and the table in the main query is small, then EXISTS usually makes
sense.

EXIST would a faster option because IN has return data from table where as EXIST needs to return only a literal which makes it much faster