SQL FULL OUTER JOIN — Combining All Matched and Unmatched Data

The school wants to reconcile two lists: students missing results and results missing a valid student record.

A FULL OUTER JOIN returns matching row pairs plus unmatched rows from both sources. Fields from a missing partner are represented by NULL.

The result includes the matched students, an unmatched student from the left and an orphan result from the right. PostgreSQL and recent SQLite support FULL OUTER JOIN directly. MySQL 8.4 does not; its teaching alternative combines an outer join with only the opposite side’s unmatched rows.

Related concepts: LEFT JOIN and RIGHT JOIN and Remove Duplicate Results.

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)

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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;

• 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.

UNION removes duplicate output rows, whereas UNION ALL retains them. Simply joining both directions with UNION is not a general duplicate-preserving substitute for a full join. The unmatched branch should use a non-nullable matching key to identify absent partners.

Write a duplicate-preserving two-branch alternative and identify the unmatched records.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT s.roll_no AS student_roll, r.roll_no AS result_roll FROM practice_students AS s LEFT JOIN practice_results AS r ON s.roll_no = r.roll_no UNION ALL SELECT NULL, r.roll_no FROM practice_results AS r LEFT JOIN practice_students AS s ON s.roll_no = r.roll_no WHERE s.roll_no IS NULL;

The first branch retains every student and its match. The second adds only result rows that lack a student, so matched pairs are not duplicated by both branches.

No ordering is promised by this UNION ALL query; these are its result rows, and their display order may differ.

A FULL OUTER JOIN returns matching row pairs plus unmatched rows from both sources. Fields from a missing partner are represented by NULL.

The first branch retains every student and its match. The second adds only result rows that lack a student, so matched pairs are not duplicated by both branches.

Continue your understanding with LEFT JOIN and RIGHT JOIN and Remove Duplicate Results.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.