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 JOIN
s 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 JOIN
s 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 INTERSECT
s 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 SELECT
s:
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
.