Una consulta jerárquica es un tipo de consulta SQL que maneja datos de modelos jerárquicos . Son casos especiales de consultas de puntos fijos recursivas más generales , que calculan cierres transitivos .
En SQL estándar: 1999, las consultas jerárquicas se implementan mediante expresiones de tabla común recursivas (CTE). A diferencia de la cláusula de conexión anterior de Oracle, los CTE recursivos se diseñaron con semántica de punto fijo desde el principio. [1] Los CTE recursivos del estándar estaban relativamente cerca de la implementación existente en IBM DB2 versión 2. [1] Los CTE recursivos 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 los CTE. TIBCO Spotfire no admite CTE, mientras que la implementación de Oracle 11g Release 2 carece de semántica de puntos fijos.
Sin expresiones de tabla comunes ni cláusulas conectadas por, 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 alcance de ejecución de una declaración SELECT, o .INSERTUPDATEDELETE
Los CTE se pueden considerar como alternativas a las tablas derivadas ( subconsulta ), vistas y funciones en línea definidas por el usuario.
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 recursividad). desde 11g versión 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 versión 9), Vertica , H2 (experimental), [10] y muchos otros . Oracle llama a los CTE "factorización de subconsultas". [11]
La sintaxis de un CTE (que puede ser recursivo o no) es la siguiente:
CON [ RECURSIVO ] con_query [, ...] SELECCIONAR ... donde with_queryla sintaxis es:
nombre_consulta [ ( nombre_columna [,...]) ] AS ( SELECCIONAR ...) Los CTE recursivos se pueden utilizar para recorrer relaciones (como gráficos o árboles), aunque la sintaxis es mucho más complicada porque no se crean pseudocolumnas automáticas (como LEVELse muestra a continuación); si se desean, deben crearse 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 CON en sistemas distintos a 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 usando CREATE[ RECURSIVE] VIEW. [15] Usando un CTE dentro de un 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 declaración de procedimiento. [dieciséis]
Algunas bases de datos, como PostgreSQL, admiten un formato CREAR VISTA RECURSIVA más corto que se traduce internamente a CON codificación RECURSIVA. [17]
Un ejemplo de consulta recursiva que calcula el factorial de números del 0 al 9 es el siguiente:
CON temp recursiva ( n , hecho ) AS ( SELECCIONE 0 , 1 - Subconsulta inicial UNION TODO SELECCIONE n + 1 , ( n + 1 ) * hecho DESDE temp DONDE n < 9 - Subconsulta recursiva ) SELECCIONE * DESDE temp ; CONNECT BYUna sintaxis alternativa es la construcción no estándar ; Fue introducido por Oracle en la década de 1980. [18] Antes de Oracle 10g, la construcción solo era útil para atravesar 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), haciendo que el recorrido también funcione en presencia de ciclos. [19]
CONNECT BYes compatible con Snowflake , EnterpriseDB , [20] base de datos Oracle , [21] CUBRID , [22] IBM Informix [23] e IBM Db2 aunque solo si está habilitado como modo de compatibilidad. [24] La sintaxis es la siguiente:
SELECCIONE select_list DESDE table_expression [ DONDE ... ] [ COMENZAR CON start_expression ] CONECTAR POR [ NOCYCLE ] { PRIOR child_expr = parent_expr | parent_expr = ANTERIOR child_expr } [ ORDENAR HERMANOS POR columna1 [ ASC | DESC ] [, columna2 [ ASC | DESC ] ] ... ] [ AGRUPAR POR ... ] [ TENER ... ] ... SELECCIONAR NIVEL , LPAD ( ' ' , 2 * ( NIVEL - 1 )) || ename "empleado" , empno , mgr "gerente" DESDE emp COMENZAR CON mgr ES NULO CONECTAR POR 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 | ADÁN | 7876 | 7788 3 | VADO | 7902 | 7566 4 | HERRERO | 7369 | 7902 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | GUARDIA | 7521 | 7698 3 | MARTÍN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | JAMES | 7900 | 7698 2 | CLARK | 7782 | 7839 3 | MOlinero | 7934 | 7782(14 filas)
El siguiente ejemplo devuelve el apellido de cada empleado en el departamento 10, cada gerente por encima de ese empleado en la jerarquía, el número de niveles entre gerente y empleado y la ruta entre los dos:
SELECCIONE nombre "Empleado" , CONNECT_BY_ROOT nombre "Administrador" , NIVEL - 1 "Pathlen" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Ruta" DESDE emp DONDE NIVEL > 1 Y deptno = 10 CONECTAR POR ANTES empno = mgr ORDENAR POR "Empleado " , "Administrador" , "Pathlen" , "Ruta" ; SYS_CONNECT_BY_PATHLibros de texto académicos . Tenga en cuenta que estos cubren sólo el estándar SQL:1999 (y Datalog), pero no la extensión de Oracle.