SQL: ¿Cuál es la diferencia entre Inner Join y Outer Join?

Unión interna: selecciona filas que existen en la tabla A SOLO si hay información coincidente en B Y TODA su información coincidente en B.

tabla de empleados como A (bob 1, susan 2, dave 6), tabla de departamentos como B (1 ventas, 1 marketing, 2 contabilidad, 3 HR)

Ejemplo de SQL: seleccione * del empleado A, departamento B donde A.dept_id = B.dept_id

Esto le proporciona a todos los empleados que están asignados a al menos un departamento, y para cada empleado, le dará todos los departamentos a los que está asignado el empleado como filas diferentes, es decir

bob, ventas

bob, marketing

susan, contabilidad

Bob es un 1 para muchos, se asigna a dept_id 1, hay dos filas en B con dept_id 1, por lo tanto, ambas filas se devuelven a Bob. Susan es uno a uno, Dave es un 1 a 0, por lo que no se muestra.

Imagine para la primera fila en A, miramos a través de cada fila en B,

SI la condición de unión satisface (también conocida como A.dept_id = B.dept_id), luego devuelve la fila a la pantalla, así que ahora

Para el ID de departamento 1 de Bob, miramos a través de la fila 1 en B, Ventas, ¿1 = 1? Sí, devuelve esto como una fila. Ahora para la fila 2 en B (marketing), ¿1 = 1? Sí, vuelve. fila 3 y 4 en B, ¿1 = 2 o 1 = 3? no, no regrese, no más filas en B, ahora regrese a A y comience la segunda fila y mire a través de cada fila de B nuevamente y pase a la tercera fila en A y así sucesivamente.

(izquierda o derecha) Unión externa: elija una tabla maestra y una tabla de información complementaria; imagine dos tablas, una a la izquierda, una a la derecha, si elige la de la izquierda como maestra, es una unión externa izquierda , si elige bien, es un derecho exterior.

Las uniones externas izquierda / derecha devuelven todas las filas en la tabla maestra y las filas coincidentes de la tabla suplementaria si existe una, si no hay coincidencia, devuelve nulo en lugar de los campos que se supone que provienen de la tabla suplementaria.

En las tablas A y B anteriores, si selecciona a.employee_name, b.dept_name del empleado A Left Outer Join, departamento B en A.dept_id = B.dept_id.

Obtendría todas las filas devueltas antes, pero esta vez Dave aparecerá con Dave, nulo.

Como no hay dept_id 6, dave obtendrá un valor nulo en lugar de B.dept_name porque B no tenía un dept_id coincidente.

Tenga en cuenta que la tabla maestra tiene una fila Bob, pero aún obtendrá dos filas en el conjunto de resultados porque Bob coincidió en 2 departamentos.

Una combinación siempre consta de dos tablas, que llamaré T1 y T2. La forma más común de unión, la unión interna, es una unión que devuelve tuplas (filas) de ambas tablas, cuando las condiciones de unión entre las tablas son verdaderas, y las filas de t1 no se devuelven si no hay coincidencia en t2 para el condiciones de unión

Una combinación externa siempre devuelve filas de la tabla externa. Tu puedes hacer:
t1 left join t2: esto significa siempre devolver filas de t1 y devolver valores NULL para t2 cuando no hay coincidencia.

t1 unirse a la derecha t2: esto significa siempre devolver filas de t2 y devolver valores NULL para t1 cuando no hay coincidencia.

Si esa descripción no es clara, el resultado real de las consultas debería ser:
Página en sqlfiddle.com

No pude demostrar la unión correcta en ese enlace. Solo pude hacer que sqllite funcione a través de violín sql, y no admite uniones externas correctas.

Una explicación visual de SQL Joins puede ayudar.

Aquí hay un ejemplo para ilustrar:

Supongamos que está escribiendo una solicitud para un hotel. Desea mostrar una pantalla con una lista de todas las habitaciones de hotel y el nombre de la persona que ocupa la habitación, si corresponde.

Una unión interna entre habitaciones y personas mostraría una lista de solo las habitaciones que están ocupadas.

Una unión externa entre habitaciones y personas mostraría una lista de todas las habitaciones, haya o no un ocupante. Los nombres del ocupante también se muestran si hay uno, pero el nombre es NULL si no hay ocupante.

Otros tipos de uniones:

IZQUIERDA EXTERIOR UNIR / IZQUIERDA UNIR

Cuando desee que el resultado de la consulta muestre todas las habitaciones, ya sea que haya un ocupante o no.

DE Habitaciones IZQUIERDA EXTERIOR ÚNETE Reservas
ON Rooms.room_id = Reservations.room_id

La palabra clave OUTER es opcional. LEFT JOIN significa lo mismo que LEFT OUTER JOIN .

DERECHA EXTERIOR UNIR / DERECHA UNIR

Cuando desee mostrar a todos los ocupantes, supongamos que hay algunos que aún no han sido asignados a una habitación.

DESDE Habitaciones DERECHA EXTERIOR ÚNETE Reservas
ON Rooms.room_id = Reservations.room_id

La palabra clave OUTER es opcional. LEFT JOIN significa lo mismo que LEFT OUTER JOIN .

UNIÓN COMPLETA EXTERIOR / UNIÓN COMPLETA

Cuando desee un comportamiento externo: incluya habitaciones que no tengan reservas, y también incluya reservas que no hayan sido asignadas a una habitación.

DESDE Habitaciones COMPLETO EXTERIOR ÚNETE Reservas
ON Rooms.room_id = Reservations.room_id

La palabra clave OUTER es opcional. FULL JOIN significa lo mismo que FULL OUTER JOIN .

CROSS JOIN

Cuando no tiene ninguna condición en la unión, y desea que cada elemento de una tabla coincida con cada elemento de la otra tabla. No hay cláusula ON en este caso. En nuestro ejemplo de hotel, podríamos hacer una unión cruzada entre habitaciones y días de la semana para generar un conjunto completo de combinaciones.

DE Habitaciones CRUZ ÚNETE DaysOfWeek

Equi-join

Su condición de unión se basa en que los valores en una tabla son iguales a los valores en la otra tabla. Este es el tipo más común de unión, especialmente si se trata de referencias de claves externas.

UNIÓN NATURAL

Cuando desee una unión equitativa que haga referencia a todas y cada una de las columnas que existan con el mismo nombre en ambas tablas. No puedo decir que alguna vez haya usado esto.

Es problemático cuando las bases de datos están diseñadas para tener los mismos nombres de columna en varias tablas, pero las columnas significan algo diferente en cada tabla. Por ejemplo, la columna ” id ” se coloca con frecuencia en cada tabla para contener un valor de clave principal, pero, por supuesto, el mismo valor en dos tablas no indicaría una coincidencia.

Semi-unirse

Cuando desea unir dos tablas, pero solo devuelve una fila para la tabla izquierda. Por ejemplo, si queremos conocer las habitaciones que están reservadas pero no necesitamos varias filas para los detalles de cada reserva en esa habitación, haríamos una semiunión. Normalmente vemos esto codificado como EXISTS o ANY predicado.

DE habitaciones
DONDE room_id = CUALQUIERA (SELECCIONE room_id DESDE Reservas)

La diferencia entre la unión interna y la unión externa es la siguiente:

1. La combinación interna es una combinación que combina tablas basadas en tuplas coincidentes, mientras que la combinación externa es una combinación que combina tablas basadas en tuplas coincidentes y no coincidentes.
2. La combinación interna combina filas coincidentes de dos tablas en las que se omiten las filas no coincidentes, mientras que la combinación externa combina filas de dos tablas y las filas no coincidentes se llenan con un valor nulo.
3. La unión interna es como una operación de intersección, mientras que la unión externa es como una operación de unión.
4. La unión interna es de dos tipos, mientras que la unión externa es de tres tipos.
5. La unión interna es más lenta, mientras que la unión externa es más rápida que la unión interna.

Breve forma de verlo:

Outer Join es básicamente un OR exclusivo de subconjuntos. Cuando fusiona los objetos que son únicos entre sí.

Inner Join es básicamente un AND de subconjuntos. Cuando los objetos son propiedad de ambos subconjuntos.

Unión interna : una combinación interna recuperará registros de las claves en ambas tablas.

Unión externa : los registros de una de las tablas siempre se incluyen en el resultado, para la otra, cuando no hay coincidencias, se incluyen valores NULL.

Tipos de unión externa:

Unión externa izquierda: se incluyen todas las filas de la tabla izquierda, las filas no coincidentes de la derecha se reemplazan con valores NULL.

Unión externa derecha: se incluyen todas las filas de la tabla derecha, las filas no coincidentes de la izquierda se reemplazan con valores NULL.

Unión externa completa : se incluyen todas las filas de ambas tablas, los valores NULL llenan filas no coincidentes.

He explicado las uniones SQL aquí en mi blog (DBA Republic). Espero que encuentre esto simple y fácil de seguir:

SQL JOINS explicado

Si tienes mesa A y mesa B

En caso de que la unión interna dé la intersección de las dos tablas “filas que tienen en común”

En caso de que la unión externa dé a la Unión de ambas tablas “todas las filas en la tabla A y todas las filas en la tabla B y en caso de que el valor de cualquiera de las tablas no corresponda en la otra, dará un valor nulo”.

Siempre recuerdo la diferencia entre ellos a través de la foto de abajo

Unión interna: el subconjunto existe tanto en el Conjunto A “como en el” Conjunto B
Unión externa: el subconjunto existe en el Conjunto A “o” Conjunto B