SQL Subqueries Explained

A subquery is a query nested inside another query — SQL runs the inner query first, then uses its result as part of the outer query's logic. They let you answer questions that depend on a value you don't know ahead of time, like "who earns more than the average salary," without hardcoding that average yourself.

Subqueries can appear in a WHERE clause to filter rows, or in a FROM clause to act as a temporary, on-the-fly table.

A Scalar Subquery in WHERE

A "scalar" subquery returns exactly one value, which makes it safe to compare directly with =, >, or <. Here's everyone earning above the company-wide average:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Result:

name | salary
--------------|-------
Carla Gomez | 95000
David Kim | 95000
Elena Petrov | 88000
Henry Adams | 71000

The inner query runs once, computes 73000, and the outer query then filters against that number as if it had been typed in directly.

A Multi-Row Subquery With IN

When a subquery can return several rows, pair it with IN instead of a direct comparison operator:

SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 2
);

Result:

name | department
--------------|------------
Carla Gomez | Engineering
David Kim | Engineering
Elena Petrov | Engineering

This finds every employee who works in a department with more than two people — the inner query figures out which departments qualify, and the outer query pulls the matching employees.

A Subquery in FROM

A subquery can also stand in for a table, letting you query a result set instead of raw data. This is handy when you need to filter or sort after aggregating:

SELECT department, total_salary
FROM (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
) AS department_totals
WHERE total_salary > 150000;

Every subquery used this way needs an alias (AS department_totals here) — SQL requires a name for any derived table. When a query like this starts feeling hard to read, a Common Table Expression (CTE) is usually a cleaner way to write the same logic.

---