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:

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:

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:

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

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.