Subqueries & CTEs
Subqueries & CTEs — Learn how to nest queries with subqueries, correlated subqueries, and Common Table Expressions (CTEs). The guide walks through Scalar Subqueries, Correlated Subqueries, IN, ANY, ALL with Subqueries, Common Table Expressions (CTE), Recursive CTEs. A scalar subquery returns a single value (one row, one column). It can be used in SELECT, WHERE, or HAVING clauses. Example: SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees. The subquery executes once for the outer query. Correlated subqueries reference columns from the outer query and execute for each row. They are powerful but can be slow for large datasets. Example: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id). EXISTS and NOT EXISTS use correlated subqueries. The guide is organized into 5 sections that build on each other, each pairing a prose explanation with real SQL you can run as-is. Related guides: SQL Introduction, SELECT Statement, WHERE Clause & Operators, SQL JOINs, GROUP BY & Aggregation.