El FULL OUTER JOIN combina datos de dos tablas y devuelve todas las filas de ambas tablas, incluidas las filas coincidentes y no coincidentes de ambos lados.

En otras palabras, el FULL OUTER JOIN combina los resultados del left join y del right join.

Esta es la sintaxis básica de la cláusula FULL OUTER JOIN:

SELECT select_list
FROM table1
FULL OUTER JOIN table2 
   ON table1.column_name = table2.column_name;

En esta sintaxis:

• Primero, especifique las columnas de table1 y table2 en la select_list.
• En segundo lugar, especifique la table1 que desea recuperar los datos en la cláusula FROM.
• En tercer lugar, especifique la table2 que desea unir con la table1 en la cláusula FULL OUTER JOIN.
• Por último, defina una condición para unir dos tablas.

El FULL OUTER JOIN también se conoce como FULL JOIN. La palabra clave OUTER es opcional.

Cómo funciona el FULL OUTER JOIN¶

Paso 1. Inicializar el conjunto de resultados:¶

• El FULL OUTER JOIN comienza con un resultado vacío.

Paso 2. Coincidencia de filas:¶

• En primer lugar, identifique las filas de la tabla1 y la tabla2 en las que coincidan los valores del nombre_columna especificado.
• A continuación, incluya estas filas coincidentes en el conjunto de resultados.

Paso 3. Incluir filas no coincidentes de la tabla1 y la tabla2:¶

• En primer lugar, incluya las filas de la tabla1 que no coincidan con las de la tabla2. Para las columnas de la tabla2 en estas filas, incluya NULLs.
• En segundo lugar, incluya las filas de la tabla2 que no coincidan con las de la tabla1. Para las columnas de la tabla1 en estas filas, incluya NULLs.

Paso 4. Devolver el conjunto de resultados:¶

• Devuelve el conjunto de resultados final que contendrá todas las filas de ambas tablas, con filas coincidentes y no coincidentes tanto de table1 como de table2.
• Si una fila tiene una coincidencia en ambos lados, combina los valores en una sola fila.
• Si no hay ninguna coincidencia en uno de los lados, las columnas del lado no coincidente tendrán valores NULL.

El siguiente diagrama de Venn ilustra la operación FULL OUTER JOIN.

Configurar tablas de ejemplo¶

Primero, cree dos nuevas tablas para la demostración: employees y departments:

CREATE TABLE departments (
  department_id serial PRIMARY KEY, 
  department_name VARCHAR (255) NOT NULL
);
CREATE TABLE employees (
  employee_id serial PRIMARY KEY, 
  employee_name VARCHAR (255), 
  department_id INTEGER
);

Cada departamento tiene cero o muchos empleados y cada empleado pertenece a cero o un departamento.

En segundo lugar, inserte algunos datos de ejemplo en las tablas departments y employees.

INSERT INTO departments (department_name) 
VALUES 
  ('Sales'), 
  ('Marketing'), 
  ('HR'), 
  ('IT'), 
  ('Production');
INSERT INTO employees (employee_name, department_id) 
VALUES 
  ('Bette Nicholson', 1), 
  ('Christian Gable', 1), 
  ('Joe Swank', 2), 
  ('Fred Costner', 3), 
  ('Sandra Kilmer', 4), 
  ('Julia Mcqueen', NULL);

En tercer lugar, consulta los datos de las tablas departments y employees:

SELECT * FROM departments;

Salida:

 department_id | department_name
---------------+-----------------
             1 | Sales
             2 | Marketing
             3 | HR
             4 | IT
             5 | Production
(5 rows)
SELECT * FROM employees;

Salida:

 employee_id |  employee_name  | department_id
-------------+-----------------+---------------
           1 | Bette Nicholson |             1
           2 | Christian Gable |             1
           3 | Joe Swank       |             2
           4 | Fred Costner    |             3
           5 | Sandra Kilmer   |             4
           6 | Julia Mcqueen   |          null
(6 rows)

Ejemplos de FULL OUTER JOIN en PostgreSQL¶

Veamos algunos ejemplos del uso de la cláusula FULL OUTER JOIN.

Ejemplo básico de FULL OUTER JOIN¶

La siguiente consulta utiliza la cláusula FULL OUTER JOIN para consultar datos de las tablas employees y departments:

SELECT 
  employee_name, 
  department_name 
FROM 
  employees e 
FULL OUTER JOIN departments d 
  ON d.department_id = e.department_id;

Salida:

  employee_name  | department_name
-----------------+-----------------
 Bette Nicholson | Sales
 Christian Gable | Sales
 Joe Swank       | Marketing
 Fred Costner    | HR
 Sandra Kilmer   | IT
 Julia Mcqueen   | null
 null            | Production
(7 rows)

El conjunto de resultados incluye todos los empleados que pertenecen a un departamento y todos los departamentos que tienen un empleado.

Además, incluye todos los empleados que no pertenecen a ningún departamento y todos los departamentos que no tienen empleados.

Ejemplo de utilización de FULL OUTER JOIN con cláusula WHERE¶

El siguiente ejemplo utiliza FULL OUTER JOIN con una cláusula WHERE para encontrar el departamento que no tiene ningún empleado:

SELECT 
  employee_name, 
  department_name 
FROM 
  employees e 
FULL OUTER JOIN departments d 
  ON d.department_id = e.department_id 
WHERE 
  employee_name IS NULL;

Salida:

 employee_name | department_name
---------------+-----------------
 null          | Production
(1 row)

El resultado muestra que el departamento Production no tiene ningún empleado.

El siguiente ejemplo utiliza el cluase FULL OUTER JOIN con una cláusula WHERE para encontrar empleados que no pertenezcan a ningún departamento:

SELECT 
  employee_name, 
  department_name 
FROM 
  employees e 
FULL OUTER JOIN departments d 
  ON d.department_id = e.department_id 
WHERE 
  department_name IS NULL;

Salida:

 employee_name | department_name
---------------+-----------------
 Julia Mcqueen | null
(1 row)

La salida muestra que Juila Mcqueen no pertenece a ningún departamento.

Resumen¶

• Utilice la cláusula FULL OUTER JOIN de PostgreSQL para combinar datos de ambas tablas, asegurándose de que se incluyen las filas coincidentes tanto de la tabla izquierda como de la derecha, así como las filas no coincidentes de cualquiera de las tablas.