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
IN clause is generally used to return values which are used as parameters for selecting another set of records. As followswhich is more suitable in or exist in sql?
SELECT emp_name FROM employees where emp_id IN ( select emp_id FROM another table WHERE some_condition )
And EXISTS 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 isAnswer is yes. Joins are always faster than IN. So the first qry can be written using JOIN as follows :"is join better than in?"
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 EXISTS 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 EXISTS 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,
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