inner join vs outer join
Unión interna frente a unión externa: prepárese para explorar las diferencias exactas entre la unión interna y externa
Antes de explorar las diferencias entre Inner Join y Outer Join, veamos primero qué es SQL JOIN.
Una cláusula de combinación se utiliza para combinar registros o para manipular los registros de dos o más tablas mediante una condición de combinación. La condición de unión indica cómo se comparan las columnas de cada tabla entre sí.
La unión se basa en una columna relacionada entre estas tablas. Un ejemplo más común es la unión entre dos tablas a través de la columna de clave principal y la columna de clave externa.
Supongamos que tenemos una tabla que contiene el salario del empleado y hay otra tabla que contiene los detalles del empleado.
En este caso, habrá una columna común como ID de empleado que unirá estas dos tablas. Esta columna de Id. De empleado sería la clave principal de las tablas de detalles de los empleados y la clave externa en la tabla de salarios de los empleados.
Es muy importante tener una clave común entre las dos entidades. Puede pensar en una tabla como una entidad y la clave como un enlace común entre las dos tablas que se utiliza para la operación de unión.
preguntas de la entrevista de ajuste de rendimiento de Oracle 11g
Básicamente, hay dos tipos de combinación en SQL, es decir, Unión interna y unión externa . La unión externa se subdivide en tres tipos, es decir, Unión exterior izquierda, unión exterior derecha y unión exterior completa.
En este artículo veremos la diferencia entre Unión interna y unión externa en detalle. Mantendremos las uniones cruzadas y las uniones desiguales fuera del alcance de este artículo.
Lo que vas a aprender:
- ¿Qué es Inner Join?
- ¿Qué es Outer Join?
- Diferencia entre unión interna y externa
- Rendimiento
- Unión interna y externa de MS Access
- Unión izquierda vs Unión externa izquierda
- Unión externa izquierda vs Unión externa derecha
- Diferencia entre unión interna y unión externa en formato tabular
- Unión interna y externa vs Unión
- Conclusión
- Lectura recomendada
¿Qué es Inner Join?
Una combinación interna devuelve solo las filas que tienen valores coincidentes en ambas tablas (aquí estamos considerando que la combinación se realiza entre las dos tablas).
¿Qué es Outer Join?
La unión externa incluye las filas coincidentes, así como algunas de las filas no coincidentes entre las dos tablas. Una combinación externa básicamente difiere de la combinación interna en cómo maneja la condición de coincidencia falsa.
Hay 3 tipos de unión externa:
- Izquierda combinación externa : Devuelve todas las filas de la tabla IZQUIERDA y los registros coincidentes entre ambas tablas.
- Unión externa derecha : Devuelve todas las filas de la tabla DERECHA y registros coincidentes entre ambas tablas.
- Unión externa completa : Combina el resultado de la unión exterior izquierda y la unión exterior derecha.
Diferencia entre unión interna y externa
[imagen fuente ]
Como se muestra en el diagrama anterior, hay dos entidades, es decir, la tabla 1 y la tabla 2, y ambas tablas comparten algunos datos comunes.
Una combinación interna devolverá el área común entre estas tablas (el área sombreada en verde en el diagrama anterior), es decir, todos los registros que son comunes entre la tabla 1 y la tabla 2.
Una unión externa izquierda devolverá todas las filas de la tabla 1 y solo aquellas filas de la tabla 2 que son comunes a la tabla 1 también. Una unión externa derecha hará todo lo contrario. Dará todos los registros de la tabla 2 y solo los registros coincidentes correspondientes de la tabla 1.
Además, una unión externa completa nos dará todos los registros de la tabla 1 y la tabla 2.
Comencemos con un ejemplo para aclarar esto.
Supongamos que tenemos dos mesas: EmpDetails y EmpSalary .
Tabla EmpDetails:
ID de empleado | Nombre de empleado |
7 | Lirio |
1 | John |
2 | Samantha |
3 | Ninguno |
4 | Sedoso |
5 | RAM |
6 | Arpit |
8 | Sita |
9 | Farah |
10 | alemán |
Tabla EmpSalary:
ID de empleado | Nombre de empleado | EmpleadoSalario |
---|---|---|
11 | Rosa | 90000 |
1 | John | 50000 |
2 | Samantha | 120000 |
3 | Ninguno | 75000 |
4 | Sedoso | 25000 |
5 | RAM | 150000 |
6 | Arpit | 80000 |
12 | Sakshi | 45000 |
13 | Jacobo | 250000 |
Hagamos un Inner Join en estas dos tablas y observemos el resultado:
Consulta:
|_+_|Resultado:
ID de empleado | Nombre de empleado | EmpleadoSalario |
---|---|---|
7 | Lirio | NULO |
1 | John | 50000 |
2 | Samantha | 120000 |
3 | Ninguno | 75000 |
4 | Sedoso | 25000 |
5 | RAM | 150000 |
6 | Arpit | 80000 |
En el conjunto de resultados anterior, puede ver que Inner Join ha devuelto los primeros 6 registros que estaban presentes en EmpDetails y EmpSalary con una clave coincidente, es decir, EmployeeID. Por lo tanto, si A y B son dos entidades, la combinación interna devolverá el conjunto de resultados que será igual a 'Registros en A y B', según la clave coincidente.
Veamos ahora qué hará una unión externa izquierda.
Consulta:
|_+_|Resultado:
ID de empleado | Nombre de empleado | EmpleadoSalario |
---|---|---|
1 | John | 50000 |
2 | Samantha | 120000 |
3 | Ninguno | 75000 |
4 | Sedoso | 25000 |
5 | RAM | 150000 |
6 | Arpit | 80000 |
8 | Sita | NULO |
9 | Farah | NULO |
10 | alemán | NULO |
En el conjunto de resultados anterior, puede ver que la unión externa izquierda ha devuelto los 10 registros de la tabla IZQUIERDA, es decir, la tabla EmpDetails y, como los primeros 6 registros coinciden, ha devuelto el salario del empleado para estos registros coincidentes.
Como el resto de los registros no tienen una clave coincidente en la tabla DERECHA, es decir, la tabla EmpSalary, ha devuelto NULL correspondiente a esos. Dado que Lily, Sita, Farah y Jerry no tienen una identificación de empleado coincidente en la tabla EmpSalary, su salario aparece como NULL en el conjunto de resultados.
Entonces, si A y B son dos entidades, la combinación externa izquierda devolverá el conjunto de resultados que será igual a 'Registros en A NO B', según la clave coincidente.
Ahora observemos lo que hace la unión externa derecha.
Consulta:
|_+_|Resultado:
ID de empleado | Nombre de empleado | EmpleadoSalario |
---|---|---|
NULO | NULO | 90000 |
1 | John | 50000 |
2 | Samantha | 120000 |
3 | Ninguno | 75000 |
4 | Sedoso | 25000 |
5 | RAM | 150000 |
6 | Arpit | 80000 |
NULO | NULO | 250000 |
NULO | NULO | 250000 |
En el conjunto de resultados anterior, puede ver que la unión externa derecha ha hecho exactamente lo contrario de la unión izquierda. Ha devuelto todos los salarios de la tabla de la derecha, es decir, la tabla EmpSalary.
Pero, como Rose, Sakshi y Jack no tienen una ID de empleado coincidente en la tabla de la izquierda, es decir, la tabla EmpDetails, tenemos su ID de empleado y EmployeeName como NULL de la tabla de la izquierda.
Entonces, si A y B son dos entidades, entonces la unión externa derecha devolverá el conjunto de resultados que será igual a 'Registros en B NO A', según la clave coincidente.
Veamos también cuál será el conjunto de resultados si estamos haciendo una operación de selección en todas las columnas de ambas tablas.
Consulta:
|_+_|Resultado:
ID de empleado | Nombre de empleado | ID de empleado | Nombre de empleado | EmpleadoSalario |
---|---|---|---|---|
NULO | NULO | 11 | Rosa | 90000 |
1 | John | 1 | John | 50000 |
2 | Samantha | 2 | Samantha | 120000 |
3 | Ninguno | 3 | Ninguno | 75000 |
4 | Sedoso | 4 | Sedoso | 25000 |
5 | RAM | 5 | RAM | 150000 |
6 | Arpit | 6 | Arpit | 80000 |
NULO | NULO | 12 | Sakshi | 250000 |
NULO | NULO | 13 | Jacobo | 250000 |
Ahora, pasemos a la unión completa.
Se realiza una combinación externa completa cuando queremos todos los datos de ambas tablas, independientemente de si hay una coincidencia o no. Por lo tanto, si quiero a todos los empleados incluso si no encuentro una clave coincidente, ejecutaré una consulta como se muestra a continuación.
Consulta:
|_+_|Resultado:
ID de empleado | Nombre de empleado | ID de empleado | Nombre de empleado | EmpleadoSalario |
---|---|---|---|---|
7 | Lirio | NULO | NULO | NULO |
1 | John | 1 | John | 50000 |
2 | Samantha | 2 | Samantha | 120000 |
3 | Ninguno | 3 | Ninguno | 75000 |
4 | Sedoso | 4 | Sedoso | 25000 |
5 | RAM | 5 | RAM | 150000 |
6 | Arpit | 6 | Arpit | 80000 |
8 | Sita | NULO | NULO | NULO |
9 | Farah | NULO | NULO | NULO |
10 | alemán | NULO | NULO | NULO |
NULO | NULO | 11 | Rosa | 90000 |
NULO | NULO | 12 | Sakshi | 250000 |
NULO | NULO | 13 | Jacobo | 250000 |
Puede ver en el conjunto de resultados anterior que, dado que los primeros seis registros coinciden en ambas tablas, tenemos todos los datos sin ningún NULL. Los siguientes cuatro registros existen en la tabla de la izquierda pero no en la tabla de la derecha, por lo que los datos correspondientes en la tabla de la derecha son NULL.
Los últimos tres registros existen en la tabla de la derecha y no en la tabla de la izquierda, por lo tanto, tenemos NULL en los datos correspondientes de la tabla de la izquierda. Entonces, si A y B son dos entidades, la combinación externa completa devolverá el conjunto de resultados que será igual a 'Registros en A y B', independientemente de la clave coincidente.
En teoría, es una combinación de Unión a la izquierda y Unión a la derecha.
Rendimiento
Comparemos una combinación interna con una combinación externa izquierda en el servidor SQL. Hablando de la velocidad de operación, una combinación externa izquierda obviamente no es más rápida que una combinación interna.
Según la definición, una unión externa, ya sea la izquierda o la derecha, tiene que realizar todo el trabajo de una unión interna junto con el trabajo adicional nulo, extendiendo los resultados. Se espera que una combinación externa devuelva una mayor cantidad de registros, lo que aumenta aún más su tiempo de ejecución total solo por el conjunto de resultados más grande.
Por tanto, una combinación externa es más lenta que una interna.
Además, puede haber algunas situaciones específicas en las que la combinación izquierda sea más rápida que una combinación interna, pero no podemos continuar reemplazándolas entre sí, ya que una combinación externa izquierda no es funcionalmente equivalente a una combinación interna.
Analicemos un caso en el que la unión izquierda podría ser más rápida que la unión interna. Si las tablas involucradas en la operación de combinación son demasiado pequeñas, digamos que tienen menos de 10 registros y las tablas no poseen índices suficientes para cubrir la consulta; en ese caso, la combinación izquierda es generalmente más rápida que la combinación interna.
unión completa vs unión externa completa
Creemos las dos tablas siguientes y hagamos una INNER JOIN y una IZQUIERDA OUTER JOIN entre ellas como ejemplo:
|_+_|IDENTIFICACIÓN | Nombre | IDENTIFICACIÓN | Nombre | |
---|---|---|---|---|
A continuación se muestra la visualización de una combinación interna: | A continuación se muestra la visualización de una combinación externa | |||
1 | 1 | A | 1 | A |
2 | 2 | B | 2 | B |
3 | 3 | C | 3 | C |
4 | 4 | D | 4 | D |
5 | 5 | ES | 5 | ES |
IDENTIFICACIÓN | Nombre | IDENTIFICACIÓN | Nombre | |
---|---|---|---|---|
1 | 1 | A | 1 | A |
2 | 2 | B | 2 | B |
3 | 3 | C | 3 | C |
4 | 4 | D | 4 | D |
5 | 5 | ES | 5 | ES |
Como puede ver arriba, ambas consultas han devuelto el mismo conjunto de resultados. En este caso, si ve el plan de ejecución de ambas consultas, encontrará que la combinación interna ha costado más que la combinación externa. Esto se debe a que, para una combinación interna, el servidor SQL hace una coincidencia de hash, mientras que hace bucles anidados para la combinación izquierda.
Una coincidencia de hash es normalmente más rápida que los bucles anidados. Pero, en este caso, como el número de filas es tan pequeño y no hay índice para usar (ya que estamos uniendo en la columna de nombre), la operación hash ha resultado una consulta de unión interna más cara.
Sin embargo, si cambia la clave coincidente en la consulta de combinación de Nombre a ID y si hay una gran cantidad de filas en la tabla, entonces encontrará que la combinación interna será más rápida que la combinación externa izquierda.
Unión interna y externa de MS Access
Cuando usa múltiples fuentes de datos en la consulta de MS Access, aplica JOINs para controlar los registros que desea ver, dependiendo de cómo las fuentes de datos estén vinculadas entre sí.
En una combinación interna, solo los relacionados de ambas tablas se combinan en un único conjunto de resultados. Esta es una combinación predeterminada en Access y también la que se usa con más frecuencia. Si aplica una combinación pero no especifica explícitamente qué tipo de combinación es, Access asume que es una combinación interna.
En las combinaciones externas, todos los datos relacionados de ambas tablas se combinan correctamente, más todas las filas restantes de una tabla. En las combinaciones externas completas, todos los datos se combinan siempre que sea posible.
Unión izquierda vs Unión externa izquierda
En SQL Server, la palabra clave externa es opcional cuando aplica la combinación externa izquierda. Por lo tanto, no hay ninguna diferencia si escribe 'LEFT OUTER JOIN' o 'LEFT JOIN', ya que ambos le darán el mismo resultado.
A LEFT JOIN B es una sintaxis equivalente a A LEFT OUTER JOIN B.
A continuación se muestra la lista de sintaxis equivalentes en el servidor SQL:
[imagen fuente ]
Unión externa izquierda vs Unión externa derecha
Ya hemos visto esta diferencia en este artículo. Puede consultar las consultas de Unión externa izquierda y Unión externa derecha y el conjunto de resultados para ver la diferencia.
La principal diferencia entre Left Join y Right Join radica en la inclusión de filas no coincidentes. La combinación externa izquierda incluye las filas no coincidentes de la tabla que está a la izquierda de la cláusula de combinación, mientras que una combinación externa derecha incluye las filas no coincidentes de la tabla que está a la derecha de la cláusula de combinación.
La gente pregunta cuál es mejor usar, es decir, unión izquierda o unión derecha. Básicamente, son el mismo tipo de operaciones excepto con sus argumentos invertidos. Por lo tanto, cuando pregunta qué combinación usar, en realidad está preguntando si debe escribir un una. Es solo una cuestión de preferencia.
Generalmente, las personas prefieren utilizar la combinación izquierda en su consulta SQL. Le sugiero que sea consistente en la forma en que está escribiendo la consulta para evitar cualquier confusión al interpretarla.
Hemos visto todo sobre las uniones internas y todo tipo de uniones externas hasta ahora. Resumamos rápidamente la diferencia entre Inner Join y Outer Join.
Diferencia entre unión interna y unión externa en formato tabular
Unir internamente | Unión externa |
---|---|
Devuelve solo las filas que tienen valores coincidentes en ambas tablas. | Incluye las filas coincidentes, así como algunas de las filas no coincidentes entre las dos tablas. |
En caso de que haya una gran cantidad de filas en las tablas y haya un índice para usar, INNER JOIN es generalmente más rápido que OUTER JOIN. | Generalmente, una OUTER JOIN es más lenta que una INNER JOIN ya que necesita devolver más registros en comparación con INNER JOIN. Sin embargo, puede haber algunos escenarios específicos en los que OUTER JOIN sea más rápido. |
Cuando no se encuentra una coincidencia, no devuelve nada. | Cuando no se encuentra una coincidencia, se coloca un NULL en el valor de columna devuelto. |
Utilice INNER JOIN cuando desee buscar información detallada de cualquier columna específica. | Utilice OUTER JOIN cuando desee mostrar la lista de toda la información en las dos tablas. |
INNER JOIN actúa como un filtro. Debe haber una coincidencia en ambas tablas para que una combinación interna devuelva datos. | Actúan como complementos de datos. |
Existe una notación de combinación implícita para la combinación interna que enlista las tablas para unirlas separadas por comas en la cláusula FROM. Ejemplo: SELECT * FROM producto, categoría DONDE product.CategoryID = category.CategoryID; | No existe una notación de combinación implícita para la combinación externa. |
Unión interna y externa vs Unión
A veces, confundimos Join y Union y esta es también una de las preguntas más frecuentes en Entrevistas SQL . Ya hemos visto la diferencia entre combinación interna y combinación externa. Ahora, veamos en qué se diferencia JOIN de UNION.
UNION coloca una línea de consultas una tras otra, mientras que join crea un producto cartesiano y lo subconjunta. Por lo tanto, UNION y JOIN son operaciones completamente diferentes.
Ejecutemos las dos consultas siguientes en MySQL y veamos su resultado.
Consulta UNION:
|_+_|Resultado:
Bah | |
---|---|
1 | 28 |
2 | 35 |
Consulta JOIN:
|_+_|Resultado:
foo | Bar | |
---|---|---|
1 | 38 | 35 |
Una operación UNION coloca el resultado de dos o más consultas en un único conjunto de resultados. Este conjunto de resultados contiene todos los registros que se devuelven a través de todas las consultas involucradas en UNION. Por tanto, básicamente, una UNIÓN está combinando los dos conjuntos de resultados.
Una operación de unión obtiene datos de dos o más tablas en función de las relaciones lógicas entre estas tablas, es decir, en función de la condición de unión. En la consulta de combinación, los datos de una tabla se utilizan para seleccionar registros de otra tabla. Le permite vincular datos similares que están presentes en diferentes tablas.
Para entenderlo de manera muy simple, puede decir que UNION combina filas de dos tablas, mientras que una combinación combina columnas de dos o más tablas. Por lo tanto, ambos se utilizan para combinar los datos de n tablas, pero la diferencia radica en cómo se combinan los datos.
A continuación se muestran las representaciones pictóricas de UNION y JOIN.
Lo anterior es una representación gráfica de una operación de unión que muestra que cada registro del conjunto de resultados contiene columnas de ambas tablas, es decir, la tabla A y la tabla B. Este resultado se devuelve en función de la condición de unión aplicada en la consulta.
Una combinación es generalmente el resultado de la desnormalización (opuesto a la normalización) y usa la clave externa de una tabla para buscar los valores de la columna empleando la clave primaria en otra tabla.
Lo anterior es una representación gráfica de una operación UNION que muestra que cada registro en el conjunto de resultados es una fila de cualquiera de las dos tablas. Por lo tanto, el resultado de UNION ha combinado las filas de la Tabla A y la Tabla B.
Más lecturas = >> MySQL UNION explicado con ejemplos
Conclusión
En este artículo, hemos visto las principales diferencias entre los Unión interna y unión externa en SQL . También vimos la clasificación de una combinación externa, es decir, combinación izquierda, combinación derecha y combinación completa. Hemos visto cómo funciona cada uno de estos tipos de unión y cómo varían entre sí.
También hicimos algunas comparaciones de rendimiento entre estos tipos de unión. También discutimos en qué se diferencia una unión de una unión.
Leer también = >> Tipos de unión de MySQL
Espero que este artículo le haya ayudado a aclarar sus dudas con respecto a las diferencias entre los distintos tipos de combinación. Estamos seguros de que esto le hará decidir qué tipo de combinación elegir en función del conjunto de resultados deseado.
Lectura recomendada
- Diferencia exacta entre verificación y validación con ejemplos
- Modem Vs Router: conozca la diferencia exacta
- Diferencia entre SQL Vs MySQL Vs SQL Server (con ejemplos)
- Tutorial de Python DateTime con ejemplos
- LAN Vs WAN Vs MAN: Diferencia exacta entre tipos de red
- Cortar comando en Unix con ejemplos
- Sintaxis del comando Unix Cat, opciones con ejemplos
- Uso del cursor en MongoDB con ejemplos