NOT IN vs NOT EXISTS in SQLite

When querying a database in SQLite, there are situations where you need to filter out rows based on conditions that involve another set of values or a subquery. This is where the NOT IN and NOT EXISTS operators come into play.

While both operators achieve similar results, they do so in different ways and have different performance characteristics. In this article, we will see the what is NOT IN and NOT EXISTS operator along with their examples and their differences too.

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);

--to showb table data 
SELECT * FROM employees;

Output:

Table created

Explanation: Table has been created.

What is NOT IN Condition?

In SQL, the NOT IN condition is used to filter out and exclude records in a particular range. It is commonly used in the WHERE clause of a SELECT, UPDATE, DELETE, or MERGE statement to exclude rows based on a specific list of values.

Syntax:

query...
NOT IN (subquery/value_list)
query...

Example 1

The following query will output all the employees which doesn’t have an odd employee_id:

Query:

SELECT * FROM employees
WHERE employee_id NOT IN 
(
    SELECT employee_id FROM employees
    WHERE employee_id%2=1
);

Output:

Output

Explanation: This query retrieves all columns for employees whose employee_id is not an odd number. It uses a subquery to filter out rows with odd employee_id values from the employees table.

Example 2

Let’s fetch the information about all the employees whose name starts with any character other than ‘A’.

Query:

SELECT * FROM employees
WHERE employee_id NOT IN 
(
    SELECT employee_id FROM employees
    WHERE employee_name LIKE 'A%'
);

Output:

Output

Explanation: This query retrieves all columns for employees whose names do not start with ‘A’. It uses a subquery to filter out rows with names starting with ‘A’ from the employees table.

Example 3

The following query finds out all the employee which are manager-less.

Query:

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

Output:

Output

Explanation: This query retrieves all columns for employees who are not managers. It uses a subquery to filter out rows where the manager_id is not null, indicating that the employee is a manager

What is NOT EXISTS Operator?

In SQL, the NOT EXISTS condition is a Boolean condition that tests for the non-existence of rows in a subquery. It returns FALSE if the subquery returns at least one row, otherwise it returns TRUE. It is primarily used in WHERE clauses to exclude records.

Syntax:

query...
NOT EXISTS (subquery)
query...

Example 1

The following query will output all the employees who are not manager of any other employee.

Query:

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

Output:

Output

Explanation: This query retrieves all columns for employees who are not managers. It uses a correlated subquery to check if there is no other employee with the same employee_id as the manager_id in the employees table, indicating that the employee is not a manager.

Example 2

Let’s fetch the information about all the employees whose name starts with any character other than ‘A’.

Query:

SELECT * FROM employees e1
WHERE NOT EXISTS 
(
    SELECT 1 FROM employees e2
    WHERE e1.employee_id=e2.employee_id and e2.employee_name LIKE 'A%'
);

Output:

Output

Explanation: This query retrieves all columns for employees whose names do not start with ‘A’. It uses a correlated subquery to check if there is no other employee with the same employee_id and a name starting with ‘A’.

Example 3

The following query finds out all the employee which have no manager assigned to them.

Query:

SELECT * FROM employees e1
WHERE NOT EXISTS 
(
    SELECT 1 FROM employees e2
    WHERE e1.employee_id=e2.employee_id and e2.manager_id IS NOT NULL
);

Output:

Output

Explanation: This query retrieves all columns for employees who are not managers. It uses a correlated subquery to check if there is no other employee with the same employee_id as the e1 employee and a non-null manager_id, indicating that the employee is a manager.

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