SQL
UNIRSE

Introducción

Sintaxis

• [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } ] JOIN

Observaciones

Unión interna explícita básica

Una unión básica (también llamada "unión interna") consulta datos de dos tablas, con su relación definida en una cláusula de join .

El siguiente ejemplo seleccionará los nombres de los empleados (FName) de la tabla Empleados y el nombre del departamento para el que trabajan (Nombre) de la tabla Departamentos:

SELECT Employees.FName, Departments.Name
FROM   Employees
JOIN   Departments
ON Employees.DepartmentId = Departments.Id

Esto devolvería lo siguiente de la base de datos de ejemplo :

Ingreso implícito

Las combinaciones también se pueden realizar teniendo varias tablas en la cláusula from , separadas por comas , y definiendo la relación entre ellas en la cláusula where . Esta técnica se denomina join implícita (ya que en realidad no contiene una cláusula de join ).

Todos los RDBMS lo admiten, pero la sintaxis suele desaconsejarse. Las razones por las que es una mala idea usar esta sintaxis son:

• Es posible obtener combinaciones cruzadas accidentales que luego devuelven resultados incorrectos, especialmente si tiene muchas combinaciones en la consulta.
• Si pretendía una unión cruzada, no está claro en la sintaxis (escriba CROSS JOIN en su lugar), y es probable que alguien la cambie durante el mantenimiento.

El siguiente ejemplo seleccionará los nombres de los empleados y el nombre de los departamentos para los que trabajan:

SELECT e.FName, d.Name
FROM   Employee e, Departments d
WHERE  e.DeptartmentId = d.Id

Esto devolvería lo siguiente de la base de datos de ejemplo :

Izquierda combinación externa

Una combinación externa izquierda (también conocida como combinación izquierda o externa) es una combinación que garantiza que todas las filas de la tabla izquierda estén representadas; si no existe una fila coincidente de la tabla derecha, sus campos correspondientes son NULL .

El siguiente ejemplo seleccionará todos los departamentos y el primer nombre de los empleados que trabajan en ese departamento. Los departamentos sin empleados aún se devuelven en los resultados, pero tendrán NULL para el nombre del empleado:

SELECT          Departments.Name, Employees.FName
FROM            Departments 
LEFT OUTER JOIN Employees 
ON              Departments.Id = Employees.DepartmentId

Esto devolvería lo siguiente de la base de datos de ejemplo :

Entonces, ¿cómo funciona esto?

Hay dos tablas en la cláusula FROM:

y

Primero se crea un producto cartesiano a partir de las dos tablas que dan una tabla intermedia.
Los registros que cumplen con los criterios de combinación ( Departments.Id = Employees.DepartmentId ) están resaltados en negrita; estos se pasan a la siguiente etapa de la consulta.

Como se trata de una JUNTA EXTERNA IZQUIERDA, todos los registros se devuelven del lado IZQUIERDO de la unión (Departamentos), mientras que a los registros en el lado DERECHO se les asigna un marcador NULO si no coinciden con los criterios de la unión. En la tabla de abajo esto devolverá Tech con NULL

Finalmente, cada expresión utilizada en la cláusula SELECT se evalúa para devolver nuestra tabla final:

Auto unirse

Una tabla se puede unir a sí misma, con diferentes filas que coinciden entre sí por alguna condición. En este caso de uso, se deben usar alias para distinguir las dos apariciones de la tabla.

En el ejemplo a continuación, para cada empleado en la tabla de empleados de la base de datos de ejemplo , se devuelve un registro que contiene el nombre del empleado junto con el nombre correspondiente del gerente del empleado. Como los gerentes también son empleados, la tabla se une consigo misma:

SELECT 
    e.FName AS "Employee", 
    m.FName AS "Manager"
FROM   
    Employees e
JOIN   
    Employees m 
    ON e.ManagerId = m.Id

Esta consulta devolverá los siguientes datos:

Entonces, ¿cómo funciona esto?

La tabla original contiene estos registros:

La primera acción es crear un producto cartesiano de todos los registros en las tablas utilizadas en la cláusula FROM . En este caso, es la tabla Empleados dos veces, por lo que la tabla intermedia se verá así (eliminé todos los campos que no se usaron en este ejemplo):

La siguiente acción es sólo para mantener los registros que cumplen los criterios de unión, por lo que todos los registros donde el alias e mesa ManagerId es igual al alias m tabla Id :

Luego, cada expresión utilizada en la cláusula SELECT se evalúa para devolver esta tabla:

Finalmente, los nombres de columna e.FName y m.FName se reemplazan por sus nombres de columna de alias, asignados con el operador AS :

Unirse a la cruz

La unión cruzada hace un producto cartesiano de los dos miembros. Un producto cartesiano significa que cada fila de una tabla se combina con cada fila de la segunda tabla en la unión. Por ejemplo, si TABLEA tiene 20 filas y TABLEB tiene 20 filas, el resultado sería 20*20 = 400 filas de salida.

Usando la base de datos de ejemplo

SELECT d.Name, e.FName
FROM   Departments d
CROSS JOIN Employees e;

Que devuelve:

Se recomienda escribir una UNIÓN CRUZADA explícita si desea realizar una unión cartesiana, para resaltar que esto es lo que desea.

Uniéndose a una subconsulta

La unión a una subconsulta se usa a menudo cuando se desean obtener datos agregados de una tabla secundaria / de detalles y mostrarlos junto con los registros de la tabla principal o de encabezado. Por ejemplo, es posible que desee obtener un recuento de registros secundarios, un promedio de alguna columna numérica en los registros secundarios o la fila superior o inferior basada en una fecha o campo numérico. Este ejemplo usa alias, lo que se puede argumentar hace que las consultas sean más fáciles de leer cuando tiene varias tablas involucradas. Así es como se ve una unión de subconsulta bastante típica. En este caso, estamos recuperando todas las filas de las órdenes de compra de la tabla principal y recuperando solo la primera fila de cada registro principal de la tabla secundaria PurchaseOrderLineItems.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo, 
  item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN 
     (
       SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id 
       FROM PurchaseOrderLineItems l
       GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
     ) AS item ON item.PurchaseOrderId = po.Id

APLICACIÓN CRUZADA Y UNIÓN LATERAL

Un tipo muy interesante de JOIN es el LATERAL JOIN (nuevo en PostgreSQL 9.3+),
que también se conoce como CROSS APPLY / OUTER APPLY en SQL-Server & Oracle.

La idea básica es que una función con valores de tabla (o una subconsulta en línea) se aplique a cada fila en la que se una.

Esto hace posible, por ejemplo, solo unir la primera entrada coincidente en otra tabla.
La diferencia entre una unión normal y una lateral reside en el hecho de que puede usar una columna que previamente se unió en la subconsulta que "CRUZAS APLICA".

Sintaxis:

PostgreSQL 9.3+

izquierda | derecha | interior ÚNETE LATERAL

Servidor SQL:

CRUZ | OUTER APPLY

INNER JOIN LATERAL es lo mismo que CROSS APPLY
y LEFT JOIN LATERAL es lo mismo que OUTER APPLY

Ejemplo de uso (PostgreSQL 9.3+):

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989


LEFT JOIN LATERAL 
(
    SELECT 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
   LIMIT 1 
) AS FirstOE 

Y para SQL Server.

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989

-- CROSS APPLY -- = INNER JOIN 
OUTER APPLY    -- = LEFT JOIN 
(
    SELECT TOP 1 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
) AS FirstOE 

ÚNETE COMPLETO

Un tipo de JOIN que es menos conocido, es el FULL JOIN.
(Nota: MySQL no admite FULL JOIN a partir de 2016)

Un FULL OUTER JOIN devuelve todas las filas de la tabla izquierda y todas las filas de la tabla derecha.

Si hay filas en la tabla de la izquierda que no tienen coincidencias en la tabla de la derecha, o si hay filas en la tabla de la derecha que no tienen coincidencias en la tabla de la izquierda, esas filas también aparecerán en la lista.

Ejemplo 1 :

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Ejemplo 2:

SELECT 
     COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear 
    ,COALESCE(T_Budget.Value, 0.0) AS RPT_Value 
FROM T_Budget 

FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear 
      ON tYear.Year = T_Budget.Year 

Tenga en cuenta que si está utilizando eliminaciones de software, tendrá que comprobar el estado de eliminación de software de nuevo en la cláusula WHERE (porque FULL JOIN se comporta como una UNION);
Es fácil pasar por alto este pequeño hecho, ya que se coloca AP_SoftDeleteStatus = 1 en la cláusula de unión.

Además, si está realizando una UNIÓN COMPLETA, normalmente deberá permitir NULL en la cláusula WHERE; olvidarse de permitir NULL en un valor tendrá los mismos efectos que una combinación INNER, que es algo que no desea si está realizando una UNIÓN COMPLETA.

Ejemplo:

SELECT 
     T_AccountPlan.AP_UID
    ,T_AccountPlan.AP_Code
    ,T_AccountPlan.AP_Lang_EN
    ,T_BudgetPositions.BUP_Budget
    ,T_BudgetPositions.BUP_UID 
    ,T_BudgetPositions.BUP_Jahr
FROM T_BudgetPositions    

FULL JOIN T_AccountPlan
    ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID 
    AND T_AccountPlan.AP_SoftDeleteStatus = 1 

WHERE (1=1) 
AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL) 
AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL) 

Uniones recursivas

Las combinaciones recursivas se utilizan a menudo para obtener datos padre-hijo. En SQL, se implementan con expresiones de tabla comunes recursivas, por ejemplo:

WITH RECURSIVE MyDescendants AS (
    SELECT Name
    FROM People
    WHERE Name = 'John Doe'

    UNION ALL

    SELECT People.Name
    FROM People
    JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECT * FROM MyDescendants;

Diferencias entre uniones internas / externas

SQL tiene varios tipos de unión para especificar si se incluyen (no) filas coincidentes en el resultado: INNER JOIN , LEFT OUTER JOIN , LEFT OUTER JOIN RIGHT OUTER JOIN , RIGHT OUTER JOIN FULL OUTER JOIN (las palabras clave INNER y OUTER son opcionales). La siguiente figura subraya las diferencias entre estos tipos de uniones: el área azul representa los resultados devueltos por la unión y el área blanca representa los resultados que la unión no devolverá.

Presentación pictórica cruzada de SQL ( referencia ):

Abajo hay ejemplos de esta respuesta.

Por ejemplo, hay dos tablas a continuación:

A    B
-    -
1    3
2    4
3    5
4    6

Tenga en cuenta que (1,2) son exclusivos de A, (3,4) son comunes y (5,6) son exclusivos de B.

Unir internamente

Una combinación interna que utiliza cualquiera de las consultas equivalentes proporciona la intersección de las dos tablas, es decir, las dos filas que tienen en común:

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Izquierda combinación externa

Una combinación externa izquierda proporcionará todas las filas en A, más cualquier fila común en B:

select * from a LEFT OUTER JOIN b on a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Unión externa derecha

De manera similar, una unión externa derecha dará todas las filas en B, más cualquier fila común en A:

select * from a RIGHT OUTER JOIN b on a.a = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Unión externa completa

Una unión externa completa le dará la unión de A y B, es decir, todas las filas en A y todas las filas en B. Si algo en A no tiene un dato correspondiente en B, entonces la parte B es nula, y viceversa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Terminología de JOIN: Interno, Exterior, Semi, Anti ...

Digamos que tenemos dos tablas (A y B) y algunas de sus filas coinciden (en relación con la condición de UNIÓN dada, cualquiera que sea en el caso particular):

Podemos usar varios tipos de unión para incluir o excluir filas coincidentes o no coincidentes de cada lado, y nombrar correctamente la unión seleccionando los términos correspondientes del diagrama anterior.

Los siguientes ejemplos utilizan los siguientes datos de prueba:

CREATE TABLE A (
    X varchar(255) PRIMARY KEY
);

CREATE TABLE B (
    Y varchar(255) PRIMARY KEY
);

INSERT INTO A VALUES
    ('Amy'),
    ('John'),
    ('Lisa'),
    ('Marco'),
    ('Phil');

INSERT INTO B VALUES
    ('Lisa'),
    ('Marco'),
    ('Phil'),
    ('Tim'),
    ('Vincent');

Unir internamente

Combina las filas izquierda y derecha que coinciden.

SELECT * FROM A JOIN B ON X = Y;

X      Y
------ -----
Lisa   Lisa
Marco  Marco
Phil   Phil

Izquierda combinación externa

A veces abreviado como "unir a la izquierda". Combina las filas izquierda y derecha que coinciden, e incluye filas izquierdas que no coinciden.

SELECT * FROM A LEFT JOIN B ON X = Y;

X      Y
-----  -----
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil

Unión externa derecha

A veces abreviado como "unir a la derecha". Combina las filas de la izquierda y la derecha que coinciden, e incluye las filas de la derecha que no coinciden.

SELECT * FROM A RIGHT JOIN B ON X = Y;

X      Y
-----  -------
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

Unión externa completa

A veces abreviado como "unión completa". Unión de unión externa izquierda y derecha.

SELECT * FROM A FULL JOIN B ON X = Y;

X      Y
-----  -------
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

Unirse a la izquierda

Incluye las filas de la izquierda que coinciden con las filas de la derecha.

SELECT * FROM A WHERE X IN (SELECT Y FROM B);

X
-----
Lisa
Marco
Phil

Right Semi Join

Incluye las filas de la derecha que coinciden con las filas de la izquierda.

SELECT * FROM B WHERE Y IN (SELECT X FROM A);

Y
-----
Lisa
Marco
Phil

Como puede ver, no hay una sintaxis de IN dedicada para la combinación semi izquierda / derecha; logramos el efecto simplemente cambiando las posiciones de la tabla dentro del texto SQL.

Left Anti Semi Join

Incluye las filas de la izquierda que no coinciden con las filas de la derecha.

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);

X
----
Amy
John

ADVERTENCIA: ¡ Tenga cuidado si está utilizando NOT IN en una columna NULABLE! Más detalles aquí .

Right Anti Semi Join

Incluye las filas de la derecha que no coinciden con las filas de la izquierda.

SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);

Y
-------
Tim
Vincent

Como puede ver, no hay una sintaxis NOT IN dedicada para la combinación semi izquierda / derecha: logramos el efecto simplemente cambiando las posiciones de la tabla dentro del texto SQL.

Cruzar

Un producto cartesiano de todas las izquierdas con todas las filas derechas.

SELECT * FROM A CROSS JOIN B;

X      Y
-----  -------
Amy    Lisa
John   Lisa
Lisa   Lisa
Marco  Lisa
Phil   Lisa
Amy    Marco
John   Marco
Lisa   Marco
Marco  Marco
Phil   Marco
Amy    Phil
John   Phil
Lisa   Phil
Marco  Phil
Phil   Phil
Amy    Tim
John   Tim
Lisa   Tim
Marco  Tim
Phil   Tim
Amy    Vincent
John   Vincent
Lisa   Vincent
Marco  Vincent
Phil   Vincent

La unión cruzada es equivalente a una unión interna con condición de unión que siempre coincide, por lo que la siguiente consulta habría dado el mismo resultado:

SELECT * FROM A JOIN B ON 1 = 1;

Auto-unirse

Esto simplemente denota una tabla que se une consigo misma. Una auto-unión puede ser cualquiera de los tipos de unión mencionados anteriormente. Por ejemplo, esta es una auto-unión interna:

SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);

X     X
----  -----
Amy   John
Amy   Lisa
Amy   Marco
John  Marco
Lisa  Marco
Phil  Marco
Amy   Phil