Lessons › SQL › Chapter 5: Joins, subqueries and CTEs

SQL lesson

More ways to use a subquery

One subquery gets you one number SQL works out for you. Some questions need two: "above the average but below the biggest". Each subquery has its own brackets, and SQL works out both before it checks any row. A subquery can also be a whole table. Put it in brackets after FROM, give it a name with AS, and use it like any table. And in HAVING, a subquery gives one number to compare each group's total with.

Example

Two at once, side by side, or as a table

SELECT *
FROM orders
WHERE revenue > (SELECT AVG(revenue) FROM orders)
  AND revenue < (SELECT MAX(revenue) FROM orders)

Result:

Step by step

1. In WHERE, a subquery gives a number to compare with. You can use two at once, joined with AND.

   SELECT * FROM orders WHERE revenue > (SELECT AVG(revenue) FROM orders) AND revenue < (SELECT MAX(revenue) FROM orders)

2. A subquery in SELECT gives one number. Put it next to each row's columns and every row gets the same number.

   SELECT order_id, revenue, (SELECT AVG(revenue) FROM orders) AS avg_revenue FROM orders

3. In FROM, a subquery is a whole table. Give it a name with AS and use it like any table.

   SELECT * FROM (SELECT customer_id, SUM(revenue) AS total FROM orders GROUP BY customer_id) AS totals WHERE total > 100

4. In HAVING, a subquery gives one number to compare each group's total with.

   SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(revenue) > (SELECT AVG(revenue) FROM orders)

Practice

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

SELECT * FROM orders WHERE revenue > (SELECT AVG(revenue) FROM orders) AND revenue < (SELECT MAX(revenue) FROM orders)

SELECT * FROM orders WHERE revenue > (SELECT MIN(revenue) FROM orders) AND revenue < (SELECT AVG(revenue) FROM orders)

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products) AND price < (SELECT MAX(price) FROM products)

SELECT full_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) AND salary < (SELECT MAX(salary) FROM employees)