Una consulta jerárquica es un tipo de consulta SQL que maneja datos de modelos jerárquicos . Son casos especiales de consultas de punto fijo recursivas más generales , que calculan cierres transitivos .
En el estándar SQL: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 utilizar las CTE. TIBCO Spotfire no admite las CTE, 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 conectadas 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 con nombre temporal, derivado de una consulta simple y definido dentro del ámbito de ejecución de una declaración SELECT, INSERT, UPDATEo .DELETE
Las CTE pueden considerarse alternativas a las tablas derivadas ( subconsultas ), vistas y 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 2), PostgreSQL (desde 8.4), MariaDB ( desde 10.2), MySQL (desde 8.0), SQLite (desde 3.8.3), HyperSQL , Informix (desde 14.10), [9] Google BigQuery , Sybase (a partir de la versión 9), Vertica , H2 (experimental), [10] y muchos otros . Oracle llama a las CTE "factorización de subconsultas". [11]
La sintaxis de una CTE (que puede ser recursiva o no) es la siguiente:
CON [ RECURSIVO ] con_consulta [, ...] SELECCIONAR ... donde with_queryla sintaxis es:
nombre_consulta [ ( nombre_columna [,...]) ] AS ( SELECT ...) Se pueden utilizar CTE recursivas para recorrer relaciones (como gráficos o árboles), aunque la sintaxis es mucho más compleja porque no se crean pseudocolumnas automáticas (como se muestra LEVELa continuación); si se desean, se deben crear en el código. Consulte la documentación de MSDN [2] o la documentación de IBM [12] [13] para ver ejemplos de tutoriales.
La RECURSIVEpalabra clave normalmente no es necesaria después de WITH en sistemas distintos de PostgreSQL. [14]
En SQL:1999, una consulta recursiva (CTE) puede aparecer en cualquier lugar donde se permita una consulta. Es posible, por ejemplo, nombrar el resultado utilizando CREATE[ RECURSIVE] VIEW. [15] Al utilizar 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 utilizando esta técnica sin utilizar ninguna instrucción de procedimiento. [16]
Algunas bases de datos, como PostgreSQL, admiten un formato CREATE RECURSIVE VIEW más corto que se traduce internamente a codificación WITH RECURSIVE. [17]
Un ejemplo de una consulta recursiva que calcula el factorial de números del 0 al 9 es el siguiente:
CON recursiva temp ( n , fact ) AS ( SELECT 0 , 1 - Subconsulta inicial UNION ALL SELECT n + 1 , ( n + 1 ) * fact FROM temp DONDE n < 9 - Subconsulta recursiva ) SELECT * FROM temp ; Una sintaxis alternativa es la construcción no estándar CONNECT BY; fue introducida por Oracle en la década de 1980. [18] Antes de Oracle 10g, la construcción solo era útil para recorrer gráficos acíclicos porque devolvía un error al detectar cualquier ciclo; en la versión 10g Oracle introdujo la característica NOCYCLE (y la palabra clave), lo que hizo que el recorrido funcionara también en presencia de ciclos. [19]
CONNECT BYes compatible con Snowflake , EnterpriseDB , [20] Oracle database , [21] CUBRID , [22] IBM Informix [23] e IBM Db2 aunque solo si está habilitado como modo de compatibilidad. [24] La sintaxis es la siguiente:
SELECCIONAR lista_de_selección DE expresión_de_tabla [ DONDE ... ] [ COMENZAR CON expresión_de_inicio ] CONECTAR POR [ SIN CICLO ] { ANTERIOR expr_hijo = expr_padre | expr_padre = ANTERIOR expr_hijo } [ ORDENAR HERMANOS POR columna1 [ ASC | DESC ] [, columna2 [ ASC | DESC ] ] ... ] [ AGRUPAR POR ... ] [ TENIENDO ... ] ... SELECCIONAR NIVEL , LPAD ( ' ' , 2 * ( NIVEL - 1 )) || ename "empleado" , empno , mgr "gerente" DE emp INICIO CON mgr ES NULO CONECTAR POR ANTERIOR empno = mgr ; El resultado de la consulta anterior se vería así:
nivel | empleado | empleado | 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 | BARRIO | 7521 | 7698 3 | MARTÍN | 7654 | 7698 3 | TORNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782(14 filas)
El siguiente ejemplo devuelve el apellido de cada empleado del departamento 10, cada gerente por encima de ese empleado en la jerarquía, la cantidad de niveles entre el gerente y el empleado y la ruta entre los dos:
SELECCIONAR ename "Empleado" , CONECTAR_POR_RAÍZ ename "Gerente" , NIVEL - 1 "Ruta" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Ruta" DE emp DONDE NIVEL > 1 Y deptno = 10 CONECTAR POR ANTERIOR empno = mgr ORDENAR POR "Empleado" , "Gerente" , "Ruta" , "Ruta" ; SYS_CONNECT_BY_PATHLibros de texto académicos . Tenga en cuenta que estos cubren únicamente el estándar SQL:1999 (y Datalog), pero no la extensión Oracle.