NOT IN vs. NOT EXISTS: What's the Difference? (Basic 1 - 16)

The NOT IN and NOT EXISTS operators in SQL are both used to return rows that do not match certain criteria. However, there are some key differences between the two operators.

NOT IN operator

The NOT IN operator is used to check if a value is not present in a list of values. The syntax for the NOT IN operator is as follows:

SQL

SELECT * FROM table_name WHERE column_name NOT IN (list_of_values);

The list_of_values can be a list of literal values or a subquery.

Example:

SQL

SELECT * FROM employees WHERE salary NOT IN (10000, 20000, 30000);

This query will return all employees who do not have a salary of 10000, 20000, or 30000.

NOT EXISTS operator

The NOT EXISTS operator is used to check if a row exists in another table that matches the criteria in the current table. The syntax for the NOT EXISTS operator is as follows:

SQL

SELECT * FROM table_name WHERE NOT EXISTS (subquery);

The subquery can be any valid SQL query.

Example:

SQL

SELECT * FROM employees WHERE NOT EXISTS (SELECT 1 FROM departments WHERE department_id = employees.department_id);

This query will return all employees who do not work in a department.

Differences between NOT IN and NOT EXISTS operators

The main difference between the NOT IN and NOT EXISTS operators is that the NOT IN operator checks for a specific value in a list of values, while the NOT EXISTS operator checks for the existence of any rows in another table that match the criteria in the current table.

Another difference between the two operators is that the NOT IN operator can be used to compare values to a list of literal values or a subquery, while the NOT EXISTS operator can only be used to compare values to a subquery.

Finally, the NOT EXISTS operator is generally faster than the NOT IN operator, especially when the subquery is large.

When to use NOT IN and NOT EXISTS operators

The NOT IN operator should be used when you need to check if a value is not present in a specific list of values. The NOT EXISTS operator should be used when you need to check for the absence of any rows in another table that match the criteria in the current table.

Here are some specific examples of when to use each operator:

• Use the NOT IN operator to check if a customer is not eligible for a discount.
• Use the NOT EXISTS operator to check if an order has not been placed.
• Use the NOT EXISTS operator to check if a user has not logged in.

Conclusion

The NOT IN and NOT EXISTS operators are both useful tools for returning rows that do not match certain criteria. By understanding the differences between the two operators, you can choose the right operator for your needs.

Example with NULL values

Consider the following table data:

Table: employees

ID | name | manager_id
-- | -- | --
1 | John | 2
2 | Jane | NULL
3 | Peter | 1

If we run the following query:

SQL

SELECT * FROM employees WHERE manager_id NOT IN (SELECT id FROM employees);

We will get no results, even though there are employees who do not have a manager. This is because the NOT IN operator cannot compare NULL values.

If we instead run the following query:

SQL

SELECT * FROM employees WHERE NOT EXISTS (SELECT 1 FROM employees WHERE manager_id = employees.id);

We will get the following results:

ID | name | manager_id
-- | -- | --
2 | Jane | NULL

This is because the NOT EXISTS operator can handle NULL values.

Conclusion

When using NULL values, it is important to use the NOT EXISTS operator instead of the NOT IN operator. The NOT EXISTS operator is more reliable and will produce the correct results.