This article explains MySQL recursive queries using WITH RECURSIVE, detailing their structure, execution process, common use cases, and provides a step‑by‑step example with table creation, data insertion, and a paginated hierarchical query.
This article explains how to replace parent‑id based tree storage with the nested set model, showing SQL queries to retrieve descendants, count them, detect leaf nodes, and perform insert, delete, and other operations, plus a JavaScript example for rebuilding the hierarchy.
This article explains how to use the nested set model to efficiently query, count, and manage hierarchical department data in MySQL, covering recursive retrieval, leaf detection, insertion, deletion, and traversal techniques with practical SQL examples and JavaScript code for building tree structures.
This article explains how Common Table Expressions (CTEs) simplify hierarchical queries in SQL, covering both non‑recursive and recursive forms, providing step‑by‑step examples for retrieving child, parent, and root categories using MySQL 8 and PostgreSQL 10, along with test data and execution results.
This article compares four common database designs for hierarchical data—adjacency list, path enumeration, nested set, and closure table—explaining their structures, query patterns, advantages, drawbacks, and how to decide which model best fits specific application requirements.
