¿Qué es FULL JOIN en SQL? Una Explicación con 4 Ejemplos

¿Qué es FULL JOIN - uno de los tipos de uniones SQL más olvidados? En este artículo, le mostraremos lo útil que puede ser FULL JOIN y le enseñaremos cómo aplicarlo a diferentes escenarios. Los cuatro ejemplos cubren algunos de los usos típicos.

Un join SQL es una construcción para combinar datos de dos o más tablas. FULL JOIN es uno de los tipos de join. Aprovechará al máximo este artículo si ya está familiarizado con las uniones SQL y su funcionamiento. Si no lo está o si necesita refrescar sus conocimientos, le recomendamos que siga nuestro curso interactivo Tipos de JOIN en SQL interactivo. Allí le esperan noventa y tres ejercicios en los que repasará los tipos de uniones y practicará el filtrado de datos mediante uniones simples y múltiples, autouniones y uniones no equitativas.

¿Qué es una FULL JOIN?

FULL JOIN o FULL OUTER JOIN (SQL acepta ambas) es una unión externa. En SQL, una unión externa es un tipo de unión que incluye filas no coincidentes de una o ambas tablas unidas; LEFT JOIN y RIGHT JOIN también son uniones externas. FULL JOIN es una unión de LEFT JOIN y RIGHT JOIN: muestra las filas coincidentes y no coincidentes de ambas tablas. Cuando los valores de una tabla no coinciden en la otra tabla, FULL JOIN devuelve NULLs. Visualmente, se puede mostrar así:

Sintaxis FULL JOIN

La sintaxis de FULL JOIN es:

SELECT …
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Como es habitual en las uniones SQL, las partes principales de la sintaxis FULL JOIN son las cláusulas JOIN y ON. La primera tabla de la cláusula FROM va seguida de la palabra clave FULL JOIN, que une la segunda tabla. La condición por la que se unen las tablas se especifica en la cláusula ON. Normalmente, las tablas se unen en función de una columna que comparten, por ejemplo, la columna id de la tabla writer y la columna writer_ID de la tabla book de la tabla. Estas dos columnas tendrían los mismos valores debido a la relación clave primaria - clave externa.

En un ejemplo sencillo, podríamos escribir este código y obtener el siguiente resultado:

Otros Tipos de Uniones en SQL

Ya hemos mencionado que FULL JOIN es sólo uno de los varios tipos de uniones en SQL. Aquí está la lista completa:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

JOIN (o INNER JOIN) es un tipo de unión interna que devuelve sólo las filas coincidentes de las tablas unidas. Puede aprender más sobre INNER JOIN aquí.

LEFT JOIN (o LEFT OUTER JOIN) muestra todas las filas de la primera tabla (izquierda) y sólo las filas coincidentes de la segunda tabla (derecha). Si no hay filas coincidentes en la tabla derecha, los valores se muestran como NULL. Encontrará más información en nuestro artículo sobre LEFT JOIN.

RIGHT JOIN (o RIGHT OUTER JOIN) es la imagen especular de LEFT JOIN. Devuelve todas las filas de la segunda tabla (derecha) y sólo las filas coincidentes de la primera tabla (izquierda). Cuando hay filas no coincidentes en la tabla izquierda, los valores son NULL.

Ahora puede entender mejor por qué dijimos que FULL JOIN es la unión de LEFT JOIN y RIGHT JOIN. Como muestra todos los datos de las tablas izquierda y derecha y también las filas que no coinciden, es como si se usara LEFT JOIN y RIGHT JOIN al mismo tiempo.

CROSS JOIN devuelve un producto cartesiano. En otras palabras, devuelve todas las combinaciones de todas las filas de ambas tablas unidas.

Ejemplos de FULL JOIN

Pasemos ahora a los ejemplos que muestran el uso típico de FULL JOIN.

Ejemplo 1: Mostrar todos los empleados y departamentos de la empresa

Tarea: Necesita mostrar todos los empleados de una empresa, incluso si no tienen un departamento asignado. También necesita mostrar todos los departamentos, incluso si no hay empleados asignados a ellos.

Conjunto de datos: Utilizaremos dos tablas para resolver el problema. La primera tabla es department (el script está aquí), que es una lista de los departamentos de la empresa:

id	department_name
1	IT
2	Accounting
3	Sales

La segunda tabla es empleados, con los datos que se muestran a continuación. El script está aquí:

id	first_name	last_name	department_id
1	Sarah	Zimmerman	2
2	Thomas	Tyson	1
3	Daniel	Richardson	1
4	Sofia	Tardelli	2
5	Mark	Fitzpatrick	4

Solución:

SELECT 
  first_name,
  last_name,
  department_name
FROM employees e
FULL JOIN department d
  ON e.department_id = d.id;

Explicación: El código selecciona los nombres y apellidos de los empleados y el nombre del departamento.

Podemos hacerlo porque FULL JOIN las dos tablas. Hacemos referencia a la tabla employees en FROM y le damos un alias. A continuación, hacemos referencia a la segunda tabla, departmenten FULL JOIN. Las tablas se unen en función de los ID de departamento que se encuentran en ambas tablas. Esta condición de unión se escribe en la cláusula ON.

Resultado: La salida devuelve todos los datos de ambas tablas. Hay algunas filas interesantes que necesitan elaboración.

Mark Fitzpatrick no tiene departamento. department_id valor es 5, pero no hay ningún departamento con ID 5 en la tabla department. Por lo tanto, hay un valor NULL bajo el nombre del departamento. Esto puede deberse a que es un empleado nuevo y la tabla no se actualiza con el departamento. employees y la tabla no está actualizada con los datos del departamento.

Además, la última fila muestra el departamento de Ventas, pero no el nombre del empleado. Esto indica que Ventas no tiene empleados. Nada inusual aquí, ya que nos enteramos de que los tres empleados de Ventas renunciaron recientemente a la empresa.

first_name	last_name	department_name
Sarah	Zimmerman	Accounting
Thomas	Tyson	IT
Daniel	Richardson	IT
Sofia	Tardelli	Accounting
Mark	Fitzpatrick	NULL
NULL	NULL	Sales

Ejemplo 2: Mostrar todos los clientes con el número de productos comprados y el número de productos sin ventas

Tarea: Hay una tienda de discos en Internet que vende vinilos (es decir, discos de música). Su tarea es listar todos los clientes y encontrar el número de vinilos que cada cliente compró. La salida también debe mostrar el número de vinilos que nadie ha comprado todavía.

Conjunto de datos: Trabajaremos con tres tablas. La primera es customercon el script aquí:

id	first_name	last_name
1	Marvin	Simmons
2	Marianne	Dickens
3	Susan	Strozzi

Puedes encontrar el script para la segunda tabla, vinyl, aquí. La tabla muestra la lista de vinilos, como puedes ver a continuación:

id	artist	album_name	price
1	Callier, Terry	What Color is Love	24.99
2	Guy, Buddy	Sweet Tea	32.99
3	Little Simz	A Curious Tale of Trials	32.99
4	LaVette, Bettye	Scene of the Crime	36.99

La tercera tabla es una tabla de unión llamada purchase. Puedes encontrar el script aquí:

id	customer_id	vinyl_id	purchase_date
1	1	2	2023-01-03
2	1	3	2023-01-12
3	1	2	2023-02-18
4	1	3	2023-03-01
5	2	3	2023-03-01
6	2	2	2023-04-01
7	2	4	2023-05-01

Solución:

SELECT 
  first_name,
  last_name,
  COUNT (v.id) AS vinyl_count	   
FROM customer c
FULL JOIN purchase p
  ON c.id = p.customer_id
FULL JOIN vinyl v
  ON p.vinyl_id = v.id
GROUP BY first_name, last_name;

Explicación: Este es un ejemplo del uso de FULL JOIN así como de la unión de más de dos tablas.

La consulta selecciona los nombres de los clientes. A continuación, utiliza la función agregada COUNT() para hallar el número de vinilos comprados. FULL JOIN la tabla customer con la tabla purchase tabla. Esto se hace con los ID de cliente de ambas tablas.

Ahora, tenemos que unir la tercera tabla. Es sencillo: escribimos de nuevo FULL JOIN y hacemos referencia a la tabla vinyl. Ahora, puedes unirla con la tabla purchase sobre los ID de los vinilos.

Resultado: La salida muestra que hay un vinilo que nadie ha pedido todavía. Puedes reconocerlo por los NULLs.

También muestra una lista de todos los clientes y el número de vinilos que han comprado. Susan Strozzi no ha comprado nada. Marianne Dickens y Marvin Simmons compraron tres y cuatro vinilos, respectivamente.

first_name	last_name	vinyl_count
NULL	NULL	1
Susan	Strozzi	0
Marianne	Dickens	3
Marvin	Simmons	4

Ejemplo 3: Listar todos los pares de alumnos y las notas de los alumnos

Tarea: Usted trabaja para una plataforma de certificación SQL en línea. Ofrece varias certificaciones; cada año, hay una nueva edición de cada certificación. Encuentre todos los posibles pares estudiante-certificación y la calificación que obtuvo cada estudiante en todas las ediciones de la certificación.

Conjunto de datos: El conjunto de datos está aumentando; ahora incluye cuatro tablas. La primera tabla es student; aquí está el script. Es una lista de los estudiantes de la plataforma:

nuestros vinilos, respectivamente.

id	first_name	last_name
1	Tom	Frank
2	Mary	Maddison
3	Pavel	Kuba
4	Amanda	Wilson

La segunda tabla es la tabla de certificados, que contiene la lista de certificados. Aquí está el script:

id	certificate_name
1	Microsoft Certified: Azure Data Fundamentals
2	Oracle Database SQL Certified Associate Certification
3	IBM Certified Database Associate
4	MySQL 5.7 Database Administrator Certification
5	EDB PostgreSQL 12 Associate Certification

La siguiente tabla es una tabla de unión llamada certificate_enrollment

id	student_id	edition_id	grade	pass
1	2	1	620	FALSE
2	2	6	850	TRUE
3	2	10	900	TRUE
4	1	2	100	FALSE
5	1	7	500	FALSE
6	1	7	800	TRUE
7	4	8	800	TRUE

. Puede encontrar el script aquí. La tabla muestra qué estudiantes se matricularon en qué certificado, junto con sus calificaciones y si aprobaron:

La última tabla es una tabla de unión llamada certificate_edition. Muestra la lista de ediciones del certificado, enlazando con la tabla certificate. El script está aquí:

id	certificate_id	edition
1	1	2022
2	2	2022
3	3	2022
4	4	2022
5	5	2022
6	1	2023
7	2	2023
8	3	2023
9	4	2023
10	5	2023

Solución:

SELECT 
  first_name,
  last_name,
  certificate_name,
  edition,
  grade
FROM student s
FULL JOIN certificate_enrollment cen
  ON s.id = cen.student_id
FULL JOIN certificate_edition ced
  ON cen.edition_id = ced.id
FULL JOIN certificate c
  ON ced.certificate_id = c.id;

Explicación: Primero seleccionamos todas las columnas relevantes de las cuatro tablas: first_name y last_name de studentcertificate_name de certificateedition de certificate_edition y grade de certificate_enrollment.

A continuación, unimos las tablas como en los ejemplos anteriores. En primer lugar, la student tabla FULL JOINed con certificate_enrollment en los ID de los alumnos.

La segunda FULL JOIN añade la tabla certificate_edition tabla para unirla con certificate_enrollment. Las tablas se unen en el ID de edición del certificado.

Ahora que hemos unido tres tablas, podemos añadir la cuarta. Hacemos referencia a la tabla certificate en FULL JOIN. La unimos con certificate_edition en el ID del certificado.

Resultado: La salida muestra exactamente lo que queremos.

Hay una lista de todos los estudiantes que recibieron un certificado, su edición y la nota que obtuvo cada estudiante. Si la nota es inferior a 700, el alumno suspendió y tuvo que repetir el examen del certificado.

Hay un estudiante que aún no se ha matriculado en ningún programa de certificación: Pavel Kuba. Además, hay cuatro ediciones del certificado sin ningún alumno matriculado.

first_name	last_name	certificate_name	edition	grade
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2022	620
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2023	850
Mary	Maddison	EDB PostgreSQL 12 Associate Certification	2023	900
Tom	Frank	Oracle Database SQL Certified Associate Certification	2022	100
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	500
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	800
Amanda	Wilson	IBM Certified Database Associate	2023	800
Pavel	Kuba	NULL	NULL	NULL
NULL	NULL	EDB PostgreSQL 12 Associate Certification	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2022	NULL
NULL	NULL	IBM Certified Database Associate	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2023	NULL

Ejemplo 4: Encontrar todas las parejas autor-tema y mostrar los artículos y su estado

Tarea: Usted trabaja para una empresa que contrata escritores de artículos para sus clientes. Hay una lista de escritores y temas que ofrece. Además, cada tema tiene actualmente varios artículos disponibles. Los escritores están asignados a estos artículos. Un artículo puede tener uno de estos dos estados: 'Escribiendo' (el artículo se está escribiendo) o 'Revisando' (el artículo se está revisando).

Encuentre todas las parejas escritor-tema. Además, enumere todos los artículos y su estado.

Conjunto de datos: Este conjunto de datos también consta de cuatro tablas. La primera tabla es writercon el script enlazado aquí:

id	first_name	last_name
1	Victoria	Thompson
2	Mike	McGill
3	Sky	Herrera
4	Jimmy	Goodman

La siguiente tabla es topic. Aquí está el script:

id	topic_name
1	SQL
2	Python
3	ML
4	SQL Careers
5	Python Careers

La tercera tabla es article_assignment. Vincula al escritor con el artículo y muestra el estado actual del artículo. Este es el guión:

id	writer_id	article_id	status
1	4	3	Revising
2	4	1	Writing
3	3	5	Writing
4	3	8	Revising
5	3	10	Revising
6	1	7	Writing

La cuarta tabla relaciona el artículo con el tema. Se llama articley el script está aquí:

id	article_title	topic_id
1	What is FULL JOIN in SQL? An Explanation with 4 Examples	1
2	Pandas in Python	2
3	Supervised Learning	3
4	Basic SQL Interview Questions	4
5	Basic Python Interview Questions	5
6	SQL Funciones de ventana	1
7	Ranking Data in Python	2
8	Unsupervised Learning	3
9	Intermediate SQL Interview Questions	4
10	Intermediate Python Interview Questions	5

Solución:

SELECT 
  first_name,
  last_name,
  topic_name,
  article_title,
  status
FROM writer w
FULL JOIN article_assignment aa
  ON w.id = aa.writer_id
FULL JOIN article a
  ON aa.article_id = a.id
FULL JOIN topic t
  ON a.topic_id = t.id;

Explicación: La consulta es similar a la anterior. Selecciona todas las columnas relevantes: el nombre del autor, el tema, el título del artículo y su estado. A continuación, une las tablas writer y article_assignment en el ID del autor mediante FULL OUTER JOIN.

Otro FULL JOIN añade la tercera tabla article y la une con el ID del artículo. Por último, la cuarta tabla se une con el ID del tema.

Resultado: Aquí hay una lista de todos los escritores, los temas, artículos y estados.

Los temas de Jimmy Goodman son ML y SQL. Sky Herrera escribe sobre carreras en Python y ML. Victoria Thompson escribe sólo sobre Python.

Mike McGill no tiene ningún artículo asignado. Además, hay un artículo sobre Python, uno sobre SQL y dos sobre SQL Career sin asignar a ningún escritor.

first_name	last_name	topic_name	article_title	status
Jimmy	Goodman	ML	Supervised Learning	Revising
Jimmy	Goodman	SQL	What is FULL JOIN in SQL? An Explanation with 4 Examples	Writing
Sky	Herrera	Python Careers	Basic Python Interview Questions	Writing
Sky	Herrera	ML	Unsupervised Learning	Revising
Sky	Herrera	Python Careers	Intermediate Python Interview Questions	Revising
Victoria	Thompson	Python	Ranking Data in Python	Writing
Mike	McGill	NULL	NULL	NULL
NULL	NULL	Python	Pandas in Python	NULL
NULL	NULL	SQL	SQL Funciones de ventana	NULL
NULL	NULL	SQL Careers	Basic SQL Interview Questions	NULL
NULL	NULL	SQL Careers	Intermediate SQL Interview Questions	NULL

No FULL JOIN, No SQL Mastery

FULL JOIN probablemente no se usa tan a menudo como JOIN o LEFT JOIN. Es un humilde join que espera hasta que lo necesites para brillar. Pero sin ella en tu repertorio, no puedes llamarte a ti mismo un maestro SQL.

Los ejemplos anteriores te han mostrado escenarios prácticos en los que necesitarás utilizar todos los datos de dos (o más) tablas. FULL JOIN te lo pone realmente fácil.

Por supuesto, necesitarás algo más de práctica, ya que la parte más crucial del uso de las uniones SQL es decidir qué unión usar. Una vez que domines esto, las uniones serán más fáciles: la sintaxis es la misma, independientemente del tipo de unión que utilices. Nuestro curso Tipos de JOIN en SQL puede ayudarle a dominar JOIN, ya que está repleto de ejercicios prácticos. Lo mismo puede decirse de nuestro artículo de práctica de uniones SQL con 12 ejemplos. Después de aprender todo esto, incluso estas preguntas de entrevista sobre SQL JOIN no serán difíciles de responder.

Ahora te toca a ti utilizar todos estos recursos para dominar FULL JOIN.