Subqueries

01The problem

Finance wants to review unusually expensive bookings:

"Show me every shipment that cost more than our average shipment."

To answer it, you need the average first, and then the shipments above it. You could run two queries and copy the number across by hand. But next month the average changes, and your copied number is wrong.

02The concept

A subquery is a query inside another query, written in brackets. The inner query runs first and its result is used by the outer one.

There are three common places to put one:

1. In WHERE, to compare against a calculated value or a list.
2. In SELECT, to show a calculated value on every row.
3. In FROM, to treat a result as if it were a table.

A subquery that returns a single value is called a scalar subquery. One that returns a list of values works with IN.

03Example

SELECT shipment_id, booking_date, freight_charge
FROM shipments
WHERE freight_charge > (SELECT AVG(freight_charge) FROM shipments)
ORDER BY freight_charge DESC;

04Walkthrough

• (SELECT AVG(freight_charge) FROM shipments) runs first and returns one number.
• The outer query keeps shipments whose charge is above that number.
• Because the average is calculated each time the query runs, the report stays correct as data changes.

A subquery that returns a list works with IN. Here, customers in the Pharmaceuticals industry, found by ID:

SELECT shipment_id, booking_date, containers
FROM shipments
WHERE customer_id IN (
  SELECT customer_id FROM customers WHERE industry = 'Pharmaceuticals'
);

A subquery in FROM builds a temporary table you can query again. This finds the average number of shipments per customer, which needs two levels of aggregation:

SELECT ROUND(AVG(shipment_count), 1) AS avg_shipments_per_customer
FROM (
  SELECT customer_id, COUNT(*) AS shipment_count
  FROM shipments
  GROUP BY customer_id
) AS per_customer;

The inner query gives one row per customer; the outer query averages those counts. In most databases a subquery in FROM needs an alias, here per_customer.

05Practice

06Challenge

07Check your understanding