Subqueries

A Query Inside a Query

A subquery is a SELECT nested inside another statement. It computes a value, a list or a table that the outer query uses.

Subquery Returning One Value

SELECT name, city
FROM customers
WHERE id = (SELECT MAX(id) FROM customers);

Subquery Returning a List (IN)

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Subquery in the FROM Clause

SELECT city, AVG(members) FROM (
  SELECT city, COUNT(*) AS members FROM customers GROUP BY city
) AS city_stats
GROUP BY city;

EXISTS Is Often Faster

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS stops at the first match, which frequently beats IN on large tables. The literal SELECT 1 is conventional; the value itself is ignored.

Correlated Subqueries

SELECT name,
       (SELECT MAX(total) FROM orders o WHERE o.customer_id = c.id) AS best
FROM customers c;

A correlated subquery re-runs for every outer row, referencing the outer alias (c).

Key Points

• Subqueries appear in SELECT, WHERE, FROM and HAVING.
• IN uses a list; EXISTS short-circuits for speed.
• Correlated subqueries reference the outer query.
• Subqueries in FROM are handy inline "virtual tables".