This guide is specifically for PostgreSQL syntax.

SQL Common Table Expressions (CTEs) Reference Guide

Reference for WITH clause and recursive CTEs.

Basic CTE

Syntax:

WITH cte_name AS (SELECT ...) SELECT * FROM cte_name

Defines a named temporary result set for the main query.

Example:

WITH high_salary AS (SELECT * FROM employees WHERE salary > 100000) SELECT * FROM high_salary;

💡 Note: Improves readability of complex queries

Multiple CTEs

Syntax:

WITH cte1 AS (...), cte2 AS (...) SELECT ...

Define multiple CTEs separated by commas.

Example:

WITH sales AS (SELECT ...), costs AS (SELECT ...) SELECT s.*, c.* FROM sales s JOIN costs c ON ...;

💡 Note: Later CTEs can reference earlier ones

Recursive CTE

Syntax:

WITH RECURSIVE cte AS (base_case UNION ALL recursive_case) SELECT ...

CTE that references itself. Useful for hierarchies.

Example:

WITH RECURSIVE org AS (
  SELECT id, name, 1 as level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, o.level + 1 FROM employees e JOIN org o ON e.manager_id = o.id
) SELECT * FROM org;

💡 Note: RECURSIVE keyword required in PostgreSQL

CTE vs Subquery

Syntax:

WITH cte AS (...) SELECT * FROM cte JOIN cte ...

CTEs can be referenced multiple times; subqueries are evaluated each time.

Example:

WITH stats AS (SELECT dept, AVG(salary) as avg FROM employees GROUP BY dept) SELECT * FROM stats s1 JOIN stats s2 ON ...;

💡 Note: CTEs may be materialized (database dependent)

Quick Reference Table

| Function | Purpose | |----------|---------| | Basic CTE | Defines a named temporary result set for the main query | | Multiple CTEs | Define multiple CTEs separated by commas | | Recursive CTE | CTE that references itself | | CTE vs Subquery | CTEs can be referenced multiple times; subqueries are evaluated each time |

Related Cheat Sheets

Continue learning with more SQL references:

PostgreSQL-Specific Notes

This page covers PostgreSQL syntax. Other databases may have different syntax for similar operations.