Data · SQL
How to read a subquery in SQL
What is the average of 10, 20, and 60?
How many of these three numbers are above the average?
From an average to a table built inside FROM, in five steps:
How do you select rows above the average in SQL?
Read a subquery from the inside out: work out the part in parentheses first, then replace it with what it returns. If it returns 37, the query reads WHERE amount > 37.
Let’s take five orders.
SELECT id, amount
FROM orders
WHERE amount > ( SELECT AVG(amount)
FROM orders)| id | amount |
|---|---|
| 101 | $20 |
| 102 | $60 |
| 103 | $40 |
| 104 | $50 |
| 105 | $30 |
| AVG(amount) |
|---|
| $40 |
| id | amount |
|---|---|
| 102 | $60 |
| 104 | $50 |
SELECT id, amount FROM orders WHERE amount > ( SELECT AVG(amount) FROM orders)orders id amount 101 $60 102 $10 103 $70 104 $20 105 $90 SELECT id, amount FROM orders WHERE amount > ( SELECT AVG(amount) FROM orders)orders id amount 101 $30 102 $50 103 $80 104 $20 105 $50 106 $70
How do you use IN with a subquery?
When the part in parentheses returns a list, IN keeps each row whose value is in the list. If the list is 8, 8, 11, the query keeps customer 8 and customer 11, once each.
Let’s take four customers and their orders.
SELECT name
FROM customers
WHERE id IN ( SELECT customer_id
FROM orders
WHERE amount > 50)| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| 4 | Liam |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $30 |
| 102 | 2 | $70 |
| 103 | 3 | $20 |
| 104 | 2 | $60 |
| 105 | 4 | $80 |
| customer_id |
|---|
| 2 |
| 2 |
| 4 |
| name |
|---|
| Noah |
| Liam |
Which orders does the subquery keep? Tap them in the table.
It keeps the orders over $50 and returns their
customer_id.
SELECT name FROM customers WHERE id IN ( SELECT customer_id FROM orders WHERE amount > 50)customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 3 $60 102 1 $20 103 4 $40 104 1 $90 105 2 $50 SELECT name FROM customers WHERE id IN ( SELECT customer_id FROM orders WHERE amount > 50)customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 102 5 $30 104 1 $40 106 3 $50 103 2 $60 105 4 $70 101 2 $80 107 5 $90
When should you use = or IN with a subquery?
= expects a single row. If the part in parentheses returns 7 and 9, WHERE id = (…) stops with an error, while WHERE id IN (…) keeps customers 7 and 9.
Let’s take the same tables, with = and orders over $70.
SELECT name
FROM customers
WHERE id = ( SELECT customer_id
FROM orders
WHERE amount > 70)| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| 4 | Liam |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $30 |
| 102 | 2 | $70 |
| 103 | 3 | $20 |
| 104 | 2 | $60 |
| 105 | 4 | $80 |
| customer_id |
|---|
| 4 |
| name |
|---|
| Liam |
What does the query return if the subquery keeps the orders over $50?
SELECT name FROM customers WHERE id = ( SELECT customer_id FROM orders WHERE amount > 60)customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $50 102 2 $40 103 3 $90 104 4 $30 105 2 $60 What does the query return?
SELECT name FROM customers WHERE id = ( SELECT customer_id FROM orders WHERE amount > 40)customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 4 $70 102 1 $30 103 4 $50 104 2 $20 105 3 $40 What does the query return?
How do you use a subquery in FROM?
In FROM, the part in parentheses builds a table, and the outer query reads it like any other table. If that table gives customer 7 a total of $130, WHERE total > 100 keeps customer 7, even if none of their orders is over $100.
Let’s take five orders from three customers.
SELECT customer_id, total
FROM ( SELECT customer_id,
SUM(amount) AS total
FROM orders
GROUP BY customer_id) AS t
WHERE total > 100| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 2 | $90 |
| 103 | 3 | $60 |
| 104 | 1 | $70 |
| 105 | 3 | $50 |
| customer_id | total |
|---|---|
| 1 | $110 |
| 2 | $90 |
| 3 | $110 |
| customer_id | total |
|---|---|
| 1 | $110 |
| 3 | $110 |
SELECT customer_id, total FROM ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) AS t WHERE total > 100orders id customer_id amount 101 2 $60 102 4 $30 103 1 $80 104 2 $50 105 3 $90 106 4 $70 SELECT customer_id, total FROM ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) AS t WHERE total > 100orders id customer_id amount 106 4 $30 101 3 $40 107 1 $50 103 5 $60 102 1 $70 105 3 $80 104 2 $90
How do you read a SQL subquery quickly?
It goes faster if you start with the part in parentheses: work it out on its own, keep what it returns in mind, then read the query around it.
SELECT id, amount FROM orders WHERE amount > ( SELECT AVG(amount) FROM orders)orders id amount 101 $40 102 $10 103 $60 104 $30 SELECT name FROM customers WHERE id IN ( SELECT customer_id FROM orders WHERE amount > 50)customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $30 102 3 $80 103 2 $40 104 4 $60 105 1 $90 SELECT customer_id, total FROM ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) AS t WHERE total > 100orders id customer_id amount 101 1 $90 102 3 $20 103 2 $40 104 4 $80 105 2 $70 106 3 $60 SELECT name FROM customers WHERE id IN ( SELECT customer_id FROM orders WHERE amount > 50)customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 106 4 $10 102 3 $20 105 2 $50 104 1 $60 101 5 $70 103 5 $90