SQL · Chapter 26 of 42

SQL Subqueries

A SUBQUERY is a SELECT nested inside another statement. Used in WHERE (with IN/EXISTS), FROM (as a derived table), and SELECT.

CTEs (`WITH ... AS`) are the modern, more readable alternative for complex cases.

Scalar subquery

Returns a single value: `SELECT (SELECT MAX(price) FROM products) AS max_price;`

In WHERE

`WHERE user_id IN (SELECT id FROM active_users)`.

Example 1 (sql)

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

Output

products above average price

Subquery in WHERE.

Example 2 (sql)

WITH top_customers AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders GROUP BY user_id
  ORDER BY spent DESC LIMIT 10
)
SELECT u.name, tc.spent
FROM top_customers tc
JOIN users u ON u.id = tc.user_id;

Output

top 10 spenders with names

CTE version — much more readable.

Key points

Subquery = SELECT inside another query.
Can appear in SELECT, FROM or WHERE.
CTEs (`WITH`) are cleaner for complex logic.
Watch performance — some subqueries can be slow.

💡 Note: Correlated subqueries reference the outer query and run once per outer row — often much slower than a JOIN.

SQL Subqueries

Scalar subquery

In WHERE

Key points

📝 Quick Quiz