Una cláusula de combinación en SQL , que corresponde a una operación de combinación en álgebra relacional , combina columnas de una o más tablas en una nueva tabla. ANSI -STANDARD SQL especifica cinco tipos de JOIN
: INNER
, LEFT OUTER
, RIGHT OUTER
, FULL OUTER
y CROSS
.
Tablas de ejemplo
Para explicar los tipos de combinación, el resto de este artículo utiliza las siguientes tablas:
Apellido | DepartmentID |
---|---|
Rafferty | 31 |
Jones | 33 |
Heisenberg | 33 |
Robinson | 34 |
Herrero | 34 |
Williams | NULL |
DepartmentID | Nombre de Departamento |
---|---|
31 | Ventas |
33 | Ingenieria |
34 | Clerical |
35 | Márketing |
Department.DepartmentID
es la clave principal de la Department
tabla, mientras que Employee.DepartmentID
es una clave externa .
Tenga en cuenta que en Employee
, "Williams" aún no se ha asignado a un departamento. Además, no se ha asignado ningún empleado al departamento de "Marketing".
Esta es la declaración SQL para crear las tablas anteriores:
Departamento CREAR TABLA ( DepartmentID INT PRIMARY KEY NOT NULL , Nombre de departamento VARCHAR ( 20 )); Empleado CREAR TABLA ( Apellido VARCHAR ( 20 ), DepartmentID INT REFERENCES departamento ( DepartmentID ));INSERTAR EN el departamentoVALORES ( 31 , 'Ventas' ), ( 33 , 'Ingeniería' ), ( 34 , 'clerical' ), ( 35 , "Marketing" );INSERT INTO empleadoVALORES ( 'Rafferty' , 31 ), ( 'Jones' , 33 ), ( 'Heisenberg' , 33 ), ( 'Robinson' , 34 años ), ( 'Smith' , 34 años ), ( 'Williams' , NULO );
Unión cruzada
CROSS JOIN
devuelve el producto cartesiano de filas de tablas en la combinación. En otras palabras, producirá filas que combinan cada fila de la primera tabla con cada fila de la segunda tabla. [1]
Empleado Apellido | Employee.DepartmentID | Departamento.DepartmentName | Department.DepartmentID |
---|---|---|---|
Rafferty | 31 | Ventas | 31 |
Jones | 33 | Ventas | 31 |
Heisenberg | 33 | Ventas | 31 |
Herrero | 34 | Ventas | 31 |
Robinson | 34 | Ventas | 31 |
Williams | NULL | Ventas | 31 |
Rafferty | 31 | Ingenieria | 33 |
Jones | 33 | Ingenieria | 33 |
Heisenberg | 33 | Ingenieria | 33 |
Herrero | 34 | Ingenieria | 33 |
Robinson | 34 | Ingenieria | 33 |
Williams | NULL | Ingenieria | 33 |
Rafferty | 31 | Clerical | 34 |
Jones | 33 | Clerical | 34 |
Heisenberg | 33 | Clerical | 34 |
Herrero | 34 | Clerical | 34 |
Robinson | 34 | Clerical | 34 |
Williams | NULL | Clerical | 34 |
Rafferty | 31 | Márketing | 35 |
Jones | 33 | Márketing | 35 |
Heisenberg | 33 | Márketing | 35 |
Herrero | 34 | Márketing | 35 |
Robinson | 34 | Márketing | 35 |
Williams | NULL | Márketing | 35 |
Ejemplo de una unión cruzada explícita:
SELECCIONE * DEL departamento CROSS JOIN del empleado ;
Ejemplo de una combinación cruzada implícita:
SELECCIONAR * DE empleado , departamento ;
La combinación cruzada se puede reemplazar con una combinación interna con una condición siempre verdadera:
SELECCIONAR * DEL empleado INNER JOIN departamento ON 1 = 1 ;
CROSS JOIN
no aplica en sí mismo ningún predicado para filtrar filas de la tabla unida. Los resultados de a CROSS JOIN
se pueden filtrar mediante una WHERE
cláusula, que luego puede producir el equivalente de una combinación interna.
En el estándar SQL: 2011 , las combinaciones cruzadas son parte del paquete opcional F401, "Tabla unida extendida".
Los usos normales son para comprobar el rendimiento del servidor. [ ¿por qué? ]
Unir internamente
Una combinación interna requiere que cada fila de las dos tablas unidas tenga valores de columna coincidentes y es una operación de combinación de uso común en las aplicaciones, pero no se debe suponer que sea la mejor opción en todas las situaciones. La combinación interna crea una nueva tabla de resultados combinando valores de columna de dos tablas (A y B) según el predicado de combinación. La consulta compara cada fila de A con cada fila de B para encontrar todos los pares de filas que satisfacen el predicado de unión. Cuando el predicado de unión se satisface al hacer coincidir valores no NULL , los valores de columna para cada par coincidente de filas de A y B se combinan en una fila de resultados.
El resultado de la combinación se puede definir como el resultado de tomar primero el producto cartesiano (o combinación cruzada ) de todas las filas de las tablas (combinando cada fila de la tabla A con cada fila de la tabla B) y luego devolver todas las filas que satisfacen el unir predicado. Las implementaciones reales de SQL normalmente utilizan otros enfoques, como combinaciones de hash o combinaciones de combinación de clasificación , ya que calcular el producto cartesiano es más lento y, a menudo, requeriría una cantidad prohibitivamente grande de memoria para almacenar.
SQL especifica dos formas sintácticas diferentes de expresar combinaciones: la "notación de combinación explícita" y la "notación de combinación implícita". La "notación de combinación implícita" ya no se considera una práctica recomendada, aunque los sistemas de bases de datos aún la admiten.
La "notación de combinación explícita" usa la JOIN
palabra clave, opcionalmente precedida por la INNER
palabra clave, para especificar la tabla a unir, y la ON
palabra clave para especificar los predicados para la combinación, como en el siguiente ejemplo:
SELECCIONAR empleado . Apellido , empleado . DepartmentID , departamento . DepartmentName FROM empleado INNER JOIN departamento ON empleado . DepartmentID = departamento . DepartmentID ;
Empleado Apellido | Employee.DepartmentID | Departamento.DepartmentName |
---|---|---|
Robinson | 34 | Clerical |
Jones | 33 | Ingenieria |
Herrero | 34 | Clerical |
Heisenberg | 33 | Ingenieria |
Rafferty | 31 | Ventas |
La "notación de combinación implícita" simplemente enumera las tablas para unir, en la FROM
cláusula de la SELECT
declaración, usando comas para separarlas. Por lo tanto, especifica una combinación cruzada , y la WHERE
cláusula puede aplicar predicados de filtro adicionales (que funcionan de manera comparable a los predicados de combinación en la notación explícita).
El siguiente ejemplo es equivalente al anterior, pero esta vez usando la notación de combinación implícita:
SELECCIONAR empleado . Apellido , empleado . DepartmentID , departamento . DepartmentName FROM empleado , departamento DONDE empleado . DepartmentID = departamento . DepartmentID ;
Las consultas dadas en los ejemplos anteriores se unirán a las tablas Empleado y Departamento utilizando la columna DepartmentID de ambas tablas. Cuando el DepartmentID de estas tablas coincide (es decir, se satisface el predicado de unión), la consulta combinará las columnas LastName , DepartmentID y DepartmentName de las dos tablas en una fila de resultados. Cuando DepartmentID no coincide, no se genera ninguna fila de resultados.
Así, el resultado de la ejecución de la consulta anterior será:
Empleado Apellido | Employee.DepartmentID | Departamento.DepartmentName |
---|---|---|
Robinson | 34 | Clerical |
Jones | 33 | Ingenieria |
Herrero | 34 | Clerical |
Heisenberg | 33 | Ingenieria |
Rafferty | 31 | Ventas |
El empleado "Williams" y el departamento "Marketing" no aparecen en los resultados de ejecución de la consulta. Ninguno de estos tiene filas coincidentes en la otra tabla respectiva: "Williams" no tiene un departamento asociado y ningún empleado tiene el ID de departamento 35 ("Marketing"). Dependiendo de los resultados deseados, este comportamiento puede ser un error sutil, que se puede evitar reemplazando la unión interna por una unión externa .
Combinación interna y valores NULL
Los programadores deben tener especial cuidado al unir tablas en columnas que pueden contener valores NULL , ya que NULL nunca coincidirá con ningún otro valor (ni siquiera NULL), a menos que la condición de combinación use explícitamente un predicado de combinación que primero verifique que las columnas de combinación sean NOT NULL
antes de aplicar las condiciones de predicado restantes. La combinación interna solo se puede usar de forma segura en una base de datos que refuerza la integridad referencial o donde se garantiza que las columnas de combinación no serán NULL. Muchas bases de datos relacionales de procesamiento de transacciones se basan en los estándares de actualización de datos de Atomicidad, Consistencia, Aislamiento, Durabilidad (ACID) para garantizar la integridad de los datos, lo que hace que las uniones internas sean una opción adecuada. Sin embargo, las bases de datos de transacciones también suelen tener columnas de unión deseables que pueden ser NULL. Muchos almacenes de datos y bases de datos relacionales de informes utilizan actualizaciones por lotes de extracción, transformación y carga (ETL) de gran volumen que dificultan o imposibilitan la aplicación de la integridad referencial, lo que da como resultado columnas de unión potencialmente NULL que un autor de consultas SQL no puede modificar y que provocan la omisión de las uniones internas. datos sin indicación de error. La elección de utilizar una combinación interna depende del diseño de la base de datos y las características de los datos. Por lo general, una combinación externa izquierda se puede sustituir por una combinación interna cuando las columnas de combinación en una tabla pueden contener valores NULL.
Cualquier columna de datos que pueda ser NULL (vacía) nunca debe usarse como un enlace en una combinación interna, a menos que el resultado deseado sea eliminar las filas con el valor NULL. Si las columnas de combinación NULL deben eliminarse deliberadamente del conjunto de resultados , una combinación interna puede ser más rápida que una combinación externa porque la combinación de la tabla y el filtrado se realizan en un solo paso. Por el contrario, una combinación interna puede resultar en un rendimiento desastrosamente lento o incluso un bloqueo del servidor cuando se usa en una consulta de gran volumen en combinación con funciones de base de datos en una cláusula Where de SQL. [2] [3] [4] Una función en una cláusula Where de SQL puede hacer que la base de datos ignore índices de tabla relativamente compactos. La base de datos puede leer y unir internamente las columnas seleccionadas de ambas tablas antes de reducir el número de filas usando el filtro que depende de un valor calculado, lo que resulta en una cantidad relativamente enorme de procesamiento ineficiente.
Cuando se produce un conjunto de resultados al unir varias tablas, incluidas las tablas maestras que se utilizan para buscar descripciones de texto completo de códigos de identificadores numéricos (una tabla de búsqueda ), un valor NULO en cualquiera de las claves externas puede resultar en la eliminación de toda la fila de el conjunto de resultados, sin indicación de error. Una consulta SQL compleja que incluye una o más combinaciones internas y varias combinaciones externas tiene el mismo riesgo de valores NULL en las columnas del vínculo de combinación interna.
Un compromiso con el código SQL que contiene combinaciones internas supone que las columnas de combinación NULL no serán introducidas por cambios futuros, incluidas actualizaciones de proveedores, cambios de diseño y procesamiento masivo fuera de las reglas de validación de datos de la aplicación, como conversiones de datos, migraciones, importaciones masivas y fusiones.
Además, se pueden clasificar las uniones internas como equi-uniones, como uniones naturales o como uniones cruzadas.
Equi-join
Una combinación equitativa es un tipo específico de combinación basada en comparadores, que usa solo comparaciones de igualdad en el predicado de combinación. El uso de otros operadores de comparación (como <
) descalifica una combinación como una combinación equitativa. La consulta que se muestra arriba ya ha proporcionado un ejemplo de equi-join:
SELECCIONAR * DEL empleado ÚNETE al departamento EN el empleado . DepartmentID = departamento . DepartmentID ;
Podemos escribir equi-join como se muestra a continuación,
SELECCIONE * DE empleado , departamento DONDE empleado . DepartmentID = departamento . DepartmentID ;
Si las columnas de un equi-join tienen el mismo nombre, SQL-92 proporciona una notación abreviada opcional para expresar equi-joins, mediante la USING
construcción: [5]
SELECCIONE * DEL empleado INNER JOIN departamento USANDO ( DepartmentID );
La USING
construcción es más que una mera azúcar sintáctico , sin embargo, ya que el conjunto de resultados difiere del conjunto de resultados de la versión con el predicado explícito. Específicamente, cualquier columna mencionada en la USING
lista aparecerá solo una vez, con un nombre no calificado, en lugar de una vez para cada tabla en la combinación. En el caso anterior, habrá una sola DepartmentID
columna y no employee.DepartmentID
o department.DepartmentID
.
La USING
cláusula no es compatible con MS SQL Server y Sybase.
Unión natural
La unión natural es un caso especial de equi-unión. La unión natural (⋈) es un operador binario que se escribe ( R ⋈ S ) donde R y S son relaciones . [6] El resultado de la unión natural es el conjunto de todas las combinaciones de tuplas en R y S que son iguales en sus nombres de atributos comunes. Por ejemplo, considere las tablas Employee y Dept y su combinación natural:
|
|
|
Esto también se puede utilizar para definir la composición de relaciones . Por ejemplo, la composición de Empleado y Dept es su unión como se muestra arriba, proyectada en todos menos el atributo común DeptName . En teoría de categorías , la unión es precisamente el producto de fibra .
La unión natural es posiblemente uno de los operadores más importantes, ya que es la contraparte relacional del AND lógico. Tenga en cuenta que si la misma variable aparece en cada uno de los dos predicados que están conectados por AND, entonces esa variable representa lo mismo y ambas apariencias siempre deben ser sustituidas por el mismo valor. En particular, la combinación natural permite la combinación de relaciones que están asociadas por una clave externa . Por ejemplo, en el ejemplo anterior, una clave externa probablemente se retenga de Empleado . DEPTNAME al Dpto . DeptName y luego la unión natural de Employee y Dept combina a todos los empleados con sus departamentos. Esto funciona porque la clave externa se mantiene entre atributos con el mismo nombre. Si este no es el caso, por ejemplo, en la clave externa de Dpto . gerente al empleado . Nombre, entonces estas columnas deben cambiarse de nombre antes de que se tome la combinación natural. A veces, esta combinación también se denomina equi-join .
Más formalmente, la semántica de la combinación natural se define de la siguiente manera:
- ,
donde Fun es un predicado que es verdadero para una relación r si y solo si r es una función. Por lo general, se requiere que R y S tengan al menos un atributo común, pero si se omite esta restricción y R y S no tienen atributos comunes, entonces la unión natural se convierte exactamente en el producto cartesiano.
La unión natural se puede simular con las primitivas de Codd de la siguiente manera. Deje c 1 , ..., c m sea los nombres de los atributos comunes a R y S , r 1 , ..., r n ser los nombres de los atributos únicos para R y dejar s 1 , ..., s k serán los atributos exclusivos de S . Además, se supone que los nombres de atributo x 1 , ..., x m son ni en R ni en S . En un primer paso, ahora se puede cambiar el nombre de los nombres de atributos comunes en S :
Luego tomamos el producto cartesiano y seleccionamos las tuplas que se van a unir:
Una combinación natural es un tipo de combinación equitativa en la que el predicado de combinación surge implícitamente al comparar todas las columnas de ambas tablas que tienen los mismos nombres de columna en las tablas combinadas. La tabla combinada resultante contiene solo una columna para cada par de columnas con el mismo nombre. En el caso de que no se encuentren columnas con los mismos nombres, el resultado es una combinación cruzada .
La mayoría de los expertos están de acuerdo en que los NATURAL JOIN son peligrosos y, por lo tanto, desaconsejan su uso. [7] El peligro proviene de agregar inadvertidamente una nueva columna, con el mismo nombre que otra columna en la otra tabla. Una combinación natural existente podría entonces usar "naturalmente" la nueva columna para comparaciones, haciendo comparaciones / coincidencias usando diferentes criterios (de diferentes columnas) que antes. Por lo tanto, una consulta existente podría producir resultados diferentes, aunque los datos de las tablas no se hayan modificado, solo se hayan aumentado. El uso de nombres de columnas para determinar automáticamente los enlaces de las tablas no es una opción en bases de datos grandes con cientos o miles de tablas donde impondría una restricción poco realista en las convenciones de nomenclatura. Las bases de datos del mundo real se diseñan comúnmente con datos de clave externa que no se completan de manera consistente (se permiten valores NULL), debido a las reglas de negocio y al contexto. Es una práctica común modificar los nombres de columnas de datos similares en diferentes tablas y esta falta de coherencia rígida relega las uniones naturales a un concepto teórico para la discusión.
La consulta de muestra anterior para combinaciones internas se puede expresar como una combinación natural de la siguiente manera:
SELECCIONE * DEL empleado del departamento NATURAL JOIN ;
Al igual que con la USING
cláusula explícita , solo aparece una columna DepartmentID en la tabla unida, sin calificador:
DepartmentID | Empleado Apellido | Departamento.DepartmentName |
---|---|---|
34 | Herrero | Clerical |
33 | Jones | Ingenieria |
34 | Robinson | Clerical |
33 | Heisenberg | Ingenieria |
31 | Rafferty | Ventas |
PostgreSQL, MySQL y Oracle admiten uniones naturales; Microsoft T-SQL e IBM DB2 no lo hacen. Las columnas utilizadas en la combinación son implícitas, por lo que el código de combinación no muestra qué columnas se esperan y un cambio en los nombres de las columnas puede cambiar los resultados. En el estándar SQL: 2011 , las uniones naturales son parte del paquete opcional F401, "Tabla unida extendida".
En muchos entornos de bases de datos, los nombres de las columnas los controla un proveedor externo, no el desarrollador de consultas. Una unión natural supone estabilidad y coherencia en los nombres de las columnas, que pueden cambiar durante las actualizaciones de versión exigidas por el proveedor.
Unión externa
La tabla unida conserva cada fila, incluso si no existe ninguna otra fila coincidente. Las combinaciones externas se subdividen aún más en combinaciones externas izquierdas, combinaciones externas derechas y combinaciones externas completas, según las filas de la tabla que se retengan: izquierda, derecha o ambas (en este caso, la izquierda y la derecha se refieren a los dos lados de la JOIN
palabra clave). Al igual que las uniones internas , se pueden subcategorizar aún más todos los tipos de uniones externas como equi-uniones , uniones naturales , ( θ -join ), etc. [8]ON
No existe una notación de combinación implícita para combinaciones externas en SQL estándar.
Izquierda combinación externa
El resultado de una combinación externa izquierda (o simplemente combinación izquierda ) para las tablas A y B siempre contiene todas las filas de la tabla "izquierda" (A), incluso si la condición de combinación no encuentra ninguna fila coincidente en la tabla "derecha" (B). Esto significa que si la ON
cláusula coincide con 0 (cero) filas en B (para una fila determinada en A), la combinación seguirá devolviendo una fila en el resultado (para esa fila), pero con NULL en cada columna de B. A izquierda La combinación externa devuelve todos los valores de una combinación interna más todos los valores de la tabla de la izquierda que no coinciden con la tabla de la derecha, incluidas las filas con valores NULL (vacíos) en la columna de enlace.
Por ejemplo, esto nos permite encontrar el departamento de un empleado, pero aún muestra los empleados que no han sido asignados a un departamento (al contrario del ejemplo de combinación interna anterior, donde los empleados no asignados se excluyeron del resultado).
Ejemplo de una combinación externa izquierda (la OUTER
palabra clave es opcional), con la fila de resultado adicional (en comparación con la combinación interna) en cursiva:
SELECCIONE * DEL empleado IZQUIERDA EXTERIOR ÚNASE al departamento EN el empleado . DepartmentID = departamento . DepartmentID ;
Empleado Apellido | Employee.DepartmentID | Departamento.DepartmentName | Department.DepartmentID |
---|---|---|---|
Jones | 33 | Ingenieria | 33 |
Rafferty | 31 | Ventas | 31 |
Robinson | 34 | Clerical | 34 |
Herrero | 34 | Clerical | 34 |
Williams | NULL | NULL | NULL |
Heisenberg | 33 | Ingenieria | 33 |
Sintaxis alternativas
Oracle admite la sintaxis obsoleta [9] :
SELECCIONE * DE empleado , departamento DONDE empleado . DepartmentID = departamento . DepartamentoID ( + )
Sybase admite la sintaxis ( Microsoft SQL Server desaprobó esta sintaxis desde la versión 2000):
SELECCIONAR * DE empleado , departamento DONDE empleado . DepartmentID * = departamento . DepartmentID
IBM Informix admite la sintaxis:
SELECCIONE * DE empleado , departamento EXTERIOR DONDE empleado . DepartmentID = departamento . DepartmentID
Unión exterior derecha
Una combinación externa derecha (o combinación derecha ) se parece mucho a una combinación externa izquierda, excepto con el tratamiento de las tablas al revés. Cada fila de la tabla "derecha" (B) aparecerá en la tabla unida al menos una vez. Si no existe una fila coincidente de la tabla "izquierda" (A), aparecerá NULL en las columnas de A para aquellas filas que no tengan coincidencia en B.
Una combinación externa derecha devuelve todos los valores de la tabla derecha y los valores coincidentes de la tabla izquierda (NULL en el caso de que no haya un predicado de combinación coincidente). Por ejemplo, esto nos permite encontrar a cada empleado y su departamento, pero aún mostrar los departamentos que no tienen empleados.
A continuación se muestra un ejemplo de una combinación externa derecha (la OUTER
palabra clave es opcional), con la fila de resultado adicional en cursiva:
SELECCIONE * DEL empleado DERECHA EXTERIOR ÚNASE al departamento EN el empleado . DepartmentID = departamento . DepartmentID ;
Empleado Apellido | Employee.DepartmentID | Departamento.DepartmentName | Department.DepartmentID |
---|---|---|---|
Herrero | 34 | Clerical | 34 |
Jones | 33 | Ingenieria | 33 |
Robinson | 34 | Clerical | 34 |
Heisenberg | 33 | Ingenieria | 33 |
Rafferty | 31 | Ventas | 31 |
NULL | NULL | Márketing | 35 |
Las combinaciones externas derecha e izquierda son funcionalmente equivalentes. Ninguno proporciona ninguna funcionalidad que el otro no, por lo que las combinaciones externas derecha e izquierda pueden reemplazarse entre sí siempre que se cambie el orden de la tabla.
Unión externa completa
Conceptualmente, una combinación externa completa combina el efecto de aplicar combinaciones externas tanto a la izquierda como a la derecha. Cuando las filas de las tablas FULL OUTER JOINed no coinciden, el conjunto de resultados tendrá valores NULL para cada columna de la tabla que carece de una fila coincidente. Para aquellas filas que coincidan, se generará una sola fila en el conjunto de resultados (que contiene columnas pobladas de ambas tablas).
Por ejemplo, esto nos permite ver a cada empleado que está en un departamento y cada departamento que tiene un empleado, pero también ver a cada empleado que no es parte de un departamento y cada departamento que no tiene un empleado.
Ejemplo de una combinación externa completa (la OUTER
palabra clave es opcional):
SELECCIONAR * DEL empleado COMPLETO EXTERIOR UNIR al departamento EN el empleado . DepartmentID = departamento . DepartmentID ;
Empleado Apellido | Employee.DepartmentID | Departamento.DepartmentName | Department.DepartmentID |
---|---|---|---|
Herrero | 34 | Clerical | 34 |
Jones | 33 | Ingenieria | 33 |
Robinson | 34 | Clerical | 34 |
Williams | NULL | NULL | NULL |
Heisenberg | 33 | Ingenieria | 33 |
Rafferty | 31 | Ventas | 31 |
NULL | NULL | Márketing | 35 |
Algunos sistemas de bases de datos no admiten directamente la funcionalidad de combinación externa completa, pero pueden emularla mediante el uso de una combinación interna y UNION ALL selecciona las "filas de tabla única" de las tablas izquierda y derecha, respectivamente. El mismo ejemplo puede aparecer de la siguiente manera:
SELECCIONAR empleado . Apellido , empleado . DepartmentID , departamento . DepartmentName , departamento . DepartmentID FROM empleado INNER JOIN departamento ON empleado . DepartmentID = departamento . DepartmentIDUNIÓN TODOSSELECCIONAR empleado . Apellido , empleado . DepartmentID , cast ( NULL como varchar ( 20 )), cast ( NULL como entero ) FROM empleado DONDE NO EXISTE ( SELECCIONAR * FROM departamento DONDE empleado . DepartamentoID = departamento . DepartamentoID )UNIÓN TODOSSELECT cast ( NULL como varchar ( 20 )), cast ( NULL como entero ), departamento . DepartmentName , departamento . DepartmentID FROM departamento DONDE NO EXISTE ( SELECCIONE * FROM empleado DONDE empleado . DepartmentID = departamento . DepartmentID )
Auto-unirse
Una autounión es unirse a una mesa consigo misma. [10]
Ejemplo
Si hubiera dos tablas separadas para empleados y una consulta que solicitara empleados en la primera tabla que tuvieran el mismo país que los empleados en la segunda tabla, se podría usar una operación de combinación normal para encontrar la tabla de respuestas. Sin embargo, toda la información de los empleados está contenida en una única tabla grande. [11]
Considere una Employee
tabla modificada como la siguiente:
ID de empleado | Apellido | País | DepartmentID |
---|---|---|---|
123 | Rafferty | Australia | 31 |
124 | Jones | Australia | 33 |
145 | Heisenberg | Australia | 33 |
201 | Robinson | Estados Unidos | 34 |
305 | Herrero | Alemania | 34 |
306 | Williams | Alemania | NULL |
Una consulta de solución de ejemplo podría ser la siguiente:
SELECT F . EmployeeID , F . LastName , S . EmployeeID , S . LastName , F . País DE Empleado F INTERIOR ÚNETE Empleado S EN C . País = S . País DONDE F . EmployeeID < S . EmployeeID ORDEN POR F . EmployeeID , S . EmployeeID ;
Lo que da como resultado la generación de la siguiente tabla.
ID de empleado | Apellido | ID de empleado | Apellido | País |
---|---|---|---|---|
123 | Rafferty | 124 | Jones | Australia |
123 | Rafferty | 145 | Heisenberg | Australia |
124 | Jones | 145 | Heisenberg | Australia |
305 | Herrero | 306 | Williams | Alemania |
Para este ejemplo:
F
yS
son alias para la primera y segunda copias de la tabla de empleados.- La condición
F.Country = S.Country
excluye emparejamientos entre empleados en diferentes países. La pregunta del ejemplo solo quería pares de empleados en el mismo país. - La condición
F.EmployeeID < S.EmployeeID
excluye las parejas en las que laEmployeeID
del primer empleado es mayor o igual que laEmployeeID
del segundo empleado. En otras palabras, el efecto de esta condición es excluir emparejamientos duplicados y autoparajes. Sin él, se generaría la siguiente tabla menos útil (la siguiente tabla muestra solo la parte "Alemania" del resultado):
ID de empleado | Apellido | ID de empleado | Apellido | País |
---|---|---|---|---|
305 | Herrero | 305 | Herrero | Alemania |
305 | Herrero | 306 | Williams | Alemania |
306 | Williams | 305 | Herrero | Alemania |
306 | Williams | 306 | Williams | Alemania |
Solo se necesita uno de los dos pares del medio para satisfacer la pregunta original, y el superior y el inferior no tienen ningún interés en este ejemplo.
Alternativas
El efecto de una combinación externa también se puede obtener utilizando UNION ALL entre una INNER JOIN y una SELECT de las filas de la tabla "principal" que no cumplen la condición de combinación. Por ejemplo,
SELECCIONAR empleado . Apellido , empleado . DepartmentID , departamento . Nombre de departamento FROM empleado IZQUIERDA EXTERIOR UNIRSE departamento EN empleado . DepartmentID = departamento . DepartmentID ;
también se puede escribir como
SELECCIONAR empleado . Apellido , empleado . DepartmentID , departamento . DepartmentName FROM empleado INNER JOIN departamento ON empleado . DepartmentID = departamento . DepartmentIDUNIÓN TODOSSELECCIONAR empleado . Apellido , empleado . DepartmentID , cast ( NULL as varchar ( 20 )) FROM employee WHERE NOT EXISTS ( SELECT * FROM department WHERE employee . DepartmentID = department . DepartmentID )
Implementación
Gran parte del trabajo en los sistemas de bases de datos ha tenido como objetivo la implementación eficiente de combinaciones, porque los sistemas relacionales comúnmente requieren combinaciones, pero enfrentan dificultades para optimizar su ejecución eficiente. El problema surge porque las uniones internas operan tanto conmutativa como asociativamente . En la práctica, esto significa que el usuario simplemente proporciona la lista de tablas para unir y las condiciones de unir para usar, y el sistema de base de datos tiene la tarea de determinar la forma más eficiente de realizar la operación. Un optimizador de consultas determina cómo ejecutar una consulta que contiene combinaciones. Un optimizador de consultas tiene dos libertades básicas:
- Orden de unión : debido a que une funciones conmutativa y asociativamente, el orden en el que el sistema une las tablas no cambia el conjunto de resultados final de la consulta. Sin embargo, el orden de unión podría tener un impacto enorme en el costo de la operación de unión, por lo que elegir el mejor orden de unión se vuelve muy importante.
- Método de combinación: dadas dos tablas y una condición de combinación, varios algoritmos pueden producir el conjunto de resultados de la combinación. El algoritmo que se ejecuta con mayor eficacia depende del tamaño de las tablas de entrada, el número de filas de cada tabla que coinciden con la condición de unión y las operaciones requeridas por el resto de la consulta.
Muchos algoritmos de unión tratan sus entradas de manera diferente. Uno puede referirse a las entradas a una combinación como los operandos de combinación "externos" e "internos", o "izquierda" y "derecha", respectivamente. En el caso de bucles anidados, por ejemplo, el sistema de base de datos escaneará toda la relación interna para cada fila de la relación externa.
Se pueden clasificar los planes de consulta que involucran combinaciones de la siguiente manera: [12]
- profundo a la izquierda
- usando una tabla base (en lugar de otra combinación) como operando interno de cada combinación en el plan
- profundo a la derecha
- usando una tabla base como el operando externo de cada combinación en el plan
- tupido
- ni profundo a la izquierda ni profundo a la derecha; ambas entradas a una combinación pueden resultar de combinaciones
Estos nombres derivan de la apariencia del plan de consulta si se dibuja como un árbol , con la relación de unión externa a la izquierda y la relación interna a la derecha (como dicta la convención).
Unir algoritmos
Tres algoritmos fundamentales para la realización de una operación unen existen: bucle anidado , clasificación-fusión y combinación hash .
Unir índices
Los índices de unión son índices de bases de datos que facilitan el procesamiento de consultas de unión en almacenes de datos : actualmente (2012) están disponibles en implementaciones de Oracle [13] y Teradata . [14]
En la implementación de Teradata, las columnas especificadas, las funciones agregadas en las columnas o los componentes de las columnas de fecha de una o más tablas se especifican utilizando una sintaxis similar a la definición de una vista de base de datos : se pueden especificar hasta 64 columnas / expresiones de columna en una sola unirse al índice. Opcionalmente, también se puede especificar una columna que define la clave principal de los datos compuestos: en hardware paralelo, los valores de columna se utilizan para dividir el contenido del índice en varios discos. Cuando los usuarios actualizan interactivamente las tablas de origen, el contenido del índice de combinación se actualiza automáticamente. Cualquier consulta cuya cláusula WHERE especifique cualquier combinación de columnas o expresiones de columna que sean un subconjunto exacto de las definidas en un índice de combinación (una llamada "consulta de cobertura") provocará el índice de combinación, en lugar de las tablas originales y sus índices, para ser consultado durante la ejecución de la consulta.
La implementación de Oracle se limita a utilizar índices de mapa de bits . Un índice de unión de mapa de bits se utiliza para columnas de baja cardinalidad (es decir, columnas que contienen menos de 300 valores distintos, según la documentación de Oracle): combina columnas de baja cardinalidad de varias tablas relacionadas. El ejemplo que usa Oracle es el de un sistema de inventario, donde diferentes proveedores proporcionan diferentes partes. El esquema tiene tres tablas vinculadas: dos "tablas maestras", Pieza y Proveedor, y una "tabla de detalle", Inventario. La última es una tabla de varios a varios que vincula al proveedor con la pieza y contiene la mayor cantidad de filas. Cada pieza tiene un tipo de pieza, y cada proveedor tiene su sede en los EE. UU. Y tiene una columna de estado. No hay más de 60 estados + territorios en los EE. UU. Y no más de 300 tipos de piezas. El índice de combinación de mapa de bits se define utilizando una combinación estándar de tres tablas en las tres tablas anteriores y especificando las columnas Part_Type y Supplier_State para el índice. Sin embargo, se define en la tabla de Inventario, aunque las columnas Part_Type y Supplier_State son "prestadas" de Supplier y Part respectivamente.
En cuanto a Teradata, un índice de combinación de mapa de bits de Oracle solo se utiliza para responder una consulta cuando la cláusula WHERE de la consulta especifica columnas limitadas a las que están incluidas en el índice de combinación.
Unión recta
Algunos sistemas de bases de datos permiten al usuario forzar al sistema a leer las tablas en una combinación en un orden particular. Esto se usa cuando el optimizador de combinación elige leer las tablas en un orden ineficiente. Por ejemplo, en MySQL, el comando STRAIGHT_JOIN
lee las tablas exactamente en el orden indicado en la consulta. [15]
Ver también
- Join (álgebra relacional)
- Antiunión
- Establecer operaciones (SQL)
Referencias
Citas
- ^ SQL CROSS JOIN
- ^ Greg Robidoux, "Evite las funciones de SQL Server en la cláusula WHERE para el rendimiento", Consejos de MSSQL, 3 de mayo de 2007
- ^ Patrick Wolf, "Inside Oracle APEX" Precaución al utilizar funciones PL / SQL en una declaración SQL ", 30 de noviembre de 2006
- ^ Gregory A. Larsen, "Mejores prácticas de T-SQL: no utilice funciones de valor escalar en la lista de columnas o cláusulas WHERE", 29 de octubre de 2009,
- ^ Simplificación de combinaciones con la palabra clave USING
- ^ En Unicode , el símbolo de la pajarita es ⋈ (U + 22C8).
- ^ Pregúntele a Tom "Compatibilidad con Oracle de las uniones ANSI". Regreso a lo básico: combinaciones internas »Blog de Eddie Awad Archivado el 19 de noviembre de 2010 en la Wayback Machine.
- ^ Silberschatz, Abraham ; Korth, Hank; Sudarshan, S. (2002). "Sección 4.10.2: Tipos de unión y condiciones". Conceptos del sistema de base de datos (4ª ed.). pag. 166. ISBN 0072283637.
- ^ Unión exterior izquierda de Oracle
- ^ Shah 2005 , p. 165
- ^ Adaptado de Pratt 2005 , págs. 115–6
- ^ Yu y Meng 1998 , p. 213
- ^ Índice de unión de mapa de bits de Oracle. URL: http://www.dba-oracle.com/art_builder_bitmap_join_idx.htm
- ^ Índices de unión de Teradata. "Copia archivada" . Archivado desde el original el 16 de diciembre de 2012 . Consultado el 14 de junio de 2012 .CS1 maint: copia archivada como título ( enlace )
- ^ "13.2.9.2 Sintaxis JOIN" . Manual de referencia de MySQL 5.7 . Oracle Corporation . Consultado el 3 de diciembre de 2015 .
Fuentes
- Pratt, Phillip J (2005), A Guide To SQL, séptima edición , Thomson Course Technology, ISBN 978-0-619-21674-0
- Shah, Nilesh (2005) [2002], Sistemas de bases de datos que utilizan Oracle - Una guía simplificada de SQL y PL / SQL Segunda edición ( edición internacional), Pearson Education International, ISBN 0-13-191180-5
- Yu, Clement T .; Meng, Weiyi (1998), Principios del procesamiento de consultas de bases de datos para aplicaciones avanzadas , Morgan Kaufmann, ISBN 978-1-55860-434-6, consultado el 3 de marzo de 2009
enlaces externos
- Específico para productos
- Sybase ASE 15 se une
- Uniones de MySQL 5.7
- Uniones de PostgreSQL 9.3
- Se une en Microsoft SQL Server
- Se une en MaxDB 7.6
- Se une en Oracle 12c R1
- Uniones de Oracle SQL