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.
WHERE expression IN (Value 1, Value 2,…Value N)
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.
WHERE EXISTS (sub-query)
SELECT e.* FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = 10);
Difference Between IN and EXISTS
|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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!