Lesson Description
Apply EXISTS and advanced filtering patterns to test related data without overcomplicating joins. This lesson improves query intent and helps you avoid false positives in relationship checks.
Lesson 04
Lesson Description
Apply EXISTS and advanced filtering patterns to test related data without overcomplicating joins. This lesson improves query intent and helps you avoid false positives in relationship checks.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
Every filter so far in this course has looked at columns of the row itself: its salary, its status, whether its state is NULL. Many business questions, though, depend on other rows in other tables:
The customer row does not contain a "has ordered" column. The answer lives in orders. EXISTS lets you filter one table based on whether matching rows exist in another, without pulling any columns from that other table and without producing duplicates.
SELECT columns
FROM outer_table t
WHERE EXISTS (
SELECT 1
FROM other_table o
WHERE o.foreign_key = t.id -- the correlation
);
-- the opposite
WHERE NOT EXISTS ( ... );
The part in parentheses is a subquery, a complete SELECT nested inside another query. You will study subqueries in depth in a later course; for EXISTS you only need two ideas:
EXISTS (subquery) is true if the subquery returns at least one row, and false if it returns none. It is never unknown, even when NULLs are involved.o.customer_id = c.id). This is called a correlated subquery: conceptually, it is re-evaluated for each customer, asking "does this customer have any orders?"What the subquery selects does not matter. SELECT 1 is the convention, but SELECT * or even SELECT NULL gives exactly the same result, because EXISTS only checks whether a row came back.
SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
This returns 20 of the 22 customers. Many of them have two or three orders, but each customer appears once. That is the key difference from a join (covered in the Joins course): joining customers to orders produces 50 rows, one per order, and you would have to remove duplicates yourself. EXISTS answers a yes/no question per customer.
Flip it with NOT EXISTS:
SELECT first_name, last_name, signup_date
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
2 rows: Priya Okafor (signed up 2025-01-14) and Mateo Lindqvist (2025-02-03). These are the two most recent sign-ups, a natural target list for a "complete your first purchase" email. This "find the rows with no match" pattern is called an anti-join, and it is one of the most useful things you can do in SQL.
The same pattern works anywhere. In the HR sandbox, a department with no employees:
SELECT id, name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
1 row: department 110, Accounting. In the hospital sandbox, the same shape against doctors and appointments finds Marcus Wright, the one emergency physician with no appointments booked.
The subquery is a full SELECT, so you can filter it further. Business question: which customers have had an order cancelled?
SELECT first_name, last_name, country
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.status = 'cancelled'
);
3 rows: Isabella Rodriguez (France), Aiden Martin (Brazil) and Noah Brown (Canada). The extra condition goes inside the subquery, because it describes the related row, not the customer.
The hospital version: which patients have missed an appointment?
SELECT first_name, last_name, city
FROM patients p
WHERE EXISTS (
SELECT 1
FROM appointments a
WHERE a.patient_id = p.id
AND a.status = 'no-show'
);
2 rows: Susan Thompson (Bellevue) and Karen Allen (Portland).
The employees table has a manager_id column pointing to another row in the same table. Who manages at least one person?
SELECT first_name, last_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM employees sub
WHERE sub.manager_id = e.id
);
The two aliases (e for the outer row, sub for the inner one) let you use the same table twice without ambiguity. The answer is 9 managers, among them King, Kochhar, Hunold and Weiss. NOT EXISTS on the same query returns the other 31, the individual contributors.
You might reach for NOT IN to find the non-managers:
SELECT first_name, last_name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);
This returns 0 rows. No error, no warning, just nothing. The subquery returns every manager_id, and Steven King's is NULL because he has no manager. As you saw in the NULL lesson, x NOT IN (..., NULL) can never be true, so every row is rejected.
NOT EXISTS does not have this problem. It returns the correct 31 rows, because a NULL manager_id simply never satisfies sub.manager_id = e.id, so it is treated as "no match" rather than "unknown". You can patch NOT IN by adding WHERE manager_id IS NOT NULL to the subquery, which also gives 31 rows, but you have to remember every time. NOT EXISTS is safe by construction.
Positive IN is not affected: WHERE id IN (SELECT manager_id FROM employees) correctly returns the 9 managers, because a NULL in the list only adds an unknown to an OR chain that already has a true in it.
Although a correlated subquery reads as "run once per outer row", PostgreSQL rarely executes it that way. The planner recognizes EXISTS as a semi-join (keep outer rows that have at least one match) and NOT EXISTS as an anti-join (keep outer rows that have none), and picks an efficient join strategy. Run EXPLAIN on Example 2 and the plan contains a Hash Right Anti Join between orders and customers: one pass over each table, not 22 separate lookups.
Two consequences:
EXISTS can stop at the first match for each outer row. It never needs to count all of a customer's orders.orders.customer_id, appointments.patient_id) helps when tables are large, since the planner may then probe the index for each outer row instead.| Approach | Duplicates? | NULL-safe for exclusion? | Good for |
|---|---|---|---|
EXISTS / NOT EXISTS | never | yes | "has any" / "has none" checks |
IN (subquery) | never | n/a | simple membership |
NOT IN (subquery) | never | no, one NULL returns 0 rows | only with guaranteed non-NULL columns |
JOIN | yes, one row per match | n/a | when you need columns from both tables |
Mistake 1: forgetting the correlation.
SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.status = 'cancelled'
);
This returns all 22 customers. Without o.customer_id = c.id, the subquery asks "is there any cancelled order anywhere?", which is true, so it is true for every customer. Fix: always link the inner table to the outer row.
Mistake 2: putting the related filter outside. Writing WHERE EXISTS (...) AND status = 'cancelled' on the customers query fails with ERROR: column "status" does not exist, because status belongs to orders, not customers, and the outer query cannot see inside the subquery. Conditions about the related row go inside the subquery.
Mistake 3: using NOT IN on a nullable column. As Example 5 showed, it silently returns nothing. Prefer NOT EXISTS.
Mistake 4: thinking SELECT * makes EXISTS slower. The select list inside EXISTS is ignored. SELECT 1 is a readability convention, not an optimization.
EXISTS and NOT EXISTS are standard SQL and behave identically in MySQL, SQL Server, SQLite and Oracle, including the NULL-safety advantage over NOT IN. Older MySQL versions optimized EXISTS less well than PostgreSQL, but on current versions the guidance is the same: use NOT EXISTS for anti-joins.
The exercises below this lesson ask you to:
EXISTS and NOT EXISTS.All four require an ORDER BY last_name, so remember that ordering applies to the outer query.
EXISTS (subquery) is true when the subquery returns at least one row; it ignores what is selected.o.customer_id = c.id), or it answers a different question.EXISTS never duplicates outer rows, unlike a join.NOT EXISTS is the safe anti-join. NOT IN returns 0 rows if the subquery yields any NULL.EXISTS as a semi-join and NOT EXISTS as an anti-join, so they are efficient.EXISTS asks "is there at least one related row?", which is how you find customers who have ordered, doctors with appointments, or departments with no staff. It stops at the first match and is NULL-safe, so it is often both faster and more correct than IN or a join for yes/no questions.
Customers who have placed an order
SELECT c.first_name, c.last_name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Each customer appears once, no matter how many orders they placed, so there is no need for DISTINCT.
Departments with no employees (anti-join)
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.id
);NOT EXISTS finds rows with no match and behaves correctly even if some employees have a NULL department_id.
WHERE id NOT IN (SELECT department_id FROM employees) return zero departments even though some are empty?If any employee has a NULL department_id, every NOT IN comparison becomes unknown and no row passes. Use NOT EXISTS.
Concept check
3 quick questions. One at a time. Instant score at the end.
From the store sandbox, return first_name and last_name of customers who have placed at least one order. Use EXISTS. Order by last_name.
No results yet
Run a query to see results here
From the store sandbox, return first_name and last_name of customers who have never placed an order. Use NOT EXISTS. Order by last_name.
No results yet
Run a query to see results here
From the hospital sandbox, return first_name and last_name of patients who appear in the appointments table at least once. Use EXISTS. Order by last_name.
No results yet
Run a query to see results here
Return first_name and last_name of HR employees who are the manager_id for at least one other employee. Use EXISTS. Order by last_name.
No results yet
Run a query to see results here
Final checkpoint
Pass mark: 80%. Review each explanation after you submit.
1. Which clause should you use to filter grouped results?
2. What is the safest way to avoid accidental row multiplication in joins?
3. Which feature is best for ranking rows within partitions?
4. Why should final analytical queries usually include ORDER BY?
5. When query logic gets complex, what improves readability most?