SQL FULL OUTER JOIN

A FULL OUTER JOIN returns all records from both tables.

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.id = table2.id;

Matching records are combined, while unmatched records from either table are also included.

The standard SQL syntax is:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

The exact syntax supported depends on the database system.

MySQL does not provide a native FULL OUTER JOIN operator. A common solution is to combine LEFT JOIN and RIGHT JOIN using UNION.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

FULL OUTER JOIN is useful when you want to see every record from both tables, including records that do not have a match.

For example, it can help compare:

• Students and courses
• Employees and departments
• Customers and orders
• Products and sales

When a record exists in both tables with a matching value, the information from both tables is combined.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

A FULL OUTER JOIN includes records from the left table even when there is no matching record in the right table.

The columns belonging to the right table will contain NULL.

Student: Rahul
Course ID: 10

Matching Course:
Not Found

It also includes records from the right table that have no matching record in the left table.

The columns belonging to the left table will contain NULL.

Course: Python
Course ID: 20

Matching Student:
Not Found

In MySQL, LEFT JOIN and RIGHT JOIN can be combined using UNION.

SELECT
    s.student_name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

UNION ALL can also be used, but it may return duplicate matching rows.

SELECT
    s.student_name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION ALL

SELECT
    s.student_name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

Use UNION when duplicate matching rows should be removed.

INNER JOIN: Returns only matching records.

FULL OUTER JOIN: Returns matching and unmatched records from both tables.

-- INNER JOIN
SELECT *
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

LEFT JOIN: Keeps all records from the left table.

FULL OUTER JOIN: Keeps all records from both tables.

-- LEFT JOIN
SELECT *
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;

RIGHT JOIN: Keeps all records from the right table.

FULL OUTER JOIN: Keeps all records from both tables.

-- RIGHT JOIN
SELECT *
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

NULL values are expected when a record exists on one side but has no matching record on the other side.

Student Name    Course Name
-------------   -----------
Rahul           Python
Amit            NULL
NULL            Java

Here, Amit has no matching course and Java has no matching student.

A FULL OUTER JOIN can be used to identify records that exist on only one side.

SELECT
    s.student_name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

Aliases make FULL OUTER JOIN simulations easier to read.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

A FULL OUTER JOIN concept can be extended to more tables by combining appropriate joins.

SELECT
    s.student_name,
    c.course_name,
    p.amount
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.id = p.student_id

UNION

SELECT
    s.student_name,
    c.course_name,
    p.amount
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.id = p.student_id;

COALESCE() can be used to display a value from either table when one side is NULL.

SELECT
    COALESCE(s.student_name, 'No Student') AS student,
    COALESCE(c.course_name, 'No Course') AS course
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    COALESCE(s.student_name, 'No Student') AS student,
    COALESCE(c.course_name, 'No Course') AS course
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

CASE can be used to identify whether a record exists on one or both sides.

SELECT
    s.student_name,
    c.course_name,
    CASE
        WHEN s.id IS NULL THEN 'Only in Courses'
        WHEN c.id IS NULL THEN 'Only in Students'
        ELSE 'Matched'
    END AS match_status
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name,
    CASE
        WHEN s.id IS NULL THEN 'Only in Courses'
        WHEN c.id IS NULL THEN 'Only in Students'
        ELSE 'Matched'
    END AS match_status
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

The combined result can be sorted.

SELECT
    s.student_name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
ORDER BY student_name;

Filtering can be applied to the combined result.

SELECT
    student_name,
    course_name
FROM
(
    SELECT
        s.student_name,
        c.course_name
    FROM students s
    LEFT JOIN courses c
    ON s.course_id = c.id

    UNION

    SELECT
        s.student_name,
        c.course_name
    FROM students s
    RIGHT JOIN courses c
    ON s.course_id = c.id
) AS combined_data
WHERE course_name IS NULL;

This example finds records where course information is missing.

The combined result can be grouped for reporting.

SELECT
    course_name,
    COUNT(*) AS total_records
FROM
(
    SELECT
        s.student_name,
        c.course_name
    FROM students s
    LEFT JOIN courses c
    ON s.course_id = c.id

    UNION

    SELECT
        s.student_name,
        c.course_name
    FROM students s
    RIGHT JOIN courses c
    ON s.course_id = c.id
) AS combined_data
GROUP BY course_name;

FULL OUTER JOIN is useful when comparing two datasets.

For example:

• Old student records vs new student records
• Current products vs previous products
• Employees in two departments
• Customers in two systems

It helps identify matching and missing records.

Suppose we have two student lists and want to compare them.

SELECT
    old_students.student_name,
    new_students.student_name
FROM old_students
LEFT JOIN new_students
ON old_students.student_id = new_students.student_id

UNION

SELECT
    old_students.student_name,
    new_students.student_name
FROM old_students
RIGHT JOIN new_students
ON old_students.student_id = new_students.student_id;

The same technique can compare employee records.

SELECT
    a.employee_id,
    a.employee_name,
    b.employee_name AS new_name
FROM employees_old AS a
LEFT JOIN employees_new AS b
ON a.employee_id = b.employee_id

UNION

SELECT
    a.employee_id,
    a.employee_name,
    b.employee_name AS new_name
FROM employees_old AS a
RIGHT JOIN employees_new AS b
ON a.employee_id = b.employee_id;

The main idea of simulating FULL OUTER JOIN in MySQL is:

LEFT JOIN
+
RIGHT JOIN
+
UNION

The LEFT JOIN supplies all records from the first table, while the RIGHT JOIN supplies records that exist only on the second side.

UNION removes duplicate rows, while UNION ALL keeps them.

SELECT
    s.student_name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    s.student_name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

For a typical FULL OUTER JOIN simulation, UNION is often preferred when duplicate matching rows should not appear twice.

A common mistake in MySQL is trying to directly execute:

SELECT *
FROM students
FULL OUTER JOIN courses
ON students.course_id = courses.id;

MySQL does not support the FULL OUTER JOIN keyword directly. Use a LEFT JOIN and RIGHT JOIN combination with UNION instead.

INNER JOIN: Only matching records.

LEFT JOIN: All records from the left table plus matches.

RIGHT JOIN: All records from the right table plus matches.

FULL OUTER JOIN: All records from both tables.

INNER  → Matching only
LEFT   → Everything from left
RIGHT  → Everything from right
FULL   → Everything from both

The following query can be used to compare student and course records.

SELECT
    s.student_id,
    s.student_name,
    c.id AS course_id,
    c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id

UNION

SELECT
    s.student_id,
    s.student_name,
    c.id AS course_id,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

This keeps information from both sides of the relationship.

The following example simulates a FULL OUTER JOIN in MySQL and identifies the relationship between students and courses.

SELECT
    s.student_id,
    s.student_name,
    c.id AS course_id,
    c.course_name,
    CASE
        WHEN s.id IS NULL THEN 'Only in Courses'
        WHEN c.id IS NULL THEN 'Only in Students'
        ELSE 'Matched'
    END AS match_status
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id

UNION

SELECT
    s.student_id,
    s.student_name,
    c.id AS course_id,
    c.course_name,
    CASE
        WHEN s.id IS NULL THEN 'Only in Courses'
        WHEN c.id IS NULL THEN 'Only in Students'
        ELSE 'Matched'
    END AS match_status
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

This query returns matching students and courses as well as unmatched records from either table.