Subqueries

SELECT column, (SELECT single_value FROM ...) AS alias
FROM table;

SELECT
  product_name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- product_name | price  | diff_from_avg
-- Laptop       | 999.00 | 849.50
-- Cable         | 9.99   | -139.51

Note A scalar subquery must return exactly one row and one column. If it returns more than one row, the query fails. If it returns zero rows, the result is NULL.

WHERE column IN (SELECT column FROM ...)

SELECT first_name, email
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE total_amount > 500
);

-- Users who have placed at least one order over 500

Note For large datasets, EXISTS often performs better than IN with a subquery because EXISTS short-circuits on the first match. IN materializes the entire subquery result first.

SELECT columns
FROM table1 t1
WHERE column op (
  SELECT aggregate(col) FROM table2 t2
  WHERE t2.ref = t1.id
);

SELECT product_name, price, category_id
FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category_id = p.category_id
);

-- Products priced above their own category's average

Note A correlated subquery references the outer query and executes once per outer row. This can be slow on large tables. Consider rewriting as a JOIN with a derived table for better performance.

SELECT columns
FROM (SELECT ... FROM ...) AS alias;

SELECT
  top_customers.first_name,
  top_customers.total_spent
FROM (
  SELECT u.first_name, SUM(o.total_amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.first_name
  HAVING SUM(o.total_amount) > 1000
) AS top_customers
ORDER BY top_customers.total_spent DESC;

-- first_name | total_spent
-- Alice      | 4520.00
-- Bob        | 2310.50

Note Derived tables (subqueries in FROM) must have an alias. They are computed once and treated like a temporary table. CTEs (WITH clause) are generally more readable for the same purpose.

WHERE EXISTS (SELECT 1 FROM table WHERE condition)

SELECT c.category_name
FROM categories c
WHERE NOT EXISTS (
  SELECT 1 FROM products p
  WHERE p.category_id = c.id
);

-- Categories that have no products at all

Note NOT EXISTS is the safest way to find missing related rows. Unlike NOT IN, it handles NULLs correctly and will not produce unexpected empty results.

WHERE column > ANY (subquery)
WHERE column > ALL (subquery)

SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 3
);

-- Employees earning more than everyone in department 3

Note ANY means 'at least one' - the condition must hold for at least one row from the subquery. ALL means 'every' - it must hold for all rows. An empty subquery makes ALL true and ANY false.

SELECT columns
FROM table1 t1
CROSS JOIN LATERAL (
  SELECT ... FROM table2 WHERE ref = t1.id LIMIT n
) AS sub;

SELECT u.first_name, recent.order_id, recent.total_amount
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, total_amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) AS recent;

-- Each user paired with their 3 most recent orders

Note LATERAL lets a subquery reference columns from preceding tables in the FROM clause. It is the SQL equivalent of a for-each loop. PostgreSQL supports LATERAL; MySQL 8+ supports LATERAL derived tables.

INSERT INTO target (columns)
SELECT columns FROM source WHERE condition;

INSERT INTO archived_orders (order_id, user_id, total_amount, order_date)
SELECT order_id, user_id, total_amount, order_date
FROM orders
WHERE order_date < '2024-01-01';

-- Copies old orders into an archive table

Note The SELECT column count and types must match the INSERT column list. This is an efficient way to bulk-copy data between tables without round-tripping through the application.