¿Qué es un LEFT OUTER JOIN en SQL? Una Explicación con 4 Ejemplos

El artículo de hoy discutirá el LEFT OUTER JOIN en SQL. Veremos varios ejemplos del uso del LEFT OUTER JOIN y lo compararemos con el INNER JOIN.

JOIN es una función de SQL que le permite combinar datos de dos o más tablas. La naturaleza de las bases de datos relacionales hace que JOIN sea una de las características más utilizadas en SQL. ¿Por qué? En la práctica, es muy raro tener todos los datos necesarios en una sola tabla. Incluso las bases de datos más primitivas constan de al menos dos tablas.

Existen muchos tipos diferentes de JOINs. Aquí nos centraremos en LEFT OUTER JOIN. Para obtener un resumen completo de los diferentes tipos de JOINs, pruebe nuestro curso interactivo Tipos de JOIN en SQL interactivo. Además de los conceptos básicos, explica la unión de varias tablas y el uso de uniones equitativas y no equitativas. Realizará más de 90 ejercicios prácticos, todos ellos basados en ejemplos reales.

Vamos a introducir LEFT OUTER JOIN; luego lo haremos más interesante con ejemplos prácticos. Si necesita refrescar algunos conceptos a medida que avanzamos, recuerde nuestra hoja de trucos de SQL JOIN.

¿Qué es un LEFT OUTER JOIN?

El JOIN más común es INNER JOIN. Es un tipo de unión que devuelve sólo las filas coincidentes de ambas tablas unidas. Existen otros tipos de JOIN que pueden devolver filas de una tabla unida incluso si la fila no tiene una fila coincidente en la otra tabla. Estos tipos de JOINs se denominan uniones externas.

LEFT JOIN es un tipo de unión externa que devuelve todas las filas de la tabla izquierda y las filas coincidentes de la tabla derecha.

¿Cuál es la diferencia entre LEFT OUTER JOIN y LEFT JOIN?

Respuesta corta: No hay diferencia.

Respuesta larga: El nombre completo de este tipo de unión es LEFT OUTER JOIN. Probablemente haya visto ejemplos de código SQL en los que las tablas se unen sólo con LEFT JOIN. Esto se debe a que SQL acepta tanto LEFT OUTER JOIN como LEFT JOIN.

Como LEFT JOIN es más corto, se utiliza más a menudo. Cuando lo veas, significa simplemente LEFT OUTER JOIN. El 'Outer' está implícito, ya que no hay otra left join que una left outer join.

Sintaxis de LEFT [OUTER] JOIN

La sintaxis de LEFT JOIN sigue la sintaxis estándar de JOIN:

1. Haga referencia a la primera tabla en FROM.
2. Utilice la palabra clave LEFT JOIN para hacer referencia a la segunda tabla.
3. Utilice la palabra clave ON para especificar la condición de unión.

En otras palabras, la sintaxis es:

SELECT …
FROM table_1
LEFT JOIN table_2
ON table_1.column = table_2.column;

¿Cómo funciona LEFT OUTER JOIN? En el ejemplo genérico anterior, la consulta hará referencia a table_1 y la unirá por la izquierda con table_2. Primero devolverá todas las filas de table_1, sin importar la condición de unión. ¿Por qué? Porque esta es la naturaleza de LEFT JOIN: devuelve todas las filas de la tabla de la izquierda (es decir, la primera).

A continuación, la consulta tendrá en cuenta la condición de unión: en este caso, si un valor de la columna de table_2 coincide con un valor de la columna de table_1. LEFT JOIN sólo devolverá los valores de la tabla de la derecha (es decir, la segunda tabla, o tabla_2) en los que se cumpla la condición de unión. Si no hay valores en la tabla correcta, los valores devueltos serán NULL. Puede ver un ejemplo visual de esto en nuestro artículo sobre cómo funciona el LEFT JOIN.

Cuando las tablas están unidas, no hace falta decir que puede elegir cualquier columna de ambas tablas en SELECT.

Ahora que ya conoces la sintaxis, sólo te queda ponerla en práctica.

4 Ejemplos de LEFT OUTER JOIN

El Conjunto de Datos para los Ejemplos 1-3

Primero, presentemos el conjunto de datos.

La primera tabla, departmentstiene los siguientes datos:

Puede obtener la consulta para crear la tabla aquí.

La segunda tabla es employeesy puede crearla utilizando esta consulta. La tabla tiene los siguientes datos:

Dos empleados tienen un valor NULL en la columna department_id. Estos son nuevos registros de empleados que aún no han sido actualizados con el departamento.

Ejemplo 1: Buscar todos los empleados y sus departamentos

Utilicemos las tablas anteriores para listar todos los empleados y sus departamentos.

Este es un artículo sobre LEFT OUTER JOIN, así que no hay sorpresa: usaremos exactamente esa unión para resolver este problema. Esta es la consulta:

SELECT e.id,
	 e.first_name,
	 e.last_name,
	 d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.id
ORDER BY e.id;

Los datos resultantes que necesitamos son los números de identificación, los nombres y los departamentos de los empleados. Por eso estas columnas están en SELECT.

Para obtener todas estas columnas, necesitamos acceder a los datos de ambas tablas. Veamos cómo se hace.

En primer lugar, la tabla employees en FROM. ¿Por qué esta tabla y no la otra? Recuerda: La tabla a la que se hace referencia primero importa en LEFT JOIN. ¿Por qué? Porque esa tabla es la tabla de la izquierda, y LEFT JOIN devolverá todas las filas de esa tabla pase lo que pase.

Hacemos referencia a la tabla employees porque necesitamos listar todos los empleados de esa tabla. A continuación, hacemos referencia a la tabla departments. Unimos ambas tablas con la condición de que la columna department_id de la tabla employees sea la misma que la columna id de la tabla departments. Estas dos columnas son dimensiones compartidas (la clave principal y la clave externa) entre estas dos tablas, por lo que son ideales para utilizarlas en la condición de unión.

Para obtener una salida más legible, ordenamos los resultados por ID de empleado:

La salida enumera todos los empleados, los 15. También muestra sus departamentos, que son los más importantes. También muestra sus departamentos, que son "Contabilidad" y "Ventas". Observará que algunos empleados tienen valores NULL en la columna department_name. Dos de ellos los mencionamos antes: nuevos empleados sin un ID de departamento actualizado en la tabla. Estos tenían valores NULL en la tabla employees y están marcados en azul.

Hay, sin embargo, otros empleados -marcados en verde- con valores NULL. Este es el resultado de LEFT JOIN. Todos estos empleados tienen un ID de departamento de 4, pero la tabla departments no contiene este valor. Parece que la tabla departments también podría estar pidiendo una actualización. ¡Una base de datos realmente cutre para una empresa ficticia tan respetable!

Recuerda: Los valores de la tabla izquierda que no se encuentren en la tabla derecha se mostrarán como NULL.

¿Y si utilizamos un INNER JOIN?

Volvamos a escribir la consulta anterior, esta vez con un INNER JOIN (normalmente abreviado como JOIN) en lugar de LEFT JOIN:

SELECT e.id,
	 e.first_name,
	 e.last_name,
	 d.department_name
FROM departments d
JOIN employees e
ON e.department_id = d.id
ORDER BY e.id;

La sintaxis es exactamente la misma; sólo hemos utilizado un tipo de unión diferente. Veamos el resultado:

Faltan algunos empleados; aquí sólo hay nueve. Una inspección más detallada muestra que los empleados con ID 3, 4, 7, 9, 11 y 12 no están incluidos en el resultado. ¿Por qué? Si vuelve atrás, verá que los empleados que faltan son los que tienen NULLs en department_name en la salida LEFT OUTER JOIN.

Recuerde que INNER JOIN sólo devolverá filas coincidentes de ambas tablas, es decir, sólo aquellos empleados que tengan un ID de departamento que se encuentre en ambas tablas.

Así que en esta situación, en la que queríamos todos los empleados y sus departamentos, LEFT JOIN es la opción correcta. Ahora también vemos los empleados sin el departamento, y podemos decir que nuestra base de datos necesita una actualización.

Ejemplo 2. Listar todos los departamentos y sus empleados Listar todos los departamentos y sus empleados

Para obtener el resultado deseado, tenemos que cambiar el orden de las tablas en LEFT JOIN.

Esta es la consulta:

SELECT d.id,
	 d.department_name,
	 e.first_name,
	 e.last_name
FROM  departments d
LEFT JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

Cambiamos el orden de las columnas en SELECT para presentar mejor el resultado. La mayoría de las columnas son las mismas que antes. Esta vez, estamos seleccionando el ID del departamento de la tabla departmentsy no el ID del empleado.

Ahora, la tabla departments es nuestra tabla izquierda. Esto es porque queremos mostrar todos los departamentos de esa tabla, aparezcan o no en la tabla employees - nuestra tabla derecha.

La condición ON permanece igual; sólo hemos invertido el orden de las columnas. Realmente no importa; podría haber permanecido en el mismo orden. Esto es sólo por razones estéticas, ya que es más fácil leer la condición cuando sigue el orden de las tablas en LEFT JOIN.

Además, ordenamos la salida por ID de departamento.

Este es el resultado:

Esta salida muestra sólo nueve empleados, frente a los 15 del ejemplo anterior. Todos los empleados con valores NULL en la salida del último ejemplo no aparecen en esta salida.

La razón es, de nuevo, LEFT JOIN. Como hicimos departments nuestra tabla izquierda, listó todos los departamentos y sus empleados. Todos los demás empleados no están aquí. ¿Por qué? O bien tienen el ID de departamento 4, que no aparece en la tabla departmentso son empleados nuevos (¿los recuerda?) con NULL como ID de departamento.

El único NULLs en esta salida aparece en la última fila. Es el departamento "Cumplimiento", que no tiene ningún empleado asignado. En otras palabras, no hay empleados con el valor 5 en la columna department_id de la employees tabla.

Recuerde: El orden de las tablas en LEFT JOIN es importante.

¿Y si utilizamos un INNER JOIN?

Cambiemos la consulta y utilicemos una INNER JOIN:

SELECT d.id,
	 d.department_name,
	 e.first_name,
	 e.last_name
FROM  departments d
INNER JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

El resultado es básicamente el mismo que cuando hicimos la unión interna en el Ejemplo 1. La única diferencia es que hay un ID de departamento en lugar de una tabla. La única diferencia es que hay un ID de departamento en lugar de un ID de empleado. Aparte de eso, es lo mismo, así que no perderemos tiempo analizándolo.

Ejemplo 3: Lista de todos los departamentos y el número de empleados en cada uno

LEFT JOINEl ejemplo 3, como todos los otros joins, se usa a menudo con las funciones agregadas de SQL. Esto será muy útil en este caso.

Eche un vistazo a esta consulta:

SELECT d.department_name, 
 COUNT(e.id) AS number_of_employees
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

Comencemos explicando desde la cláusula FROM. Como queremos listar todos los departamentos existentes, primero nos referimos a la tabla departments en FROM. ¿Por qué? Porque hay algunos empleados sin departamento, y no los queremos en nuestro resultado. Como vimos, también hay un departamento sin empleados; queremos este departamento en el resultado a pesar de todo.

A continuación aparece la tabla employees después de LEFT JOIN. Las tablas se unen con la misma condición que en el ejemplo anterior.

Para obtener la salida correcta, necesitamos listar el nombre del departamento en SELECT. También necesitamos usar la función agregada COUNT(e.id) para contar el número de IDs de empleados.

¿Por qué no podemos utilizar COUNT(*)? Porque cuenta todas las filas, incluidos los valores de NULL. Esto sesgaría nuestros resultados. El departamento de cumplimiento tiene cero empleados, que se habrían mostrado como NULL al unirse. COUNT(*) habría contado este NULL como uno, lo que no sería exacto.

La opción COUNT(e.id) ignora los valores de NULL y sólo contará los empleados cuyo ID no sea NULL. Esto es más un tema de función agregada que un tema de LEFT JOIN, así que no entraremos en más detalles.

Sin embargo, es extremadamente importante entender cuándo utilizar las distintas opciones de COUNT(). Puede consultar nuestro artículo que detalla todas las diferentes encarnaciones y usos de la función COUNT() para obtener más información.

Volvamos a nuestro código. Después de contar y unir, la salida se agrupa por el nombre del departamento. Todo ello dará como resultado la lista de departamentos con el número de empleados en cada departamento:

El resultado muestra que hay, en total, nueve empleados en los departamentos de la empresa: seis en Contabilidad, cero en Cumplimiento y tres en Ventas.

¿Y si utilizamos INNER JOIN?

Ahora, hagamos la misma consulta pero con INNER JOIN.

SELECT d.department_name, 
	 COUNT(e.id) AS number_of_employees
FROM departments d
JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

¿Será diferente el resultado? Veámoslo.

El resultado, de nuevo, muestra que hay nueve empleados en varios departamentos. Lo que obviamente falta es que la empresa tiene un departamento de Cumplimiento que no aparece en este resultado.

Si hubiéramos utilizado INNER JOIN, habríamos llegado a la conclusión de que sólo hay dos departamentos en la empresa. LEFT JOIN fue, de nuevo, la elección correcta, ya que puede haber (al menos temporalmente) un departamento sin empleados.

Conjunto de datos del ejemplo 4

En este ejemplo utilizaremos tres tablas. La primera es artistsy contiene los siguientes datos sobre artistas musicales:

Cree la tabla usando la consulta aquí.

La siguiente tabla es albums:

Y aquí está la consulta para crear esta tabla.

La consulta para la tercera tabla está aquí. La tabla se llama grammy_award. Es una lista de las categorías de los premios Grammy. Sólo tiene dos categorías: el ganador del mejor álbum y el nominado al mejor álbum.

Ejemplo 4: Lista de todos los artistas, sus álbumes y la categoría del premio Grammy

LEFT JOIN también se puede utilizar para unir más de dos tablas, y veremos cómo hacerlo en la consulta siguiente:

SELECT artist_name,
	 album_title,
	 grammy_category
FROM artists ar
LEFT JOIN albums al
ON ar.id = al.artist_id
LEFT JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

En primer lugar, enumeramos todas las columnas necesarias para mostrar el nombre del artista, el título del álbum, el año de publicación y la categoría del premio Grammy.

Vemos en las tablas presentadas que hay álbumes sin información sobre el artista. Nuestro catálogo está incompleto, por lo que la forma más segura de listar todos los artistas disponibles es LEFT JOIN tablas y considerar la tabla artists como la tabla izquierda. Se une a la izquierda con la tabla albums con el ID del artista.

Para obtener los datos sobre los Grammy, debemos unir de algún modo la tercera tabla. ¿Cómo se hace? Sencillo: escriba de nuevo el comando LEFT JOIN y haga referencia a la tabla grammy_award después. Esto hará que LEFT JOIN albums con grammy_award. Las tablas se unen en el ID de categoría Grammy.

Veamos el resultado:

El resultado muestra todos los artistas, sus álbumes y si el álbum fue nominado o ganador del premio Álbum del Año. También muestra los álbumes que no fueron ni nominados ni ganadores del Grammy.

¿Y si utilizamos INNER JOIN?

Este es el mismo código anterior, con JOIN en lugar de LEFT JOIN:

SELECT artist_name,
	 album_title,
	 grammy_category
FROM artists ar
JOIN albums al
ON ar.id = al.artist_id
JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

Veamos lo que devuelve el código:

Este resultado está incompleto. Le falta un artista: George Benson. También faltan los álbumes sin categoría Grammy. Como no queríamos listar sólo los álbumes con categoría Grammy, LEFT JOIN era la opción correcta.

Aquí hay algunas explicaciones adicionales sobre cómo LEFT JOIN múltiples tablas.

Rincón del principiante: Algunos consejos para escribir JOINs

He aquí un resumen de los errores más comunes que cometen los principiantes al usar LEFT OUTER JOIN en SQL.

Ya los hemos cubierto en los ejemplos, pero puede que no sean tan obvios para un ojo menos experimentado. Así que pongámoslos en palabras.

Eligiendo el Join Correcto

Comparamos todos los ejemplos de LEFT JOIN con las versiones de INNER JOIN. Como has visto, el resultado cambia significativamente. Es por eso que debe elegir cuidadosamente qué unión utilizará.

El mejor consejo es pensar en esas dos definiciones de join. Así, si sólo necesita que coincidan los datos de dos tablas, entonces necesita INNER JOIN. En todos los demás casos, LEFT JOIN será probablemente la elección correcta.

Cómo decidir qué tabla es la izquierda

Una vez que decida utilizar LEFT JOIN, ¿cómo sabe qué tabla debe ser la izquierda? En primer lugar, la izquierda es la tabla que viene inmediatamente después de FROM. La de la derecha es la que viene después de LEFT JOIN.

Pero, ¿cómo decides cuál es cuál? Tienes que entender correctamente el problema que estás resolviendo. Busca pistas en la redacción y la lógica. Si necesitas todos los empleados, entonces la tabla con los empleados será la de la izquierda. En otras palabras, si cree que una de sus tablas necesita mostrarse sin cambios, esa es su tabla izquierda.

Cuidado con las Funciones Agregadas

Incluso si comete un error y elige la unión incorrecta, es muy probable que lo detecte en la salida si no agrega los datos. Si está familiarizado con sus datos, verá que faltan algunas filas o que no hay NULLs donde esperaba que estuvieran.

Sin embargo, tenga mucho cuidado cuando utilice funciones de agregación. Como la salida será agregada, será mucho más difícil ver el error en la salida - a menos que conozcas todos los resultados de la agregación de datos de memoria, lo que es muy poco probable. En ese caso no necesitarías SQL, ¿verdad?

Esto es especialmente cierto cuando se utiliza COUNT(). Como has visto en uno de nuestros ejemplos, COUNT(*) puede darte resultados diferentes a los de COUNT(column_name). Es extremadamente importante que sepas lo que quieres conseguir y cómo funciona COUNT().

Recuerde: COUNT(column_name) ignora los valores de NULL, ¡mientras que COUNT(*) no lo hace!

Así que, cuando trabaje con funciones agregadas, pruebe su consulta con INNER JOIN -si tiene la oportunidad- y compruebe si devuelve resultados diferentes. Si es así, vuelva a la definición del problema y vea qué lógica de unión se adapta mejor a su lógica de negocio.

Uniones izquierdas en cascada

Esto se observa cuando se unen más de dos tablas. Si elige LEFT JOIN las primeras dos tablas, usualmente tendrá que LEFT JOIN todas las otras tablas.

Usted vio esto en nuestro último ejemplo, ya que usamos dos LEFT JOINs. Incluso si utilizáramos primero un LEFT JOIN y luego un INNER JOIN, no obtendríamos la lista de todos los artistas ni los demás datos. Incluir INNER JOIN en esta cadena de uniones dará el mismo resultado que utilizar INNER JOIN en lugar de cada LEFT JOIN.

No siempre es así. Pero es bastante seguro seguir esta regla general: si necesita una LEFT JOIN, necesita todas las LEFT JOIN.

¿Busca más práctica de LEFT JOIN?

Este artículo le dio una idea de lo que es un LEFT JOIN y cómo manejar sus trucos ocasionales. También practicamos escribiendo código mientras se familiarizaba con la sintaxis de LEFT JOIN.

Para dominar todos los matices de LEFT OUTER JOIN en SQL, recomendamos más práctica. Tres ejemplos no son suficientes. Para profundizar en lo que ha aprendido aquí, le recomendamos nuestro curso Tipos de JOIN en SQL curso. Aparte de los fundamentos teóricos, también obtendrá mucha práctica con escenarios del mundo real.

Aquí tienes algunas ideas adicionales sobre cómo practicar Tipos de JOIN en SQL. Las preguntas de la entrevistaTipos de JOIN en SQL son también un valioso recurso para practicar los materiales. ¡Feliz aprendizaje!