lesson 29 Dates, strings & subqueries · intermediate

Ask whether a row exists

Test whether matching rows exist without duplicating results.

the idea

EXISTS is true when its subquery returns at least one row. A correlated subquery refers to the outer row. NOT EXISTS is useful for finding missing relationships.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customers4 columns · 8 rows

idinteger · key	nametext	citytext	joined_attext
1	Alice	Chicago	2024-01-10
2	Ben	Austin	2024-02-12
3	Cara	Seattle	2024-03-05
4	Dev	Chicago	2024-04-20
5	Elena	NULL	2024-05-01

showing 5 of 8 rows

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

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

Watch for: A join may duplicate customer names; EXISTS preserves one outer row.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Check for a matching paid order. EXISTS asks whether at least one related row satisfies the inner filter. It does not multiply the outer customer rows.
step-1.sql
sql›
```
SELECT c.id, c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id
LIMIT 12;
```
2
Keep customers with a match. EXISTS returns true when the correlated subquery finds one or more rows. The outer SELECT lists those customers once.
step-2.sql
sql›
```
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'paid'
);
```