Recursive query
A SQL query that calls itself to traverse hierarchies and graphs — implemented with WITH RECURSIVE. The standard way to fetch whole subtrees or ancestor chains from an adjacency-list table (the parent_id model).
Mechanic: a recursive CTE has two parts — a base case (the starting rows, e.g. a certain node) and a recursive step that joins back against the CTE itself to fetch the next level (e.g. the children). The database iterates until the step yields no more rows. Use case: "fetch all subcategories", "the whole comment thread", "the shortest path in a graph", "all managers above an employee". Supported by PostgreSQL, SQL Server, Oracle, MySQL 8+, SQLite. Pitfalls: without a condition guaranteeing termination, a cyclic graph can cause an infinite loop (some databases have CYCLE protection); deep trees can become slow. It's the "glue" that makes the adjacency list practical. Related to adjacency list and CTE.