Topic 36 of 52
Subqueries
Overview
Subqueries are SELECT statements nested inside another query. They can appear in SELECT, FROM, WHERE, and HAVING clauses — enabling complex logic like finding rows relative to aggregate values or filtering based on existence.
Syntax
sql
-- Subquery in WHERE (scalar subquery)
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Subquery in FROM (derived table / inline view)
SELECT dept, avg_sal FROM (
SELECT department AS dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
) dept_stats
WHERE avg_sal > 70000;
-- EXISTS / NOT EXISTS (correlated subquery)
SELECT u.name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.status = 'completed'
);
-- Correlated subquery (references outer query)
SELECT name, salary,
(SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept) AS dept_avg
FROM employees e1;
-- Subquery in SELECT (scalar — one value per row)
SELECT name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;Common Pitfalls
- Correlated subqueries run once per outer row — O(n) complexity. For large tables, rewrite as a JOIN or CTE.
- Scalar subqueries (in SELECT) that return more than one row throw a runtime error — ensure your subquery is guaranteed to return at most one value.
- Interview tip: EXISTS is usually faster than IN with subqueries because it stops at the first match, while IN collects all values first.
Real-World Example
Finding top performers and above-average products:
example
sql
-- Products with price above category average
SELECT p.name, p.price, cat.name AS category,
cat_avg.avg_price AS category_avg
FROM products p
JOIN categories cat ON p.category_id = cat.id
JOIN (
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
) cat_avg ON p.category_id = cat_avg.category_id
WHERE p.price > cat_avg.avg_price
ORDER BY cat.name, p.price DESC;
-- Users who placed orders in ALL 12 months of 2025
SELECT user_id FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY user_id
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM created_at)) = 12;