CodeRang
SQL topics

SQL NOT EXISTS: find rows without a matching record

SQL NOT EXISTS returns rows for which a correlated subquery finds no matching record. This anti join pattern is useful for customers without orders, products without sales, empty groups, and other missing-relationship checks.

Updated · SQL Arena team

SQL for QA testers trackAll problems

What you will practice

  • Find unmatched rows with NOT EXISTS
  • Write a correctly correlated subquery
  • Express the same anti join with LEFT JOIN ... IS NULL
  • Avoid NOT IN problems caused by NULL
  • Check for absence without unnecessary GROUP BY

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Practice task: customers with no orders

Return customers from customers for whom no row exists in orders with the same customer_id. Output only customer_name and city.

Query 1PostgreSQLv16
SELECT c.customer_name,
       c.city
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
)
ORDER BY c.customer_name;
Submit
Resultcustomersorders
Answer accepted

How SQL NOT EXISTS works as an anti join

The outer query scans customers. For each current customer row, PostgreSQL evaluates the subquery inside NOT EXISTS. The condition o.customer_id = c.id correlates that inner check with the current customer. SELECT 1 is conventional because EXISTS only cares whether at least one row is found; it does not use the selected value.

When a matching order exists, EXISTS is true and NOT EXISTS becomes false, so that customer is removed. When no matching order exists, the subquery returns no rows and the customer remains. In this dataset Ava, Cara, Emma, and Grace have orders, leaving Ben, Dylan, and Felix in the result.

You can express the same logic with LEFT JOIN orders o ON o.customer_id = c.id followed by WHERE o.id IS NULL. That is also an anti join. NOT EXISTS often reads closer to the requirement, while LEFT JOIN ... IS NULL can fit naturally into a query that already uses joins. NOT IN, however, needs extra care because a NULL returned by its subquery can change the truth value of the comparison.

EXISTS is true when its subquery finds at least one matching row, while NOT EXISTS reverses that result. The expression selected inside the subquery is usually irrelevant because these operators test row existence rather than returned values. In a correlated anti join, a condition such as o.customer_id = c.id connects the inner query to the current outer row. If a match is found, NOT EXISTS is false; if no match is found, the outer row remains in the result.

SQLPostgreSQL v16
SELECT c.customer_name,
       c.city
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
)
ORDER BY c.customer_name;
Query result
customer_namecity
BenYork
DylanLeeds
FelixBath

Common SQL NOT EXISTS mistakes

01

Forgetting the correlation

If the subquery does not reference c.id, it checks whether any order exists anywhere. As soon as orders contains one row, NOT EXISTS is false for every customer.

02

Using NOT IN with nullable data

c.id NOT IN (SELECT customer_id FROM orders) can behave unexpectedly when the subquery contains NULL. Either filter NULL explicitly or use NOT EXISTS for a clearer absence check.

03

Testing a nullable joined column

With LEFT JOIN ... IS NULL, test a right-side column that cannot be NULL when a match exists, such as the primary key o.id. Testing a nullable business column can confuse “no joined row” with “joined row containing NULL”.

Practice problems on this topic

SQL for QA testers track

Once the basic customer-order pattern is clear, practice the same logic on different schemas. Exercises such as products nobody bought or books with no available copies make the anti join pattern automatic. Medium tasks add conditions such as a year or category, forcing you to place filters on the correct side of the absence test. For data validation, continue with the “SQL for QA Engineers” track. SQL Arena has 1,192 tasks, with answers checked on hidden data rather than only the visible sample.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL NOT EXISTS?

SQL NOT EXISTS is a condition that becomes true when its subquery returns no rows. It is commonly used to find records that do not have a related record in another table.

How does SQL NOT EXISTS work?

The subquery usually contains a condition connecting it to the current outer row. If the subquery finds a match, NOT EXISTS is false; if it finds no match, NOT EXISTS is true.

How do you use SQL NOT EXISTS for an anti join?

Select rows from the main table and place a correlated NOT EXISTS subquery in the WHERE clause. The correlation checks for a related row in the second table, and only unmatched outer rows are retained.

What is the difference between SQL NOT EXISTS and EXISTS?

EXISTS is true when at least one matching row is found. NOT EXISTS is true only when no matching row is found.

What is the difference between SQL NOT EXISTS and LEFT JOIN?

Both can express an anti join. A LEFT JOIN version normally filters for NULL on the joined side, whereas NOT EXISTS directly states that no matching row may exist.

See also

Practice SQL NOT EXISTS and anti joins

Use SQL Arena to solve anti join exercises across different schemas and data shapes. The free plan includes 292 tasks for PostgreSQL, MySQL, ClickHouse, and BigQuery with no card required.

Practice anti joins

More practice from CodeRang