FULL JOIN

In the previous lessons, we learned about INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Now we will explore FULL JOIN, which combines the behavior of both LEFT and RIGHT joins.

What is FULL JOIN?

A FULL JOIN returns:

• All matching rows from both tables
• All non-matching rows from the left table
• All non-matching rows from the right table

When there is no match, NULL values are used.

FULL JOIN Concept (Simple)

Think of FULL JOIN as:

• LEFT JOIN results
• PLUS RIGHT JOIN results
• WITHOUT losing any data

Every row from both tables appears at least once.

Standard FULL JOIN Syntax

In databases that support FULL JOIN, the syntax looks like this:

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

However, there is an important limitation in MySQL.

⚠️ FULL JOIN in MySQL (Very Important)

MySQL does NOT support FULL JOIN directly.

If you try to run a FULL JOIN query in MySQL, it will result in an error.

Instead, we simulate FULL JOIN using:

• LEFT JOIN
• RIGHT JOIN
• UNION

Example Tables

employees

+----+----------+---------------+
| id | name     | department_id |
+----+----------+---------------+
| 1  | Alice    | 101           |
| 2  | Bob      | 102           |
| 3  | Charlie  | 105           |
+----+----------+---------------+
  

departments

+---------------+----------------+
| department_id | department_name|
+---------------+----------------+
| 101           | IT             |
| 102           | HR             |
| 103           | Finance        |
+---------------+----------------+
  

Simulating FULL JOIN in MySQL

To simulate FULL JOIN in MySQL, we combine LEFT JOIN and RIGHT JOIN using UNION.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id

UNION

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
  

This query returns:

• Matching employee–department rows
• Employees without departments
• Departments without employees

Why UNION Works

UNION:

• Combines results from two SELECT queries
• Removes duplicate rows by default

LEFT JOIN gives unmatched left rows, RIGHT JOIN gives unmatched right rows. Together, they behave like FULL JOIN.

FULL JOIN vs LEFT vs RIGHT

Common Beginner Mistakes

• Trying to use FULL JOIN directly in MySQL
• Forgetting UNION when simulating FULL JOIN
• Using UNION ALL unintentionally
• Assuming FULL JOIN is always required

When Do You Need FULL JOIN?

FULL JOIN is useful when:

• You need a complete comparison of two tables
• You want to detect missing relationships on both sides
• You are performing data audits or reconciliation

In many real-world applications, LEFT JOIN is sufficient.

What’s Next?

In the next lesson, we will explore CROSS JOIN, which creates combinations of rows between tables.