CTEs — Common Table Expressions (WITH Clause) — Free SQL Tutorial

TL;DR: Learn CTEs — Common Table Expressions (WITH Clause) in SQL with a free, beginner-friendly tutorial, examples and practice for Indian students on Sylla…

CTEs — Common Table Expressions (WITH Clause) in SQL

A CTE (Common Table Expression) is a named temporary result set defined with the WITH clause. It makes complex queries readable by breaking them into named, reusable steps — like variables in SQL.

Without CTEs, complex queries use deeply nested subqueries that are hard to read and debug. CTEs let you define each logical step separately with a meaningful name, then reference it like a table.

Multiple CTEs: You can chain several CTEs separated by commas, where each can reference previous ones. This creates a readable step-by-step analytics pipeline.

Recursive CTEs reference themselves, enabling traversal of hierarchical data like org charts, category trees, and bill of materials.

WITH cte_name AS (
    SELECT ...       -- CTE definition
),
another_cte AS (
    SELECT ... FROM cte_name  -- can reference earlier CTEs
)
SELECT * FROM another_cte;   -- main query

-- Recursive CTE:
WITH RECURSIVE hierarchy AS (
    SELECT id, name, parent_id FROM org WHERE parent_id IS NULL  -- anchor
    UNION ALL
    SELECT e.id, e.name, e.parent_id FROM org e
    JOIN hierarchy h ON e.parent_id = h.id   -- recursive part
)
SELECT * FROM hierarchy;

Learn CTEs — Common Table Expressions (WITH Clause) step by step with Syllab's free interactive SQL tutorial — runnable code examples, practice exercises and instant AI feedback, all free with no signup. Explore the full SQL course →