EXISTS vs. IN clause in SQL :: -------------------------------------------------- > 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); >> The Exists keyword evaluates true or false, but IN keyword compare all values in the corresponding sub query column. : EXISTS is very faster than IN (when the subquery results is very large) : IN is faster than EXISTS (when the subquery results is very small) >< EXISTS Is Faster in Performance than IN. If Most of the filter criteria is in subquery then better to use IN and If most of the filter criteria is in main query then better to use EXISTS. If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match. docs.oracle/cd/E17952_01/refman-5.1-en/exists-and-not-exists-subqueries.html techrepublic/article/oracle-tip-understand-the-difference-between-in-and-exists-in-subqueries/
Posted on: Wed, 11 Sep 2013 10:49:16 +0000
Trending Topics
Recently Viewed Topics
© 2015