🎓 A1Learner
🔍

SQL Subqueries

A subquery is a query written inside another query. The inner query runs first, and the outer query uses its result.

The inner query on its own

This query returns the average product price. The next snippet places this same query inside a larger one.

SELECT AVG(price) AS average_price FROM products;

Priced above the average

The subquery in parentheses returns the average price. The outer WHERE test compares each price against that one value.

SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

A list from a subquery

The subquery returns the ids of customers who placed an order. IN keeps the customers whose id sits in that list.

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders)
ORDER BY name;

Example

SELECT name, city FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE product_id = (SELECT product_id FROM products WHERE name = 'Headphones'))
ORDER BY name;

Key points

  • Subquery: a query inside parentheses within another query
  • The inner query runs before the outer query reads its result.
  • A subquery that returns one value can stand where one value is needed.
  • Use IN with a subquery to test a column against the rows the subquery returns.
  • A subquery lets one query work with the answer from another query.

In this example, the deepest subquery finds the product id for Headphones. The middle subquery uses that id to list the customers who ordered it. The outer query then returns the names and cities for those customers, Ben Carter and Eli Ross.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

Where does a subquery sit in a query?