intro
Let's everything you need to know about NOT EXISTS in SQL—an operator used to check whether certain rows do not exist in a dataset.
In the real world, sometimes the easiest way to identify objects is to think of them as something that does not exist or fall into a specific category. This is exactly why databases provide NOT EXISTS in SQL!
The NOT EXISTS SQL operator helps you find elements that do not exist in a sub-dataset. In this guide, you will learn what the operator is, how it works, when to use it, and what alternatives SQL offers.
Let’s dive in!
What is the NOT EXISTS SQL Operator?
In SQL, the NOT EXISTS operator checks if a subquery returns no rows. In other words, it tests for the existence of no records in a subquery. Specifically, it evaluates to:
Generally, the SQL NOT EXISTS operator is used in WHERE clauses to filter records that do not have corresponding entries in another table.
SQL NOT EXISTS: Syntax and Usage
This is the syntax of NOT EXISTS in SQL:
1
NOT EXISTS (subquery)
Where subquery is an arbitrary SELECT statement. For more details, read our guide on SQL subqueries.
1
DELETE FROM customers
2
WHERE NOT EXISTS (
3
SELECT * FROM orders
4
WHERE orders.customer_id = customers.id
5
);
This query removes customers who have never placed an order, while the subquery checks if a customer has any corresponding rows in the orders table. If no matching rows exist, NOT EXISTS evaluates to true, and the customer is deleted.
Thus, the subquery following the SQL NOT EXISTS operator is evaluated to check if it returns any rows. If at least one row is found, NOT EXISTS evaluates to false; if no rows are returned, it evaluates to true.
Notes:
An Example of Using NOT EXISTS in SQL
You now know what the SQL NOT EXISTS operator is, how it works, and how to use it. Still, to fully understand it, the best approach is through a complete walkthrough.
Note 1: The sample queries below are written in MySQL, but they can easily be adapted for PostgreSQL, SQL Server, Oracle, or any other database.
Note 2: The sample queries will be executed in DbVisualizer, a powerful, top-rated multi-database client. However, any other database client will work.
Let’s see NOT EXISTS in SQL in action!
Data Presentation
Suppose you have a simple company database with the following two tables:
employees contain the following data:
While departments store the following data:
As you can tell, the two tables are connected by a foreign key via the department_id
column. The relationship is visualized in the ERD-like schema generated by DbVisualizer Pro:
Note that not all employees are assigned to a department and not all departments have at least one employee.
Example #1: Checking If an Employee Is NOT in a Department
Assume you want to retrieve all employees who do not belong to a department that exists in the departments table. This is the NOT EXISTS SQL query you should run:
1
SELECT first_name, last_name
2
FROM employees e
3
WHERE NOT EXISTS (
4
SELECT *
5
FROM departments d
6
WHERE e.department_id = d.department_id
7
);
This query checks if there is no corresponding department for each employee by using NOT EXISTS in SQL. Then, it returns employees whose department_id does not exist in the departments table.
As expected, the result will be “Alice Green”:
Sure, you could have achieved the same result with a simple IS NOT NULL query on the department_id column. But what if the foreign key constraint is not set, and some department_id values no longer refer to valid records in the departments table? In this case, the NOT EXISTS query would be the go-to solution.
Example 2: Checking If a Department Has No Employees
Consider a scenario where you want to get all departments that do not have any employees assigned to them. Achieve that with NOT EXISTS :
1
SELECT department_name
2
FROM departments d
3
WHERE NOT EXISTS (
4
SELECT *
5
FROM employees e
6
WHERE e.department_id = d.department_id
7
);
The result will be “Finance”, as it is the only department with no employees:
Et voilà! You now understand NOT EXISTS in SQL.
Alternatives to NOT EXISTS in SQL
Thinking about the non-existence of something can be counterintuitive, so are there alternatives to NOT EXISTS? Well, it depends on the specific scenario, but there are often other, more intuitive solutions…
For example, consider the query that uses NOT EXISTS to find departments without employees:
1
SELECT d.department_id, d.department_name
2
FROM departments d
3
WHERE NOT EXISTS (
4
SELECT 1
5
FROM employees e
6
WHERE e.department_id = d.department_id
7
);
Let's explore equivalent queries using different approaches!
Alternative #1: Using NOT IN
1
SELECT d.department_id, d.department_name
2
FROM departments d
3
WHERE d.department_id NOT IN (
4
SELECT COALESCE(e.department_id, 0)
5
FROM employees e
6
);
This query uses the SQL NOT IN operator to filter departments where the department_id is not found in the list of department IDs that have employees. It selects all departments that do not have any employees by checking the employees table.
Note: Be cautious when using NOT IN, as it may behave incorrectly if the subquery returns NULL values (it might return no rows due to how NULL values are handled in NOT IN). To prevent that, the SQL COALESCE function is used to replace any NULL values in the department_id column with a default value (like 0), guaranteeing that the query executes correctly even if NULL values are encountered in the subquery (as it happens in this scenario.)
Alternative #2: Using EXCEPT
1
SELECT d.department_id, d.department_name
2
FROM departments d
3
EXCEPT
4
SELECT d.department_id, d.department_name
5
FROM departments d
6
JOIN employees e ON d.department_id = e.department_id;
The SQL EXCEPT operator returns the result of the first query, excluding the rows returned by the second query. In this case, it selects all departments and subtracts the ones that have employees (identified by a JOIN with the employees table).
This approach is useful for finding departments without employees, but it can be less efficient than NOT EXISTS for larger datasets since it requires two queries.
Alternative #3: Using a LEFT JOIN
1
SELECT d.department_id, d.department_name
2
FROM departments d
3
LEFT JOIN employees e ON d.department_id = e.department_id
4
WHERE e.department_id IS NULL;
The above query uses a LEFT JOIN to join the departments table with the employees table. It selects departments where there is no matching employee (e.department_id IS NULL).
This may appear as the most intuitive method to find departments without employees because it clearly shows the relationship between the two tables and handles the case of unmatched rows effectively.
Conclusion
In this guide, you learned about the SQL NOT EXISTS operator, how it works, and how to use it. As you saw, working with such operators becomes much easier with a visual database tool that allows you to explore data interactively—just like DbVisualizer.
DbVisualizer is a powerful database client that fully supports NOT EXISTS and all other SQL operators. It also offers advanced features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Download DbVisualizer for free today!
FAQ
Is the NOT EXISTS SQL operator part of standard SQL?
Yes, the NOT EXISTS operator is part of standard ANSI SQL. That is the reason why it is widely supported by major relational database management systems such as MySQL, PostgreSQL, SQL Server, and Oracle.
What is the difference between EXISTS and NOT EXISTS in SQL?
This is the main difference between EXISTS and NOT EXISTS in SQL:
These two clauses are opposites of each other.
SQL NOT EXISTS vs NOT IN: What is the difference?
See the difference in the SQL NOT EXISTS vs NOT IN table below:
| Feature | NOT EXISTS | NOT IN |
|---|---|---|
| Purpose | Checks for non-existence of rows in a subquery | Checks if a value is not in a list of values or subquery results |
| Subquery type | Often used with correlated subqueries | Works with both correlated and non-correlated subqueries |
| Use Case | Ideal for checking the non-existence of related rows | Suitable for static values or checking against a list of values |
Behavior with **NULL** | Safely deals with NULLs in the subquery | Lead to unexpected behavior if the subquery returns NULLs |
Can the SQL NOT EXISTS operator be used outside of a WHERE clause?
Yes, like any other SQL operator, the NOT EXISTS operator can be used in the SELECT clause and other parts of a query. Still, it is primarily used within the WHERE clause, as it is most effective for filtering results based on the existence (or non-existence) of rows returned by a subquery.
What does "NOT EXISTS" do in CREATE TABLE IF NOT EXISTS in SQL?
In the context of CREATE TABLE IF NOT EXISTS, the NOT EXISTS is not the same as the NOT EXISTS SQL operator used in subqueries. There, it checks whether a table already exists in the database. If the table does not exist, the query will create it. If the table already exists, no action is taken.
