Lesson 11
Asking whether a row exists: EXISTS and NOT EXISTS
For readers who finished lesson 10 and can join tables with JOIN and LEFT JOIN.
- A subquery in WHERE
- EXISTS
- Why not a join
- NOT EXISTS
- The NOT IN trap
- A subquery that returns one value
- 5 exercises
Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.
For a reference page on the same statements, read SQL EXISTS Operator Explained with Practical Examples.
The idea
EXISTS asks whether a subquery returns any row
A query in parentheses inside WHERE is a subquery. With EXISTS in front, the condition is true when the subquery returns at least one row. The subquery can name the outer row, as in o.customer_id = c.customer_id, so it is asked again for every customer.
Try it
Find the customers with a cancelled order
select c.customer_id, c.first_name, c.last_name
from customers c
where exists (
select 1
from orders o
where o.customer_id = c.customer_id
and o.status = 'cancelled'
);24 customers, each listed once.
select 1 is enough: EXISTS looks only at whether a row comes back, never at its columns.
Watch out
A join repeats a customer once per match
The same question as a join:
select count(*)
from customers c
join orders o on o.customer_id = c.customer_id
where o.status = 'cancelled';
| count |
|---|
| 100 |
100 rows for 24 customers: Fatima, with four cancelled orders, appears four times. A join answers with one row per pair; EXISTS keeps each customer once.
Try it
NOT EXISTS finds what has no match
NOT EXISTS is true when the subquery returns no row at all.
select d.department_id, d.name
from departments d
where not exists (
select 1
from employees e
where e.department_id = d.department_id
);Legal, department 7, has no employees.
Watch out
NOT IN against a list with NULL returns nothing
These are meant to be the employees who took no order:
select e.employee_id, e.first_name
from employees e
where e.employee_id not in (select employee_id from orders);
The answer has no rows, although 50 employees took no order. The 500 online orders have NULL as employee_id, and x not in (…, NULL) is never true, as lesson 4 showed for <>. NOT EXISTS has no such trap: the same question with it returns 50.
Try it
A subquery can return one value to compare with
A subquery that returns one row and one column can stand where a value would.
select order_id, customer_id, order_date from orders where order_date = (select max(order_date) from orders);
The 3 orders placed on the latest date, 2026-10-02.
Exercise 1 of 5
Show the id, first and last name of every customer with at least one cancelled order, each customer once.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select c.customer_id, c.first_name, c.last_name from customers c where exists (select 1 from orders o where o.customer_id = c.customer_id and o.status = 'cancelled')
Exercise 2 of 5
Show the id of every customer without orders, using NOT EXISTS.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select c.customer_id from customers c where not exists (select 1 from orders o where o.customer_id = c.customer_id)
Exercise 3 of 5
Show the id of every employee who took no order.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select e.employee_id from employees e where not exists (select 1 from orders o where o.employee_id = e.employee_id)
Exercise 4 of 5
How many customers have orders, all of them delivered? Answer with one number.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select count(*) from customers c where exists (select 1 from orders o where o.customer_id = c.customer_id) and not exists (select 1 from orders o where o.customer_id = c.customer_id and o.status <> 'delivered')
Exercise 5 of 5
Show the name of every product priced above the average product price.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select name from products where price > (select avg(price) from products)