Consultas jerárquicas y recursivas en SQL

Una consulta jerárquica es un tipo de consulta SQL que maneja datos de modelos jerárquicos . Son útiles para trabajar con bases de datos de datos estructurados en grafos , como redes fluviales, árboles de sistemas de archivos o comentarios encadenados. Son casos especiales de consultas recursivas de punto fijo más generales , que calculan cierres transitivos .

En SQL estándar:1999, las consultas jerárquicas se implementan mediante expresiones de tabla comunes recursivas (CTE). A diferencia de la cláusula connect-by anterior de Oracle , las CTE recursivas se diseñaron con semántica de punto fijo desde el principio. ^{[ 1 ]} Las CTE recursivas del estándar eran relativamente cercanas a la implementación existente en IBM DB2 versión 2. ^{[ 1 ]} Las CTE recursivas también son compatibles con Microsoft SQL Server (desde SQL Server 2008 R2), ^{[ 2 ]} Firebird 2.1 , ^{[ 3 ]} PostgreSQL 8.4+ , ^{[ 4 ]} SQLite 3.8.3+ , ^{[ 5 ]} IBM Informix versión 11.50+, CUBRID , MariaDB 10.2+ y MySQL 8.0.1+ . ^{[ 6 ]} Tableau tiene documentación que describe cómo se pueden usar las CTE. TIBCO Spotfire no admite CTEs, mientras que la implementación de Oracle 11g Release 2 carece de semántica de punto fijo.

Sin expresiones de tabla comunes ni cláusulas connected-by es posible lograr consultas jerárquicas con funciones recursivas definidas por el usuario. ^{[ 7 ]}

Una expresión de tabla común, o CTE, (en SQL ) es un conjunto de resultados temporales con nombre, derivado de una consulta simple y definido dentro del ámbito de ejecución de una instrucción SELECT, INSERT, UPDATE, o DELETE.

Las CTE pueden considerarse alternativas a las tablas derivadas ( subconsultas ), las vistas y las funciones definidas por el usuario en línea.

Las expresiones de tabla comunes son compatibles con Teradata (a partir de la versión 14), IBM Db2 , Informix (a partir de la versión 14.1), Firebird (a partir de la versión 2.1), ^{[ 8 ]} Microsoft SQL Server (a partir de la versión 2005), Oracle (con recursión desde la versión 11g release 2), PostgreSQL (desde la versión 8.4), MariaDB (desde la versión 10.2 ^{[ 9 ]} ), MySQL (desde la versión 8.0) , SQLite (desde la versión 3.8.3), HyperSQL , Informix (desde la versión 14.10), ^{[ 10 ]} Google BigQuery , Sybase (a partir de la versión 9), Vertica , H2 (experimental), ^{[ 11 ]} y muchos otros . Oracle denomina a las CTEs "factorización de subconsultas". ^{[ 12 ]}

La sintaxis para una CTE (que puede ser recursiva o no) es la siguiente:

CON [ RECURSIVO ] con_consulta [, ...] SELECCIONAR ...

donde with_queryla sintaxis de 's es:

nombre_consulta [ ( nombre_columna [,...]) ] AS ( SELECT ...)

Las CTE recursivas pueden utilizarse para recorrer relaciones (como grafos o árboles), aunque la sintaxis es mucho más compleja, ya que no se crean pseudocolumnas automáticamente (como se muestra LEVELa continuación ); si se desean, deben crearse en el código. Consulte la documentación de MSDN ^{[ 2 ]} o la documentación de IBM ^{[ 13 ] [ 14 ]} para ver ejemplos.

La RECURSIVEpalabra clave no suele ser necesaria después de WITH en sistemas distintos de PostgreSQL. ^{[ 15 ]}

En SQL:1999, una consulta recursiva (CTE) puede aparecer en cualquier lugar donde se permita una consulta. Es posible, por ejemplo, nombrar el resultado usando CREATE[ RECURSIVE] VIEW. ^{[ 16 ]} Usando una CTE dentro de una INSERT INTO, se puede llenar una tabla con datos generados a partir de una consulta recursiva; la generación aleatoria de datos es posible usando esta técnica sin usar ninguna instrucción procedimental. ^{[ 17 ]}

Algunas bases de datos, como PostgreSQL, admiten un formato CREATE RECURSIVE VIEW más corto que se traduce internamente en codificación WITH RECURSIVE. ^{[ 18 ]}

Un ejemplo de consulta recursiva que calcula el factorial de números del 0 al 9 es el siguiente:

WITH recursive temp ( n , fact ) AS ( SELECT 0 , 1 -- Subconsulta inicial UNION ALL SELECT n + 1 , ( n + 1 ) * fact FROM temp WHERE n < 9 -- Subconsulta recursiva ) SELECT * FROM temp ;

Una sintaxis alternativa es la construcción no estándar CONNECT BY, introducida por Oracle en la década de 1980. ^{[ 19 ]} Antes de Oracle 10g, la construcción solo era útil para recorrer grafos acíclicos porque devolvía un error al detectar cualquier ciclo; en la versión 10g, Oracle introdujo la característica (y palabra clave) NOCYCLE, lo que permite que el recorrido funcione también en presencia de ciclos. ^{[ 20 ]}

CONNECT BYEs compatible con Snowflake , EnterpriseDB , ^{[ 21 ]} Oracle Database , ^{[ 22 ]} CUBRID , ^{[ 23 ]} IBM Informix ^{[ 24 ]} e IBM Db2 , aunque solo si está habilitado como modo de compatibilidad. ^{[ 25 ]} La sintaxis es la siguiente:

SELECT select_list FROM table_expression [ WHERE ... ] [ START WITH start_expression ] CONNECT BY [ NOCYCLE ] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ] [ GROUP BY ... ] [ HAVING ... ] ...

Por ejemplo,

SELECCIONAR NIVEL , LPAD ( ' ' , 2 * ( NIVEL - 1 )) || ename "empleado" , empno , mgr "gerente" FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ;

El resultado de la consulta anterior se vería así:

nivel | empleado | empno | gerente -------+-------------+-------+--------- 1 | REY | 7839 | 2 | JONES | 7566 | 7839 3 | SCOTT | 7788 | 7566 4 | ADAMS | 7876 | 7788 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | DISTRITO | 7521 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 (14 filas)

SELECT ename "Employee" , CONNECT_BY_ROOT ename "Manager" , LEVEL - 1 "Pathlen" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Path" FROM emp WHERE LEVEL > 1 AND deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Employee" , "Manager" , "Pathlen" , "Path" ;

• Datalog también implementa consultas de punto fijo.
• Las consultas de ruta regulares son un tipo específico de consulta recursiva en las bases de datos de grafos.
• bases de datos deductivas
• Modelo jerárquico
• Unión recursiva
• Accesibilidad
• Cierre transitivo
• Estructura del árbol

• CJ Date (2011). SQL y teoría relacional: Cómo escribir código SQL preciso (2.ª  ed.). O'Reilly Media. págs. 159–163 . ISBN  978-1-4493-1640-2.

Libros de texto académicos . Tenga en cuenta que estos solo cubren el estándar SQL:1999 (y Datalog), pero no la extensión de Oracle.

• Abraham Silberschatz; Henry Korth; S. Sudarshan (2010). Conceptos de sistemas de bases de datos (6.ª  ed.). McGraw-Hill. págs. 187–192 . ISBN  978-0-07-352332-3.
• Raghu Ramakrishnan; Johannes Gehrke (2003). Sistemas de gestión de bases de datos (3ª  ed.). McGraw-Hill. ISBN 978-0-07-246563-1.Capítulo 24.
• Hector Garcia-Molina; Jeffrey D. Ullman; Jennifer Widom (2009). Sistemas de bases de datos: el libro completo (2.ª  ed.). Pearson Prentice Hall. pp. 437–445 . ISBN  978-0-13-187325-4.

• "sql - Detección de ciclos con factorización de subconsultas recursivas - Stack Overflow" . stackoverflow.com . Consultado el 4 de febrero de 2026 .
• "SQL Server: ¿son realmente las CTE recursivas basadas en conjuntos? en EXPLAIN EXTENDED" . explainextended.com . Consultado el 4 de febrero de 2026 .
• "Comprendiendo la cláusula WITH | Jonathan Gennick" . Archivado del original el 14 de noviembre de 2013. Consultado el 4 de febrero de 2026 .
• "SQL: Recursión" (PDF) . Archivado del original (PDF) el 17 de enero de 2005.
• "BlackTDN  :: MSSQL usando consulta CTE recursiva para montaje de árbol" . www.blacktdn.com.br . Consultado el 4 de febrero de 2026 .