SQL Full Outer Join

Example Tables

We will use the following tables named employees and departments for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(50),
        department_id INT
    );

    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(50)
    );

    INSERT INTO employees (employee_id, employee_name, department_id) VALUES
    (1, 'John Doe', 1),
    (2, 'Jane Smith', 2),
    (3, 'Emily Davis', NULL),
    (4, 'Michael Brown', 3);

    INSERT INTO departments (department_id, department_name) VALUES
    (1, 'HR'),
    (2, 'Finance'),
    (4, 'IT');
    

Employees Table

Departments Table

Using SQL FULL OUTER JOIN

The SQL FULL OUTER JOIN keyword returns all records when there is a match in either left (employees) or right (departments) table records. It means that if there are rows in the left table that do not have matches in the right table, or if there are rows in the right table that do not have matches in the left table, those rows will be included in the result set.

Example: Full Outer Join

To perform a full outer join on the employees and departments tables, use the following query:

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

Result: