top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

When should we use EXISTS, and when should we use IN?

+4 votes
51 views
When should we use EXISTS, and when should we use IN?
posted Jun 23, 2015 by Manikandan J

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

The exists keyword can be used in that way, but really it's intended as a way to avoid counting:
--this statement needs to check the entire table select count(*) from [table] where ... --this statement is true as soon as one match is found exists ( select * from [table] where ... )
This is most useful where you have if conditional statements, as exists can be a lot quicker than count.
The in is best used where you have a static list to pass:

select * from [table] where [field] in (1, 2, 3)

When you have a table in an in statement it makes more sense to use a join, but it doesn't really matter. The query optimiser will return the same plan either way.
EXISTS will tell you whether a query returned any results. eg:

SELECT * FROM Orders o WHERE EXISTS ( SELECT * FROM Products p where p.ProductNumber = o.ProductNumber)

IN is used to compare one value to several, and can use literal values, like this:

SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100)

You can also use query results with the IN clause, like this:

SELECT * FROM Orders WHERE ProductNumber IN ( SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0)

EXISTS is much faster than IN (when the sub-query results is very large)
IN is faster than EXISTS (when the sub-query results is very small)

answer Jun 25, 2015 by Shivaranjini
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...