NOT IN vs NOT EXISTS in PL/SQL

Last Updated : 05 Nov, 2024

PL/SQL is a Procedural Language/Structured Query Language. It allows developers to create robust, modular, and reusable code for implementing data manipulation, and transaction control in databases. Two crucial operators in PL/SQL are NOT IN and NOT EXISTS, which are often used to filter data based on specific conditions in subqueries. Understanding the differences between these two operators is essential for writing efficient queries and avoiding unexpected results.

In this article, we will understand the NOT IN vs NOT EXISTS operator along with the examples and so on.

Setting Up The Environment

Let us start by creating a sample table and inserting some values into it. For this article, we are going to create an employee table that contains information on the manager of each employee. We will later use this manager information to understand NOT IN and NOT EXISTS clauses. The following query creates the specified table and inserts some rows into it.

PL/SQL

CREATE TABLE employees (      employee_id number(10) NOT NULL,     employee_name varchar2(50) NOT NULL,     manager_id number(10) );   INSERT INTO employees (employee_id, employee_name, manager_id) SELECT 1, 'Jack', 2 FROM DUAL UNION ALL SELECT 2, 'Jill', NULL FROM DUAL UNION ALL SELECT 3, 'Jim', NULL FROM DUAL UNION ALL SELECT 4, 'Bill', 3 FROM DUAL UNION ALL SELECT 5, 'Ben', NULL FROM DUAL UNION ALL SELECT 6, 'Alex', 2 FROM DUAL UNION ALL SELECT 7, 'Andrew', 5 FROM DUAL UNION ALL SELECT 8, 'Chris', 5 FROM DUAL; SELECT * FROM employees;

Output:

Using the NOT IN Operator

To improve our understanding of NOT IN and NOT EXISTS clauses, we will try to find out all the employees which are not managers.

Firstly, let use a simple query using NOT IN to find the desired result. The following query selects all the employees which are not managers using a subquery.

Example of NOT IN:

SELECT * FROM employees WHERE employee_id NOT IN  (     SELECT manager_id FROM employees );

However, when we run the above query it doesn't return any values. This would mean that all employees are managers which is not true as we understand by seeing the data. So something must be wrong.

If we run the inner query independently, we will find out that it returns the following data:

Query:

SELECT manager_id FROM employees;

Output:

Explanation: As we can see it returns NULL values. Whenever there is NULL values in the inner query, NOT IN fails as it doesn't have the ability to compare to NULL value. Hence, it returns false for every record which results in no data being printed.

Now let's modify the NOT IN query to get the correct output. The following modified query only retrieves the manager_ids which are not NULL.

Query:

SELECT * FROM employees WHERE employee_id NOT IN  (     SELECT manager_id FROM employees      WHERE manager_id IS NOT NULL );

Output:

Explanation: As We can see in the above image, the query now works fine and returns the correct employee data.

Using the NOT EXISTS Operator

Unlike NOT IN, the NOT EXISTS operator does not encounter issues with NULL values. It’s generally more efficient and easier to use when dealing with subqueries, especially if you’re working with larger datasets.

Let's fetch employee records from the "employees" table where an employee does not have any corresponding entries indicating that they are a manager of another employee.

Query:

SELECT * FROM employees e WHERE NOT EXISTS  (     SELECT 1 FROM employees m     where m.manager_id=e.employee_id );

Output:

Explanation: As we can see from the above image, we didn't need to specially handle NULL values in the case of NOT EXISTS statement. It automatically gives NOT NULL values in the result.

Key Differences Between NOT IN and NOT EXISTS Operator

The following are some of the differences between NOT IN and NOT EXISTS:

Feature	NOT IN	NOT EXISTS
NULL Handling	Fails if the subquery contains `NULL` values, potentially leading to unexpected results.	Automatically handles `NULL` values, making it safer to use without additional conditions.
Execution Efficiency	Executes a full table scan for each record in the main query.	Uses indexes more effectively and stops processing once a match is found.
Performance with Joins	Generally slower for large datasets, especially with multiple conditions.	Faster with complex queries due to better optimization with joins and indexing.
Query Interpretation	Evaluates all records returned by the subquery, which can be slower.	Stops processing as soon as it finds a match, reducing workload and improving performance.
Best Use Cases	When data does not contain `NULL` values, and performance is not a priority.	Ideal for complex, large datasets where performance and NULL handling are critical.

Conclusion

Overall, After reading whole article both NOT IN and NOT EXISTS are used for solving subquery results in PL/SQL, they have distinct characteristics. NOT EXISTS is typically more efficient, especially for large datasets, due to its ability to terminate processing once a match is found and NOT IN can lead to unexpected results if the subquery contains NULL values We have seen in the above examples that the in NOT EXISTS we don't need to specify to contain NOT NULL value but in NOT IN we have to specify that to contain NOT NULL values.

How to Insert If Not Exists in SQL SERVER?

dvsingla28

Improve

Article Tags :

NOT IN vs NOT EXISTS in PL/SQL

Setting Up The Environment

Using the NOT IN Operator

Using the NOT EXISTS Operator

Key Differences Between NOT IN and NOT EXISTS Operator

Conclusion

Similar Reads