Recursive CTE

Advanced

A CTE that references itself, enabling traversal of hierarchical/tree data.

Syntax

WITH RECURSIVE name AS (base UNION ALL recursive) SELECT ...

Example

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

About SQL Recursive CTE

The Recursive CTE keyword belongs to the Advanced category of SQL statements. A CTE that references itself, enabling traversal of hierarchical/tree data. Understanding this command is essential for any developer working with relational databases like PostgreSQL, MySQL, SQLite, or SQL Server.

SQL (Structured Query Language) is the standard language for managing and querying relational databases. The Recursive CTE statement is supported across all major database systems, though specific syntax may vary slightly between PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and SQLite. Always consult your database's documentation for vendor-specific features and limitations.

Best practices for using Recursive CTE: always test queries on a development database before running them in production, use parameterized queries to prevent SQL injection, and leverage EXPLAIN to understand query performance. For complex queries, consider using CTEs (Common Table Expressions) to improve readability and maintainability.

Related SQL Keywords

WITH (CTE)UNION ALLSelf JoinSubquery