Diferencias entre INNER JOIN y OUTER JOIN en SQL Server: Efecto de los NULLs en las consultas

Los desarrolladores de SQL Server deben entender dos cosas: la diferencia entre JOINs internos y externos y el efecto de los NULLs en estas elecciones. Este consejo aborda ambos temas.

Tomémoslo paso a paso: primero, las diferencias entre JOINs internos y externos, y luego las implicaciones de los NULLs en nuestras consultas. Para examinar la diferencia entre JOINs internos y externos, necesitaremos algunos datos de ejemplo.

Supongamos que tienes dos tablas, T1 y T2, cada una de las cuales contiene una columna llamada TestJoin. La columna contiene los valores 1, 2 y 3 en la tabla T1, mientras que la columna contiene NULL, 2 y 3 en la tabla T2. Si haces un JOIN interno de estas tablas en la columna TestJoin, obtendrás dos filas, ya que no puedes unir el valor 1 al NULL. Un JOIN externo es lo opuesto. Por ejemplo, lo siguiente solo devuelve aquellas filas en las que los clientes hayan realizado pedidos.

SELECT CustomerID, OrderID FROM Customers Inner Join Orders
On Customers.CustomerID = Orders.CustomerID

(En muchos negocios, esto puede ser válido; otras empresas pueden definir un cliente como aquel que ha realizado al menos un pedido. Esa es una regla de negocio que debería reflejarse en la definición de la base de datos, no en la capa intermedia, en mi opinión, aunque algunos podrían diferir).

Cómo garantizar la validación local de los clientes en un dominio multi-sitio

Un JOIN externo se vería así:

SELECT CustomerID, OrderID FROM Customers LEFT OUTER JOIN Orders
On Orders.CustomerID = Customers.CustomerID

lo cual daría como resultado una lista de todos los clientes, independientemente de si han realizado pedidos o no.

Ejecuta el script simple en el Listing A para crear algunas tablas de prueba.

Aquí tienes el SQL para comparar los JOINs internos y externos:

SELECT InnerOuter.T1.T1ID, InnerOuter.T1.Name AS Name1,
       InnerOuter.T2.T2ID, InnerOuter.T2.Name AS Name2
FROM InnerOuter.T1 LEFT OUTER JOIN
       InnerOuter.T2 ON InnerOuter.T1.T1ID = InnerOuter.T2.T1ID

Cuando ejecutes ambas consultas, confirmarás que el JOIN interno devuelve dos filas, mientras que el JOIN externo devuelve tres. Este principio se mantiene incluso si agregas una tercera tabla, como se puede ver en Listing B.

Si unes la tercera tabla en las declaraciones SELECT, obtendrás los mismos resultados: dos filas del JOIN interno y tres del JOIN externo. Ver Listado C.

Cuál es la mejor opción de protocolo de enrutamiento para una red empresarial

Ten en cuenta el efecto de los NULLs en las funciones de agregación. Para observar este efecto, agrega una columna de dinero con valor NULL a T2 llamada Amount. Agrega algunas filas más a T2 de la siguiente manera:

T2ID  T1ID  Name  Amount
1     1     T2Text1     NULL
2     NULL  T2Text2     NULL
3     3     T2Text3     120.0000
4     1     T2Text4     123.0000
5     1     T2Text5     234.0000
6     3     T2Text6     345.0000
NULL  NULL  NULL  NULL

Ahora ejecuta esta consulta:

SELECT InnerOuter.T1.T1ID,
      Sum(InnerOuter.T2.Amount) AS TotalAmount,
      Count(InnerOuter.T2.T1ID) AS NumberOfRows,
      Count(InnerOuter.T2.Amount) AS NumberOfAmounts,
      Avg(InnerOuter.T2.Amount) AS AverageAmount
FROM InnerOuter.T1 LEFT OUTER JOIN
     InnerOuter.T2 ON InnerOuter.T1.T1ID = InnerOuter.T2.T1ID
GROUP BY InnerOuter.T1.T1ID

Los datos anteriores producen este conjunto de resultados:

1     357.00      3     2     178.50
2     NULL 0     0     NULL
3     465.00      2     2     232.50

La primera fila devuelta indica que si cuentas la columna T1ID, obtienes el número correcto de filas, pero si cuentas los Amounts, obtienes el número de valores no nulos. Ten en cuenta que los promedios reflejan el número de valores no nulos, no el número de filas.

Podrías pensar que estos ejemplos son artificiales, ya que dado las relaciones entre las tablas, parecería poco probable que las columnas de clave externa permitan NULLs. Sin embargo, veo este tipo de cosas todo el tiempo en las bases de datos. RR.HH. contrata a un nuevo empleado pero todavía no lo asigna a un departamento. O, se agrega un nuevo cliente a una base de datos antes de asignarle un representante de ventas.

Hay varias formas de solucionar la ausencia de esta información. En mi opinión, el peor enfoque es agregar lo que se conoce coloquialmente como la fila 0, en la que se agrega una fila "falsa" a cada tabla de búsqueda, y se utiliza este valor cero cuando una clave externa es desconocida, por lo que las claves externas nunca serán NULL. Este enfoque tiene dos problemas significativos: (1) Falsifica conceptualmente la realidad al introducir un nuevo tipo de datos en la tabla, cuando la teoría relacional insiste en que una tabla contiene exactamente un tipo de datos; (2) Complica las consultas porque siempre tendrás que eliminar esas filas de las consultas estándar.

Los 8 pasos para solucionar problemas de red y sistemas

¿Te perdiste un consejo?

Consulta el archivo de SQL Server y ponte al día con las ediciones más recientes de la columna de Arthur Fuller.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Redes, allí encontraras muchos artículos similares a Diferencias entre INNER JOIN y OUTER JOIN en SQL Server: Efecto de los NULLs en las consultas , tenemos lo ultimo en tecnología 2023.