Data · SQL

How to read a subquery in SQL

Published

What is the average of 10, 20, and 60?

From an average to a table built inside FROM, in five steps:

  1. Replace the part in parentheses with its value
  2. Filter on a list with IN
  3. Choose between = and IN
  4. Read a table built inside FROM
  5. Practice

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)
orders
idamount
101$20
102$60
103$40
104$50
105$30
subquery
AVG(amount)
$40
result
idamount
102$60
104$50
  1. AVG takes the average of the amount column. Add up the five amounts, then divide by 5.

    $

  2. Replace the part in parentheses with 40. The query keeps each order whose amount is over $40.

    rows

  1. SELECT id, amount
    FROM orders
    WHERE amount > (  SELECT AVG(amount)
      FROM orders)
    orders
    idamount
    101$60
    102$10
    103$70
    104$20
    105$90

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)
customers
idname
1Emma
2Noah
3Mia
4Liam
orders
idcustomer_idamount
1011$30
1022$70
1033$20
1042$60
1054$80
subquery
customer_id
2
2
4
result
name
Noah
Liam
  1. Which orders does the subquery keep? Tap them in the table.

    It keeps the orders over $50 and returns their customer_id.

  2. The list is 2, 2, 4. Go through the customers one at a time: each one comes out if their id is in the list.

    rows

  1. SELECT name
    FROM customers
    WHERE id IN (  SELECT customer_id
      FROM orders
      WHERE amount > 50)
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1013$60
    1021$20
    1034$40
    1041$90
    1052$50

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)
customers
idname
1Emma
2Noah
3Mia
4Liam
orders
idcustomer_idamount
1011$30
1022$70
1033$20
1042$60
1054$80
subquery
customer_id
4
result
name
Liam
  1. It returns one row per order over $70.

  2. What does the query return if the subquery keeps the orders over $50?

  1. SELECT name
    FROM customers
    WHERE id = (  SELECT customer_id
      FROM orders
      WHERE amount > 60)
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$50
    1022$40
    1033$90
    1044$30
    1052$60

    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
orders
idcustomer_idamount
1011$40
1022$90
1033$60
1041$70
1053$50
t
customer_idtotal
1$110
2$90
3$110
result
customer_idtotal
1$110
3$110
  1. GROUP BY customer_id makes one row per customer. SUM adds up the amounts of that customer’s orders.

    $

  2. Do the same for customers 2 and 3, then keep the totals over $100.

    rows

  1. SELECT customer_id, total
    FROM (  SELECT customer_id,
        SUM(amount) AS total
      FROM orders
      GROUP BY customer_id) AS t
    WHERE total > 100
    orders
    idcustomer_idamount
    1012$60
    1024$30
    1031$80
    1042$50
    1053$90
    1064$70

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.

  1. SELECT id, amount
    FROM orders
    WHERE amount > (  SELECT AVG(amount)
      FROM orders)
    orders
    idamount
    101$40
    102$10
    103$60
    104$30