FULL OUTER JOIN in MySQL

In this lesson: MySQL has no FULL OUTER JOIN — emulate it with LEFT JOIN UNION RIGHT JOIN. Example query with output. In Hindi & English.

What a FULL OUTER JOIN really means

Imagine two lists on paper. One is a list of students, the other is a list of classes. A FULL OUTER JOIN is you sitting down and writing one combined sheet that shows everything: every student who is sitting in a class, but also every student who has no class yet, and every class that has no students yet. Nobody gets left off the page.

That is the whole idea. A normal (inner) join only shows the students who match a class — the leftovers on both sides disappear. A LEFT JOIN keeps all the students but drops the empty classes. A FULL OUTER JOIN refuses to drop anyone: matched rows, left-only rows, and right-only rows all appear together.

Why MySQL has no FULL OUTER JOIN

Here is the surprise that trips people up: MySQL simply does not have the FULL OUTER JOIN keyword. Databases like PostgreSQL and SQL Server do, but MySQL never added it. If you try to write it, MySQL stops with an error.

SELECT * FROM students
FULL OUTER JOIN classes
  ON students.class_id = classes.class_id;

So instead of one keyword, we build the same result out of two pieces MySQL does understand: a LEFT JOIN and a UNION ALL. Once you see the pattern once, it is easy to reuse everywhere.

Our sample tables

Let's use a tiny, believable example so the output is easy to check by hand. Two tables: students and classes.

CREATE TABLE classes (
    class_id   INT PRIMARY KEY,
    class_name VARCHAR(30)
);

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(30),
    class_id     INT   -- can be NULL if not assigned
);

INSERT INTO classes VALUES
(10,'X-A'), (20,'X-B'), (30,'XI-A');

INSERT INTO students VALUES
(1,'Aarav',10),
(2,'Diya',20),
(3,'Kabir',10),
(4,'Meera',20),
(5,'Vihaan',NULL);   -- not in any class yet

Read the data carefully, because it contains exactly the two interesting cases:

• Vihaan is a student with class_id = NULL — a left-only row (a student with no class).
• XI-A (class 30) has no students pointing to it — a right-only row (a class with no students).

A correct full outer join must show both of these, plus the four normal matches. So we already know the answer should have 6 rows. Predicting the row count first is the easiest way to check your query later.

The LEFT JOIN + UNION ALL trick

The recipe has two halves. The first half grabs every student (matched or not). The second half grabs every class that got left behind, and only those.

-- Half 1: every student, plus class if matched
SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM students AS s
LEFT JOIN classes AS c
       ON c.class_id = s.class_id

UNION ALL

-- Half 2: only classes that no student matched
SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM classes AS c
LEFT JOIN students AS s
       ON s.class_id = c.class_id
WHERE s.student_id IS NULL;

The magic is that last line: WHERE s.student_id IS NULL. In the second half we start from classes and try to attach students. Any class that already found a student is thrown away by that filter — because we already listed those matches in the first half. What survives is only the lonely classes (XI-A). That is what stops rows from appearing twice.

Full output explained

Look at the last two rows — they are the whole reason we did this. Vihaan shows up with NULL class columns (a student with no class). XI-A shows up with NULL student columns (a class with no student). The four rows above them are the ordinary matches. Six rows total, exactly as we predicted.

UNION vs UNION ALL (which to use)

Both stack one result on top of another, but they differ in one important way.

Since our WHERE ... IS NULL filter already guarantees no row repeats, running UNION would just waste time hunting for duplicates that cannot exist. UNION ALL is the correct, faster pick. Use plain UNION only when you genuinely expect duplicate rows and want them merged.

Common mistakes

• Typing FULL OUTER JOIN and expecting it to work. MySQL will reject it outright. There is no hidden setting to turn it on — the keyword just does not exist.
• Using plain UNION and losing real rows. If two legitimately different rows happen to look identical after selecting only a few columns, UNION silently merges them. That can quietly change your row count.
• Forgetting the WHERE ... IS NULL filter. Without it, the matched pairs from the first half get added a second time in the reversed half, and your matches appear twice.
• Filtering on the join key instead of the primary key. If the join column itself can be NULL, testing it for NULL gives wrong "no match" results. Always test a non-nullable primary key from the side you are checking.

Summary

• A FULL OUTER JOIN returns all matches plus the leftovers from both tables.
• MySQL has no FULL OUTER JOIN keyword — you build it yourself.
• The pattern is LEFT JOIN … UNION ALL … reversed LEFT JOIN with WHERE key IS NULL.
• Use UNION ALL (not UNION) — the filter already prevents duplicates, so it is faster.
• Always predict the row count first; it makes checking your query effortless.

Once this clicks, related joins feel easy. Next, compare it with SELECT DISTINCT for removing duplicate values, or revise the basics in the cross and self join lesson.