Subqueries
Nested SELECT — scalar, IN, EXISTS, and derived tables
Skills taught: sql.subqueries, sql.scalar-subquery, sql.correlated-subquery, sql.exists
Scalar subqueries and IN
-- Scalar subquery: returns one value
-- Products more expensive than the average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Scalar subquery in SELECT column
SELECT
name,
price,
ROUND((SELECT AVG(price) FROM products), 2) AS avg_price,
price - ROUND((SELECT AVG(price) FROM products), 2) AS diff_from_avg
FROM products;
-- IN subquery: users who have placed at least one order
SELECT name, city
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- NOT IN: users who have NEVER ordered
SELECT name, city
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
EXISTS and derived tables
-- EXISTS: efficient, stops on first match
SELECT name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id AND o.total > 1000
);
-- 'Give me users who have at least one order over 1000'
-- NOT EXISTS (preferred over NOT IN)
SELECT name
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Derived table: subquery in FROM
-- Find users whose average order exceeds 500
SELECT u.name, avg_orders.avg_total
FROM users u
JOIN (
SELECT user_id, ROUND(AVG(total), 2) AS avg_total
FROM orders
GROUP BY user_id
) AS avg_orders ON u.id = avg_orders.user_id
WHERE avg_orders.avg_total > 500;
Frequently Asked Questions
When should I use a subquery vs a JOIN?
JOINs are usually faster and more readable for combining rows from related tables. Use subqueries when you need to compute something first (scalar subquery for averages/counts), use IN/EXISTS for existence checks, or build derived tables for intermediate aggregations. When a subquery gets complex, convert it to a CTE (WITH clause) for readability.