Subqueries

Advanced

⏱️ 17 mins read

What You'll Learn

A subquery is a query nested inside another. Non-correlated subqueries run once independently. Correlated subqueries reference the outer query and run once per outer row — they can be slow on large tables. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses.

Syntax

-- WHERE subquery
SELECT * FROM table
WHERE col > (SELECT AVG(col) FROM table);

-- FROM subquery (derived table)
SELECT * FROM (SELECT ...) AS alias;

Example

-- Non-correlated: runs once
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM
SELECT dept, avg_sal
FROM (
  SELECT department AS dept,
         AVG(salary) AS avg_sal
  FROM employees GROUP BY department
) dept_stats
WHERE avg_sal > 75000;

-- Correlated: runs per outer row (SLOW)
SELECT e.name, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(salary) FROM employees
  WHERE department = e.department -- references outer e
);

Common Mistakes

Correlated subqueries run N times (once per row in the outer query). On a 1M-row table, that's 1M subquery executions. Prefer JOINs or CTEs for performance. Also: NOT IN with a subquery that returns NULLs always produces empty results — use NOT EXISTS instead.

Interview Tips

Know when to use EXISTS vs IN. EXISTS stops at the first match (faster for large sets). Correlated subqueries are often rewritable as JOINs — show both approaches and discuss trade-offs.

Practice

Find the top earner in each department using a correlated subquery. Then rewrite the same query using a CTE with ROW_NUMBER().

Back to Full Roadmap