Check Existence
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 7 of 25.
EXISTS asks whether a subquery produces any row. A correlated subquery refers to the current outer row. Unlike a join, EXISTS does not multiply the outer row when several matches exist.
Input table customers:
id | name | city |
|---|---|---|
1 | Ari | Haifa |
2 | Bo | Haifa |
3 | Cleo | Eilat |
4 | Dee | Eilat |
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT c.id, c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id AND o.amount >= 40) ORDER BY c.id;Only Cleo has an order of at least 40. The selected value 1 is incidental: EXISTS cares about the presence of a row. The correlation ties the check to each customer.
Use EXISTS for a yes-or-no relationship check without duplicating outer rows.
Challenge
MediumReturn id and name once per customer who has at least one pending order. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
2Queries in Stages
Scalar SubqueriesCheck ExistenceName a Query with WITHChain CTEsAbove the Customer AveragePractice on your own: PostgreSQL playground