Pregunta ¿Cuál es la diferencia entre "INNER JOIN" y "OUTER JOIN"?


¿También cómo? LEFT JOIN, RIGHT JOIN y FULL JOIN encajar?


4014
2017-09-01 22:36


origen


Respuestas:


Suponiendo que se está uniendo a columnas sin duplicados, que es un caso muy común:

  • Una combinación interna de A y B da el resultado de A se cruzan B, es decir, la parte interna de un diagrama de Venn intersección.

  • Una unión externa de A y B da los resultados de una unión B, es decir, las partes externas de una unión de diagrama de Venn.

Ejemplos

Supongamos que tiene dos tablas, con una sola columna cada una, y datos de la siguiente manera:

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 dará 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;
select a.*, b.*  from a,b where a.a = b.b(+);

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

Unión externa derecha

Una combinació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;
select a.*, b.*  from a,b where a.a(+) = b.b;

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

Unión externa completa

Una combinació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 porción B es nula, y viceversa versa.

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

5419
2017-09-01 22:59



También puede considerar el siguiente esquema para diferentes tipos de unión;

visual explanation of joins

Fuente: Visual-Representation-of-SQL-Joins explicado en detalle por C.L. Moffatt


2441
2018-05-16 23:03



yo recomiendo Artículo de blog de Jeff. La mejor descripción que he visto, además de una visualización, por ejemplo:

Unir internamente:

enter image description here

Únase al exterior completo:

enter image description here


591
2017-08-30 11:52



Los diagramas de Venn realmente no lo hacen por mí.

No muestran ninguna distinción entre una combinación cruzada y una combinación interna, por ejemplo, o más generalmente muestran cualquier distinción entre diferentes tipos de predicados de unión o proporcionan un marco para el razonamiento sobre cómo operarán.

No hay sustituto para comprender el procesamiento lógico y es relativamente fácil de entender de todos modos.

  1. Imagina una combinación cruzada.
  2. Evalúa el on cláusula contra todas las filas del paso 1 manteniendo aquellas donde el predicado evalúa true
  3. (Solo para uniones externas) agregue nuevamente en cualquier fila externa que se perdió en el paso 2.

(NB: en la práctica, el optimizador de consultas puede encontrar formas más eficientes de ejecutar la consulta que la descripción puramente lógica anterior, pero el resultado final debe ser el mismo)

Comenzaré con una versión animada de un unión externa completa. Más explicación sigue.

enter image description here


Explicación

Tablas fuente

enter link description here

Primero comienza con un CROSS JOIN (Producto cartesiano AKA). Esto no tiene un ON cláusula y simplemente devuelve cada permutación de filas de las dos tablas.

SELECCIONE A.Colour, B.Colour FROM A CROSS JOIN B

enter link description here

Las uniones internas y externas tienen un predicado de cláusula "ON".

  • Unir internamente. Evalúe la condición en la cláusula "ON" para todas las filas en el resultado de unión cruzada. Si es verdadero, devuelve la fila unida. De lo contrario, deséchelo.
  • Izquierda combinación externa. Igual que la unión interna, para las filas de la tabla izquierda que no coinciden con las salidas de estos valores NULL para las columnas de la tabla derecha.
  • Derecha Unión Exterior. Igual que la unión interna, para las filas de la tabla derecha que no coinciden con las salidas de estos con valores NULOS para las columnas de la tabla izquierda.
  • Únase al exterior completo. Igual que la unión interna, entonces conserve las filas no coincidentes izquierda como en la combinación externa izquierda y las filas que no coinciden a la derecha como en la combinación externa derecha.

Algunos ejemplos

SELECCIONE A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

Lo anterior es el clásico equi join.

Inner Join

Versión animada

enter image description here

SELECCIONE A.Color, B.Color FROM A INNER JOIN B ON A.Color NOT IN ('Verde', 'Azul')

La condición de unión interna no tiene que ser necesariamente una condición de igualdad y no necesita columnas de referencia de ambas (o incluso de ninguna) de las tablas. Evaluar A.Colour NOT IN ('Green','Blue') en cada fila de la cruz se une devoluciones.

inner 2

SELECCIONE A.Colour, B.Colour FROM A INNER JOIN B ON 1 = 1

La condición de unión se evalúa como verdadera para todas las filas en el resultado de combinación cruzada, por lo que es igual a una combinación cruzada. No repetiré la imagen de las 16 filas nuevamente.

SELECCIONE A.Color, B.Color DESDE UN EXTERIOR IZQUIERDO ÚNASE A B. A. Color = B.Color

Las combinaciones externas se evalúan lógicamente del mismo modo que las combinaciones internas, excepto que si una fila de la tabla izquierda (para una combinación izquierda) no se une con ninguna fila de la tabla derecha, se conserva en el resultado con NULL valores para las columnas de la derecha.

LOJ

SELECCIONE A.Color, B.Color DESDE UN EXTERIOR EXTERIOR ÚNASE A B ON A.Color = B.Color DONDE B.Color ES NULO

Esto simplemente restringe el resultado anterior para devolver solo las filas donde B.Colour IS NULL. En este caso particular, estas serán las filas que se conservaron porque no tenían coincidencia en la tabla de la derecha y la consulta devuelve la única fila roja que no coincide en la tabla B. Esto se conoce como anti semi join.

Es importante seleccionar una columna para el IS NULL prueba que no admite nulos o para la cual la condición de unión asegura que cualquier NULL Se excluirán los valores para que este patrón funcione correctamente y evite simplemente recuperar las filas que tienen una NULL valor para esa columna además de las filas no coincidentes.

loj is null

SELECCIONE A.Color, B.Color DESDE UN EXTERIOR DERECHO ÚNASE B SOBRE A.Color = B.Color

Las uniones externas derechas actúan de forma similar a las uniones externas izquierdas, excepto que conservan filas que no coinciden de la tabla derecha y nulas extienden las columnas de la izquierda.

ROJ

SELECCIONA A.Color, B.Color FROM A FULL OUTER ÚNETE B ON A.Color = B.Colour

Las combinaciones externas completas combinan el comportamiento de las combinaciones izquierda y derecha y conservan las filas que no coinciden de las tablas izquierda y derecha.

FOJ

SELECCIONE A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

Ninguna fila en la unión cruzada coincide con el 1=0 predicado. Todas las filas de ambos lados se conservan usando las reglas normales de combinación externa con NULL en las columnas de la tabla en el otro lado.

FOJ 2

SELECCIONE COALESCE (A.Colour, B.Colour) COMO COLOR DE UN OUTER COMPLETO ÚNASE A B ON 1 = 0

Con una modificación menor a la consulta anterior, se podría simular una UNION ALL de las dos tablas.

UNION ALL

SELECCIONE A.Color, B.Color DESDE UN EXTERIOR EXTERIOR ÚNASE B ON A.Color = B.Color DONDE B.Color = 'Verde'

Tenga en cuenta que WHERE cláusula (si está presente) se ejecuta lógicamente después de la unión. Un error común es realizar una combinación externa izquierda y luego incluir una cláusula WHERE con una condición en la tabla correcta que termina excluyendo las filas que no coinciden. Lo anterior termina realizando la unión externa ...

LOJ

... Y luego se ejecuta la cláusula "Dónde". NULL= 'Green' no se evalúa como verdadero, por lo que la fila conservada por la unión externa termina descartada (junto con la azul) convirtiendo efectivamente la unión a una interna.

LOJtoInner 

Si la intención era incluir únicamente las filas de B donde Color es verde y todas las filas de A independientemente de la sintaxis correcta sería

SELECCIONE A.Color, B.Color DE UNA UNIÓN EXTERIOR IZQUIERDA ÚNASE A B. A. Color = B.Color Y B.Color = 'Verde'

enter image description here

SQL Fiddle

Vea estos ejemplos ejecutar en vivo en SQLFiddle.com.


526
2017-12-13 11:58



Lo siguiente fue tomado del artículo "MySQL - LEFT JOIN y RIGHT JOIN, INNER JOIN y OUTER JOIN"por Graham Ellis en su blog Horse's Mouth.

En una base de datos como MySQL, los datos se dividen en una cantidad de tablas que luego se conectan (Joined) juntos por JOIN en SELECT comandos para leer registros de varias tablas. Lee este ejemplo para ver cómo funciona.

Primero, algunos datos de muestra:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

JUNTA REGULAR

Si hacemos un JOIN regular (sin ninguna de las palabras clave INNER, OUTER, LEFT or RIGHT), obtenemos todos los registros que coinciden de la manera adecuada en las dos tablas, y los registros en ambas tablas entrantes que no coinciden no se informan :

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

LEFT JOIN

Si hacemos una unión IZQUIERDA, obtenemos todos los registros que coinciden de la misma manera y ADEMÁS obtenemos un registro adicional por cada registro no coincidente en la tabla izquierda de la unión, asegurando (en este ejemplo) que cada persona recibe una mención :

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

DERECHO ÚNASE

Si hacemos un DERECHO A UNIRSE, obtenemos todos los registros que coinciden y ADEMÁS un registro adicional para cada registro no coincidente en la tabla correcta de la unión; en mi ejemplo, eso significa que cada propiedad recibe una mención incluso si no lo hacemos tener detalles del vendedor:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

Un INNER JOIN hace una unión completa, al igual que el primer ejemplo, y la palabra OUTER se puede agregar después de la palabra IZQUIERDA o DERECHA en los dos últimos ejemplos: se proporciona para compatibilidad con ODBC y no agrega capacidades adicionales.


291
2018-02-14 05:53



Unir internamente

Recupere solo las filas coincidentes, es decir, A intersect B.

Enter image description here

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Izquierda combinación externa

Seleccione todos los registros de la primera tabla y cualquier registro en la segunda tabla que coincide con las teclas unidas

Enter image description here

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Unión externa completa

Seleccione todos los registros de la segunda tabla y cualquier registro en la primera tabla que coincide con las teclas unidas

Enter image description here

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Referencias


114
2018-01-27 12:16



Une se utilizan para combinar los datos de dos tablas, con el resultado de una nueva tabla temporal. Las uniones se realizan en base a algo llamado predicado, que especifica la condición que se utilizará para realizar una unión. La diferencia entre una combinación interna y una combinación externa es que una combinación interna devolverá solo las filas que realmente coincidan en función del predicado de unión. Consideremos la tabla Empleado y Ubicación:

enter image description here

Unir internamente:- La combinación interna crea una nueva tabla de resultados combinando valores de columna de dos tablas (Empleado y Ubicación) basado en el predicado de unión. La consulta compara cada fila de Empleado con cada fila de Ubicación 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 los valores que no son NULL, los valores de columna para cada par coincidente de filas de Empleado y Ubicación se combinan en una fila de resultados. Así es como se verá el SQL para una unión interna:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Ahora, aquí está el resultado de ejecutar ese SQL: enter image description here enter image description here

Unir Exterior: - Una combinación externa no requiere que cada registro en las dos tablas combinadas tenga un registro coincidente. La tabla unida conserva cada registro, incluso si no existe otro registro coincidente. Las uniones externas se subdividen más en uniones externas izquierdas y uniones externas derechas, dependiendo de las filas de la tabla que se retengan (izquierda o derecha).

Izquierda combinación externa:- El resultado de una combinación externa izquierda (o simplemente combinación izquierda) para tablas Empleado y Ubicación siempre contiene todos los registros de la tabla "izquierda" (Empleado), incluso si la condición de unión no encuentra ningún registro coincidente en la tabla "derecha" (Ubicación) Aquí se muestra cómo se vería el SQL para una combinación externa izquierda, usando las tablas de arriba:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Ahora, aquí está el resultado de ejecutar este SQL: enter image description here enter image description here

Conexión exterior derecha: - Una combinación externa derecha (o unión derecha) se asemeja mucho a una combinación externa izquierda, excepto con el tratamiento de las tablas invertidas. Cada fila de la tabla "derecha" (Ubicación) aparecerá en la tabla unida al menos una vez. Si no hay una fila coincidente de la tabla "izquierda" (Empleado) existe, NULL aparecerá en columnas de Empleado para aquellos registros que no tienen coincidencia Ubicación. Así es como se ve el SQL:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Usando las tablas de arriba, podemos mostrar cómo se vería el conjunto resultante de una unión externa derecha:

enter image description hereenter image description here

Uniones exteriores completas: - Full Outer Join o Full Join es retener la información no coincidente al incluir filas no coincidentes en los resultados de una unión, usar una combinación externa completa. Incluye todas las filas de ambas tablas, independientemente de si la otra tabla tiene un valor coincidente. enter image description here

Fuente de imagen

Manual de referencia de MySQL 8.0 - Sintaxis de unión


108
2017-12-18 06:54



En palabras simples:

Un unir internamente recuperar las filas coincidentes solamente.

Considerando que unión externa recupera las filas coincidentes de una tabla y todas las filas de la otra tabla ... el resultado depende de cuál uses:

  • Izquierda: Filas coincidentes en la tabla derecha y todas las filas en la tabla izquierda

  • Derecha: Filas coincidentes en la tabla izquierda y todas las filas en la tabla derecha o

  • Completo: Todas las filas en todas las tablas. No importa si hay una coincidencia o no


101
2018-01-12 11:07



Una combinación interna solo muestra filas si hay un registro coincidente en el otro lado (derecho) de la combinación.

Una combinación externa (izquierda) muestra las filas de cada registro en el lado izquierdo, incluso si no hay filas coincidentes en el otro lado (derecho) de la unión. Si no hay una fila coincidente, las columnas del otro lado (derecho) mostrarán NULLs.


92
2017-09-01 22:38



Las combinaciones internas requieren que exista un registro con una ID relacionada en la tabla unida.

Las uniones externas devolverán registros para el lado izquierdo, incluso si no existe nada para el lado derecho.

Por ejemplo, tiene un orden y una tabla de detalles de pedido. Están relacionados por un "OrderID".

Pedidos

  • Solicitar ID
  • Nombre del cliente

OrderDetails

  • OrderDetailID
  • Solicitar ID
  • Nombre del producto
  • Cantidad
  • Precio

La solicitud

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

solo devolverá pedidos que también tengan algo en la tabla OrderDetails.

Si lo cambias a OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

luego devolverá registros de la tabla Pedidos incluso si no tienen registros OrderDetails.

Puede usar esto para encontrar pedidos que no tienen ningún OrderDetails que indique una posible orden huérfana agregando una cláusula where como WHERE OrderDetails.OrderID IS NULL.


67
2017-09-01 22:47