NOT IN vs NOT EXISTS in PL/SQL

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. It is widely used for developing stored procedures, functions, triggers, and other database objects to enhance the functionality and performance of database applications. In this article, We will understand the NOT IN vs NOT EXISTS operator along with the examples and so on.

Setting Up 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.

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;

Let us now print the content of the table using the following query:

SELECT * FROM employees;

Output:

Table

Example of 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.

Query:

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:

Manager id

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:

Employee which are not managers

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

Example of NOT EXISTS Operator

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:

Employee which are not managers

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.

Difference Between NOT IN vs NOT EXISTS Operator

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

NOT IN	NOT EXISTS
NOT IN cannot compare NULL values	NOT EXISTS can handle NULL values
Queries containing NOT IN perform nested full table scan	NOT EXISTS can use indexes.

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.