EXISTS Subquery in SQL

Level 4135 keystrokes

EXISTS is true when its subquery returns at least one row; here it finds customers with an order over 500.

SQL code

-- Return the name and email of matching customers
1SELECT
2 name,
3 email
4FROM
-- c is a short alias for customers, used inside the subquery
5 customers c
-- Keep the customer if the subquery finds at least one row
-- Each customer appears once, however many big orders they have
6WHERE EXISTS (
-- The selected value does not matter for EXISTS, so 1 is a common choice
7 SELECT 1
8 FROM orders o
-- Correlated: o.customer_id is compared with c.id of the current outer row
9 WHERE o.customer_id = c.id
-- Only orders over 500 count
10 AND o.total > 500
11)
12ORDER BY
13 name;

Output

+-------+-------------------+
| name  | email             |
+-------+-------------------+
| Alice | alice@example.com |
| Bob   | bob@example.com   |
+-------+-------------------+
2 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • .× 3
  • _× 1
  • ,× 1
  • ;× 1
  • (× 1
  • )× 1
  • =× 1
  • >× 1

More SQL examples at level 4

← SQL typing practice