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:

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:

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:

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.

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.