Free SQL queries lessonsSQL queries · Stage 4 · Subqueries and window functions · Step 2

Does a matching row exist?

Test for the presence or absence of related rows with EXISTS and NOT EXISTS. About 9 minutes.

A subquery that looks at the outer row

Sometimes the inner question depends on the row being checked: does this customer have any orders? That is a correlated subquery, because it refers to a column of the outer row.

EXISTS (subquery) is true when the subquery returns at least one row, and it doesn't care what the rows contain, so people write SELECT 1. It stops at the first match.

EXISTS: true when the inner query finds any row.

In code

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

Customers that have at least one order

customers

id	name
1	Asha
2	Ravi
3	Meera

orders

id	customer_id
7	2
8	2
9	3

Asha: does any order have customer_id 1? No rows come back, so EXISTS is false. Dropped.

Move 1 of 4

Why not just JOIN?

A join pairs rows, so a customer with three orders comes out three times. You'd need DISTINCT to tidy up. EXISTS only asks whether a match is there, so each customer shows once.

NOT EXISTS is the clean way to say "has none". Unlike NOT IN, a NULL inside the subquery's data can't break it.

Join for 'has any': repeats the row for each match.

In code

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

Quick check

A customer has 3 orders. How many rows does each query return for that customer? (a) customers JOIN orders (b) customers WHERE EXISTS (their orders)

A(a) 3 rows, (b) 1 row
B(a) 1 row, (b) 1 row
C(a) 1 row, (b) 3 rows

Show the answer

(a) 3 rows, (b) 1 row. A join gives one row per matching order. EXISTS just asks whether any match exists.

Your problem

Active members

The members table has one row per library member and visits has one row per visit. List the name of every member who has visited at least once, each name once, A to Z.

Tables

members(id, name)
visits(id, member_id, day)

Up to 1,000 members · names are unique

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.