SQL Tutorial · Joins and Subqueries · Lesson 24 of 36

SQL Subqueries

Use a query inside another query — in WHERE, FROM and SELECT.

Subquery in WHERE

A subquery is a SELECT inside another statement:

Example 1Try it yourself

-- Products more expensive than average
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price;
-- Customers who have at least one pending order
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'pending');

Output

name | price
Mechanical Keyboard | 89
Robot Kit | 120
4K Monitor | 329

name
Ada Lovelace
Alan Turing

Correlated subqueries

A correlated subquery refers to the outer row, so it runs once per row:

Example 2Try it yourself

-- Each employee earning more than their department's average
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department)
ORDER BY e.department;

Output

name | department | salary
Omar Haddad | Engineering | 210000
Mei Chen | Engineering | 165000
Hannah Okafor | Sales | 150000

Subquery in FROM

A subquery in FROM (a derived table) acts like a temporary table:

Example 3Try it yourself

SELECT customer, ROUND(total, 2) AS total
FROM (
  SELECT c.name AS customer, SUM(oi.quantity * oi.unit_price) AS total
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY c.customer_id
) AS spend
WHERE total > 100
ORDER BY total DESC;

Output

customer | total
Ada Lovelace | 749.49
Grace Hopper | 523.89
Linus Torvalds | 216.98
Alan Turing | 153.94

Test yourself

What is a correlated subquery?

A subquery in FROM
A subquery that refers to columns of the outer query
Two subqueries joined together
A subquery with GROUP BY

Show answer

Answer: A subquery that refers to columns of the outer query