Skip to main content
Filtering & Logic
4 / 4
0%

EXISTS — test for related rows

0/4 solved4 to unlock next
Prev★ Final lesson

Lesson 04

EXISTS — test for related rows

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.

Filtering on what lives in another table

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:

  • "Which customers have placed at least one order?"
  • "Which customers signed up but never bought anything?"
  • "Which departments have no employees at all?"

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.

Syntax

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:

  1. 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.
  2. The subquery refers to the outer row (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.

Worked examples

Example 1: customers who have ordered

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.

Example 2: customers who never ordered

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.

Example 3: adding conditions inside the subquery

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).

Example 4: a table that refers to itself

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.

Example 5: the NOT IN trap with real data

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.

Under the hood

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.
  • An index on the correlated column (orders.customer_id, appointments.patient_id) helps when tables are large, since the planner may then probe the index for each outer row instead.
ApproachDuplicates?NULL-safe for exclusion?Good for
EXISTS / NOT EXISTSneveryes"has any" / "has none" checks
IN (subquery)nevern/asimple membership
NOT IN (subquery)neverno, one NULL returns 0 rowsonly with guaranteed non-NULL columns
JOINyes, one row per matchn/awhen you need columns from both tables

Common mistakes

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.

Other databases

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.

Practice

The exercises below this lesson ask you to:

  • List store customers who have placed at least one order, and then those who never have, using EXISTS and NOT EXISTS.
  • List hospital patients who appear in the appointments table.
  • List HR employees who manage at least one other employee. You will need two aliases for the same table.

All four require an ORDER BY last_name, so remember that ordering applies to the outer query.

Key takeaways

  • EXISTS (subquery) is true when the subquery returns at least one row; it ignores what is selected.
  • Correlate the subquery to the outer row (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.
  • PostgreSQL plans EXISTS as a semi-join and NOT EXISTS as an anti-join, so they are efficient.

EXISTS — test for related rows: in depth

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.

Worked examples

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.

Common mistakes

  • Using NOT IN (SELECT department_id FROM employees): one NULL in that column makes the whole result empty. NOT EXISTS does not have this problem.
  • Forgetting to correlate the subquery. Without WHERE o.customer_id = c.id, EXISTS is true for every customer as soon as any order exists.
  • Selecting columns inside EXISTS for no reason. The select list is ignored; SELECT 1 is the convention.

Other databases

  • EXISTS and NOT EXISTS behave the same in all major databases.
  • Some databases also offer semi-join shortcuts (for example LEFT SEMI JOIN in Spark SQL); EXISTS is the portable form.
Check yourself: Why might 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

Quick check: EXISTS — test for related rows

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Customers who have placed orders (EXISTS)

Commerce Operations IntelligenceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Customers with no orders (NOT EXISTS)

Commerce Operations IntelligenceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Patients who have had at least one appointment

Clinical Care PerformanceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Employees who manage at least one other employee

Enterprise Workforce AnalyticseasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Final checkpoint

End-of-course high-level quiz

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?

Filtering & Logic
4 / 4
0%

EXISTS — test for related rows

0/4 solved4 to unlock next
Prev★ Final lesson