Lessons › SQL › Chapter 5: Combining tables

SQL lesson

Subqueries

A subquery is a question nested inside another question - you get one result first, then feed it straight into the outer query without any manual steps in between. Like doing a quick back-of-envelope calculation before writing your final answer, except the database handles both calculations at once.

Example

Answering Benchmark Questions with Subqueries

SELECT order_id, customer_id, revenue
FROM orders
WHERE revenue > (SELECT AVG(revenue) FROM orders)

Result:

Step by step

1. Run the query without AVG so you can see what the unfiltered values look like.

   SELECT order_id, customer_id, revenue
   FROM orders
   WHERE revenue > (SELECT revenue FROM orders);

2. AVG returns the mean of the values in a column.

   SELECT order_id, customer_id, revenue
   FROM orders
   WHERE revenue > (SELECT AVG(revenue) FROM orders);

3. AS gives the AVG result a friendly column name so the output reads cleanly.

   SELECT order_id, customer_id, revenue
   FROM orders
   WHERE revenue > (SELECT AVG(revenue) AS avg_result FROM orders);

Practice

Try each one in your head first, then open the answer.

SELECT * FROM orders WHERE revenue > (SELECT AVG(revenue) FROM orders)

SELECT * FROM customers WHERE email > (SELECT AVG(email) FROM customers)

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)

SELECT * FROM store_sales WHERE revenue > (SELECT AVG(revenue) FROM store_sales)

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)