Introduction to Subqueries

Use a SELECT inside another query to compute values or filter rows.

Overview

A subquery (also called a nested query or inner query) is a SELECT statement inside another SQL statement. Subqueries can appear in WHERE (to filter based on computed values), in FROM (as a derived table), and in SELECT (as scalar subqueries that return a single value). Subqueries are powerful but can be slow if not written carefully a correlated subquery runs once per outer row. For most use cases, JOINs or CTEs are more readable and often faster. But scalar subqueries and IN subqueries are common and perfectly fine for most workloads.

Key Points

•Scalar subquery: returns one row, one column usable anywhere an expression is
•IN subquery: col IN (SELECT col FROM ...) filter with a list from another query
•Derived table: FROM (SELECT ...) AS alias use query result as a table
•EXISTS subquery: checks if rows are returned
•Correlated subquery: references outer query columns runs once per outer row

Examples

Scalar and IN subqueries

-- Scalar: find product with max price
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- IN subquery: orders from customers in UK
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Derived table (subquery in FROM)

-- Group orders by customer, then filter high-value customers
SELECT customer_id, total_spent
FROM (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 1000;

Introduction to Subqueries

Overview

Key Points

Examples

Exercises

Knowledge Check

What is a scalar subquery?