Subqueries

Overview

A subquery is a SELECT statement nested inside another statement. It can appear in the WHERE, FROM, or SELECT clause and lets you use the result of one query as input to another. Subqueries are either scalar (return one value), row/column lists (used with IN), or correlated (reference the outer query).

Syntax / Usage

A subquery is wrapped in parentheses. When used with IN it returns a column of values; with = it must return a single value; a correlated subquery references columns from the outer query and runs per outer row.

-- Scalar subquery in WHERE
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery with IN
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Correlated subquery
SELECT u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Examples

Each customer's most recent order date using a correlated subquery:

SELECT u.name,
       (SELECT MAX(o.created_at)
        FROM orders o
        WHERE o.user_id = u.id) AS last_order
FROM users u;

Filter using a derived table (subquery in FROM):

SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE avg_salary > 50000;

Common Mistakes

• Using = when the subquery can return more than one row (use IN instead)
• Forgetting that a correlated subquery runs once per outer row, which can be slow
• Using IN with a subquery that may contain NULL, which breaks NOT IN logic
• Writing a correlated EXISTS when a JOIN would be clearer and faster
• Not aliasing a derived table in the FROM clause (required by most databases)

See Also

select where-clause sql-cte