Subqueries: Scalar, IN, and Derived Tables

Use nested SELECT statements for complex filtering, computation, and data transformation.

Overview

Subqueries (nested queries) allow you to use the result of one SELECT inside another. There are three main placements: in WHERE (scalar comparisons, IN lists), in FROM (derived tables treated like a temporary table), and in SELECT (scalar subqueries that return a single value). Subqueries are powerful but can be slow if the inner query runs once per outer row (correlated subqueries). For performance-critical code, prefer JOINs or CTEs. Use subqueries when they make the intent clearer or when a JOIN would be awkward.

Key Points

•Scalar subquery in WHERE: WHERE col = (SELECT MAX(col) FROM t)
•IN subquery: WHERE col IN (SELECT id FROM other)
•Derived table in FROM: FROM (SELECT ...) AS alias
•Scalar in SELECT: SELECT (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS cnt FROM customers c
•Correlated subquery: references the outer query runs once per outer row

Examples

Scalar and IN subqueries

-- Find products with the max price
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

-- Products ordered by VIP customers
SELECT * FROM products WHERE id IN (
  SELECT product_id FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  JOIN customers c ON c.id = o.customer_id
  WHERE c.tier = 'vip'
);

Derived table for two-level aggregation

SELECT customer_id, total_spent
FROM (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
) totals
WHERE total_spent > 500;

Subqueries: Scalar, IN, and Derived Tables

Overview

Key Points

Examples

Exercises

Knowledge Check

A scalar subquery that returns 2 rows causes what?