SQL Full Join

SQL FULL JOIN (FULL OUTER JOIN)

FULL JOIN returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for the missing side.

Basic Syntax (Supported Databases)

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

How FULL JOIN Works

• Returns matching rows (like INNER JOIN)
• Returns unmatched rows from the left table (like LEFT JOIN)
• Returns unmatched rows from the right table (like RIGHT JOIN)

Example

SELECT c.name, o.order_date
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON c.id = o.customer_id;

MySQL / MariaDB Note

MySQL and MariaDB do not support FULL OUTER JOIN directly. You can simulate it using UNION between LEFT JOIN and RIGHT JOIN.

SELECT c.name, o.order_date
FROM customers AS c
LEFT JOIN orders AS o
  ON c.id = o.customer_id

UNION

SELECT c.name, o.order_date
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.id = o.customer_id;

Understanding NULL Values

If a row exists in one table but not the other, the missing side will contain NULL values.

When to Use FULL JOIN

• When comparing two datasets
• When identifying mismatched records
• When performing data reconciliation

Common Mistakes

• Trying to use FULL JOIN in MySQL without simulation
• Forgetting that UNION removes duplicates (use UNION ALL if needed)
• Not handling NULL values in result interpretation

Next Step

Continue with SQL SELF JOIN to learn how to join a table with itself.