Menu
CoddyTech

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:

idnamecity
1AriHaifa
2BoHaifa
3CleoEilat
4DeeEilat

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"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 icon

Challenge

Medium

Return 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground