Difference Between IN and EXISTS in Oracle

What is the difference between in and exists in Oracle? How can we know which is more cost efficient? The answer to all such questions lies in this post.

To understand the difference between IN and EXISTS, you must understand the definition of both first.

Check out other difference posts here >> Other Difference Posts

IN Condition in Oracle

IN condition is one of the widely used conditions in Oracle to filter the data in the WHERE clause. It is also used when data needs to be limited through sub query. IN condition saves us from using multiple OR clause in the SQL statement and provide simpler version of the query.

The negate condition of IN is NOT IN.

Syntax

WHERE expression IN (Value 1, Value 2,…Value N)

Example

SELECT e.*
  FROM employees e
 WHERE e.dept_id IN (10, 20);

EXISTS Condition in Oracle

The EXISTS condition in Oracle is used with sub-query. It is considered as a success if at least one row is returned.

The negate condition of EXISTS is NOT EXISTS.

Syntax

WHERE EXISTS (sub-query)

Example

SELECT e.*
  FROM employees e
 WHERE EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = 10);

Difference Between IN and EXISTS

EXISTS

IN

SQL Engine will stop the process as soon as it finds a single positive condition in EXISTS condition SQL Engine compares all values in IN condition
The answer of EXISTS can be TRUE or FALSE The answer of IN can be TRUE or FALSE or NULL
EXISTS cannot compare values between parent query and sub-query IN compares values between parent query and sub-query
It can be used to determine if any values are returned or not IN is used as multiple OR operator
EXISTS is faster than IN if sub-query result is large IN is faster than EXISTS if sub-query result is less
NULL can be compared using EXISTS condition NULL cannot be compared using IN condition
Direct values cannot be compared using EXISTS condition. It should have sub-query with SELECT clause

E.g. SELECT SYSDATE FROM DUAL WHERE 1 EXISTS (1)

This query will return an error of invalid relational operator

IN condition can have multiple direct values instead of sub-query

E.g. SELECT SYSDATE FROM DUAL WHERE 1 IN (1);

This query will return today’s date as output

I hope you like the page and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.