NOT IN vs NOT EXISTS in PostgreSQL

PostgreSQL is one of the most advanced general-purpose object-relational database management systems and is open-source. Being an open-source software, its source code is available under the PostgreSQL license, a liberal open-source license. Anyone with the right skills can use, modify, and distribute PostgreSQL in any form. As it is highly stable, very low effort is required to maintain this DBMS. 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
CREATE TABLE employees
( 
    employee_id INTEGER NOT NULL,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INTEGER
);

-- insert
INSERT INTO employees VALUES (1, 'Jack', 2);
INSERT INTO employees VALUES (2, 'Jill', NULL);
INSERT INTO employees VALUES (3, 'Jim', NULL);
INSERT INTO employees VALUES (4, 'Bill', 3);
INSERT INTO employees VALUES (5, 'Ben', NULL);
INSERT INTO employees VALUES (6, 'Alex', 2);
INSERT INTO employees VALUES (7, 'Andrew', 5);
INSERT INTO employees VALUES (8, 'Chris', 5);

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

SELECT * FROM employees;

Output:

Initial data

Example of NOT IN vs NOT EXISTS in PostgreSQL

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 data

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:

query output

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:

query 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.

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 PostgreSQL, 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.