SQL FULL OUTER JOIN - Combining All Matched and Unmatched Data

Introduction

Imagine you have a list of students and a separate list of exam results. Some students didn’t appear. Some results don’t have a matching student. You want a complete view — no exclusions. That’s what FULL OUTER JOIN does. It combines all rows from both tables, matched or not.

What is FULL OUTER JOIN?

FULL OUTER JOIN returns:

• Matched rows from both tables
• Unmatched rows from the left table (with NULLs on the right)
• Unmatched rows from the right table (with NULLs on the left)

Syntax

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

1. Sample Tables – Students and Results

students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A'),
(2, 'Diya Iyer', '9B'),
(3, 'Sneha Patil', '10A'),
(4, 'Mehul Agarwal', '8C');

results

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO results VALUES
(1, 'Maths', 85),
(2, 'Maths', 92),
(5, 'Maths', 88); -- No student with roll_no 5

2. Performing FULL OUTER JOIN

Let’s see all students and all results, matched or not:

SELECT s.roll_no AS student_roll,
        s.name AS student_name,
        r.subject,
        r.marks
FROM students s
FULL OUTER JOIN results r
ON s.roll_no = r.roll_no;

student_roll | student_name   | subject | marks
-------------+----------------+---------+-------
1            | Aarav Sharma   | Maths   | 85
2            | Diya Iyer      | Maths   | 92
3            | Sneha Patil    | NULL    | NULL
4            | Mehul Agarwal  | NULL    | NULL
NULL         | NULL           | Maths   | 88

Explanation: You see:

• Matched rows: roll_no 1 and 2
• Students with no results: roll_no 3, 4
• Results with no student: roll_no 5

3. Filtering Unmatched Records

Want to find only mismatches (unmatched records)?

SELECT s.name, r.subject, r.marks
FROM students s
FULL OUTER JOIN results r ON s.roll_no = r.roll_no
WHERE s.roll_no IS NULL OR r.roll_no IS NULL;

name           | subject | marks
----------------+---------+-------
NULL           | Maths   | 88
Sneha Patil    | NULL    | NULL
Mehul Agarwal  | NULL    | NULL

Note on Database Support

Some databases (like MySQL) don’t support FULL OUTER JOIN directly. You can simulate it using UNION of LEFT JOIN and RIGHT JOIN:

SELECT s.roll_no, s.name, r.subject, r.marks
FROM students s
LEFT JOIN results r ON s.roll_no = r.roll_no

UNION

SELECT s.roll_no, s.name, r.subject, r.marks
FROM students s
RIGHT JOIN results r ON s.roll_no = r.roll_no;

Best Practices

• Use FULL OUTER JOIN when you need complete visibility from both tables.
• Check database compatibility — use UNION workaround if FULL OUTER JOIN isn’t supported.
• Use IS NULL filters to extract unmatched records for reporting or audit.

Summary

FULL OUTER JOIN gives you the big picture. Whether you're building a report card, checking attendance logs, or reconciling financial data — this join shows everything: the perfect matches, and the missing pieces.

What’s Next?

Now that you know all join types, we’ll explore SELF JOIN — joining a table with itself to uncover patterns within a single dataset.