Subqueries & CTEs

A query inside a query

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

The inner query runs first and returns one value (the max salary). The outer query uses it.

Subqueries can also return a list:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE quantity >= 2);

CTEs: name your steps

A common table expression (WITH) lets you build a complex query step by step, like variables in code:

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.name, e.salary, ROUND(d.avg_salary) AS dept_average
FROM employees e
JOIN dept_avg d ON d.department_id = e.department_id
WHERE e.salary > d.avg_salary;

That finds people paid above their own department's average. Try writing it without the CTE and you'll see why professionals love them.

🎯 You can now answer most SQL interview questions: filtering, grouping, joining, ranking and subqueries. Practice them in Interview Prep → SQL.