sql – ¿Cuál es la diferencia entre «INNER JOIN» y «OUTER JOIN»?

Consulte la respuesta de Martin Smith para obtener mejores ejemplos y explicaciones de las diferentes uniones, incluidas y especialmente las diferencias entre FULL OUTER JOIN, RIGHT OUTER JOIN y LEFT OUTER JOIN.

Estos dos cuadros forman una base para la representación de la JOINs abajo:

ÚNETE CRUZADO

SELECT *
  FROM citizen
 CROSS JOIN postalcode

El resultado serán los productos cartesianos de todas las combinaciones. No JOIN condición requerida:

UNIR INTERNAMENTE

INNER JOIN es lo mismo que simplemente: JOIN

SELECT *
  FROM citizen    c
  JOIN postalcode p ON c.postal = p.postal

El resultado serán combinaciones que satisfagan los requisitos JOIN condición:

IZQUIERDA COMBINACIÓN EXTERNA

LEFT OUTER JOIN es lo mismo que LEFT JOIN

SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON c.postal = p.postal

El resultado será todo desde citizen incluso si no hay coincidencias en postalcode. De nuevo un JOIN se requiere condición:

Datos para jugar

Todos los ejemplos se han ejecutado en un Oracle 18c. Están disponibles en dbfiddle.uk que es también de donde provienen las capturas de pantalla de las tablas.

CREATE TABLE citizen (id      NUMBER,
                      name    VARCHAR2(20),
                      postal  NUMBER,  -- <-- could do with a redesign to postalcode.id instead.
                      leader  NUMBER);

CREATE TABLE postalcode (id      NUMBER,
                         postal  NUMBER,
                         city    VARCHAR2(20),
                         area    VARCHAR2(20));

INSERT INTO citizen (id, name, postal, leader)
              SELECT 1, 'Smith', 2200,  null FROM DUAL
        UNION SELECT 2, 'Green', 31006, 1    FROM DUAL
        UNION SELECT 3, 'Jensen', 623,  1    FROM DUAL;

INSERT INTO postalcode (id, postal, city, area)
                 SELECT 1, 2200,     'BigCity',         'Geancy'  FROM DUAL
           UNION SELECT 2, 31006,    'SmallTown',       'Snizkim' FROM DUAL
           UNION SELECT 3, 31006,    'Settlement',      'Moon'    FROM DUAL  -- <-- Uuh-uhh.
           UNION SELECT 4, 78567390, 'LookoutTowerX89', 'Space'   FROM DUAL;

ÚNETE CRUZADO

CROSS JOIN resultando en filas como La idea general /INNER JOIN:

SELECT *
  FROM citizen          c
  CROSS JOIN postalcode p
 WHERE c.postal = p.postal -- < -- The WHERE condition is limiting the resulting rows

Utilizando CROSS JOIN para obtener el resultado de un LEFT OUTER JOIN requiere trucos como agregar un NULL hilera. Está omitido.

UNIR INTERNAMENTE

INNER JOIN se convierte en un producto cartesiano. Es lo mismo que La idea general /CROSS JOIN:

SELECT *
  FROM citizen    c
  JOIN postalcode p ON 1 = 1  -- < -- The ON condition makes it a CROSS JOIN

Aquí es donde la unión interna realmente se puede ver como la unión cruzada con resultados que no coinciden con la condición eliminada. Aquí no se elimina ninguna de las filas resultantes.

Utilizando INNER JOIN para obtener el resultado de un LEFT OUTER JOIN también requiere trucos. Está omitido.

IZQUIERDA COMBINACIÓN EXTERNA

LEFT JOIN da como resultado filas como La idea general /CROSS JOIN:

SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON 1 = 1 -- < -- The ON condition makes it a CROSS JOIN

LEFT JOIN da como resultado filas como La idea general /INNER JOIN:

SELECT *
  FROM citizen         c
  LEFT JOIN postalcode p ON c.postal = p.postal
 WHERE p.postal IS NOT NULL -- < -- removed the row where there's no mathcing result from postalcode

---

Una búsqueda de imágenes en Internet sobre «sql join cross inner external» mostrará una multitud de diagramas de Venn. Solía ​​tener una copia impresa de uno en mi escritorio. Pero hay problemas con la representación.

Los diagramas de Venn son excelentes para la teoría de conjuntos, donde un elemento puede estar en uno o en ambos conjuntos. Pero para las bases de datos, un elemento en un «conjunto» me parece que es una fila en una tabla y, por lo tanto, no está presente en ninguna otra tabla. No existe una fila en varias tablas. Una fila es exclusiva de la mesa.

Las autouniones son un caso de esquina en el que cada elemento es de hecho el mismo en ambos conjuntos. Pero todavía no está libre de ninguno de los problemas a continuación.

El conjunto A representa el conjunto de la izquierda (el citizen mesa) y el conjunto B es el conjunto de la derecha (el postalcode tabla) en la discusión a continuación.

ÚNETE CRUZADO

Todos los elementos de ambos conjuntos coinciden con todos los elementos del otro conjunto, lo que significa que necesitamos A cantidad de cada B elementos y B cantidad de cada A elementos para representar correctamente este producto cartesiano. La teoría de conjuntos no está hecha para múltiples elementos idénticos en un conjunto, por lo que creo que los diagramas de Venn para representarlo adecuadamente son poco prácticos / imposibles. No parece que UNION encaja en absoluto.

Las filas son distintas. los UNION son 7 filas en total. Pero son incompatibles con un SQL conjunto de resultados. Y no es así como CROSS JOIN funciona en absoluto:

Tratando de representarlo así:

… pero ahora parece un INTERSECTION, que ciertamente es no. Además, no hay ningún elemento en el INTERSECTION que en realidad está en cualquiera de los dos conjuntos distintos. Sin embargo, se parece mucho a los resultados de búsqueda similares a esto:

Como referencia, un resultado de búsqueda para CROSS JOINs se puede ver en Tutorialgateway. los INTERSECTION, al igual que este, está vacío.

UNIR INTERNAMENTE

El valor de un elemento depende de la JOIN condición. Es posible representar esto bajo la condición de que cada fila sea única para esa condición. Sentido id=x solo es cierto para uno hilera. Una vez por fila en la mesa A (citizen) coincide con varias filas en la tabla B (postalcode) bajo la JOIN condición, el resultado tiene los mismos problemas que el CROSS JOIN: La fila debe representarse varias veces y la teoría de conjuntos no está hecha para eso. Sin embargo, bajo la condición de unicidad, el diagrama podría funcionar, pero tenga en cuenta que el JOIN condición determina la ubicación de un elemento en el diagrama. Mirando solo los valores de la JOIN condición con el resto de la fila solo para el viaje:

Esta representación se desmorona completamente cuando se usa un INNER JOIN con un ON 1 = 1 condición convirtiéndolo en un CROSS JOIN.

Con unJOIN, las filas son de hecho elementos idénticos en ambas tablas, pero representan las tablas como ambas A y B no es muy adecuado. Por ejemplo, unJOIN condición que hace que un elemento en A coincidir con un diferente elemento en B es ON A.parent = B.child, haciendo el partido de A para B en elementos separados. De los ejemplos que serían un SQL como esto:

SELECT *
  FROM citizen c1
  JOIN citizen c2 ON c1.id = c2.leader

Lo que significa que Smith es el líder de Green y Jensen.

ÚNETE EXTERIOR

Nuevamente, los problemas comienzan cuando una fila tiene varias coincidencias con las filas de la otra tabla. Esto se complica aún más porque el OUTER JOIN puede pensarse como para que coincida con el conjunto vacío. Pero en la teoría de conjuntos, la unión de cualquier conjunto C y un conjunto vacío, siempre es solo C. El conjunto vacío no aporta nada. La representación de este LEFT OUTER JOIN por lo general, solo muestra todos los A para ilustrar que filas en A se seleccionan independientemente de si hay una coincidencia o no de B. Sin embargo, los «elementos coincidentes» tienen los mismos problemas que la ilustración anterior. Dependen de la condición. Y el conjunto vacío parece haberse desviado hacia A:

Cláusula WHERE – tiene sentido

Encontrar todas las filas de un CROSS JOIN con Smith y código postal en la Luna:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name="Smith"
   AND p.area="Moon";

Ahora el diagrama de Venn no se usa para reflejar el JOIN. Es usado solamente Para el WHERE cláusula:

..y eso tiene sentido.

INTERSECARSE

Como explica un INNER JOIN no es realmente un INTERSECT. Sin embargo INTERSECTs se pueden utilizar en los resultados de consultas independientes. Aquí, un diagrama de Venn tiene sentido, porque los elementos de las consultas separadas son de hecho filas que pertenecen a solo uno de los resultados o ambos. Intersect obviamente solo devolverá resultados donde la fila esté presente en ambas consultas. Esta SQL resultará en la misma fila que la anterior WHERE, y el diagrama de Venn también será el mismo:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name="Smith"
INTERSECT
SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area="Moon";

UNIÓN

Un OUTER JOIN no es un UNION. Sin embargo UNION trabajar en las mismas condiciones que INTERSECT, lo que resulta en un retorno de todos los resultados combinando ambos SELECTs:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name="Smith"
UNION
SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE p.area="Moon";

que es equivalente a:

SELECT *
  FROM citizen          c
 CROSS JOIN postalcode  p
 WHERE c.name="Smith"
   OR p.area="Moon";

..y da el resultado:

También aquí tiene sentido un diagrama de Venn:

Cuando no aplica

Un nota IMPORTANTE es que estos solo funcionan cuando la estructura de los resultados de los dos SELECT’s es la misma, lo que permite una comparación o unión. Los resultados de estos dos no permitirán que:

SELECT *
  FROM citizen
 WHERE name="Smith"

SELECT *
  FROM postalcode
 WHERE area="Moon";

..intentando combinar los resultados con UNION da un

ORA-01790: expression must have same datatype as corresponding expression

---

Para mayor interés lea Diga NO a los diagramas de Venn al explicar los JOIN y sql se une como diagrama de venn. Ambos también cubren EXCEPT.