SQL FULL JOIN Syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;FULL OUTER JOIN and FULL JOIN are identical — the keyword OUTER is optional.
Sample Tables
We will use the same Employees and Departments tables from the Joins Introduction:
+----+-----------+--------+--------+
| ID | Name | DeptID | Salary |
+----+-----------+--------+--------+
| 1 | Priya K. | 101 | 55000 |
| 2 | John D. | 102 | 62000 |
| 3 | Arun M. | 101 | 78000 |
| 4 | Emma W. | 103 | 51000 |
| 5 | Ravi S. | NULL | 72000 |
+----+-----------+--------+--------++-----+----------------+-----------+
| ID | DeptName | Location |
+-----+----------------+-----------+
| 101 | IT | Delhi |
| 102 | Marketing | London |
| 103 | Sales | Mumbai |
| 104 | HR | New York |
+-----+----------------+-----------+SQL FULL JOIN Example
This query joins all rows from both tables. Unmatched rows on either side appear with NULL:
SELECT e.Name, d.DeptName, e.Salary
FROM Employees AS e
FULL OUTER JOIN Departments AS d
ON e.DeptID = d.ID;+-----------+-----------+--------+ | Name | DeptName | Salary | +-----------+-----------+--------+ | Priya K. | IT | 55000 | | John D. | Marketing | 62000 | | Arun M. | IT | 78000 | | Emma W. | Sales | 51000 | | Ravi S. | NULL | 72000 | | NULL | HR | NULL | +-----------+-----------+--------+
Ravi S. appears with NULL DeptName (no department). HR appears with NULL Name and Salary (no employees). FULL JOIN keeps every row from both tables.
Find Unmatched Rows on Both Sides
Use WHERE to find rows that have no match in the other table:
SELECT e.Name, d.DeptName
FROM Employees AS e
FULL OUTER JOIN Departments AS d
ON e.DeptID = d.ID
WHERE e.ID IS NULL
OR d.ID IS NULL;+-----------+-----------+ | Name | DeptName | +-----------+-----------+ | Ravi S. | NULL | | NULL | HR | +-----------+-----------+
FULL JOIN vs Other JOIN Types
| JOIN Type | Left Unmatched | Right Unmatched | Both Matched |
|---|---|---|---|
INNER JOIN |
Excluded | Excluded | Included |
LEFT JOIN |
Included (NULL right) | Excluded | Included |
RIGHT JOIN |
Excluded | Included (NULL left) | Included |
FULL JOIN |
Included (NULL right) | Included (NULL left) | Included |
⚠️ MySQL Does Not Support FULL OUTER JOIN
MySQL does not have native FULL OUTER JOIN support. You can simulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION:
SELECT e.Name, d.DeptName
FROM Employees AS e
LEFT JOIN Departments AS d ON e.DeptID = d.ID
UNION
SELECT e.Name, d.DeptName
FROM Employees AS e
RIGHT JOIN Departments AS d ON e.DeptID = d.ID;The UNION combines both results and removes duplicates automatically. PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN natively.
For the complete JOIN syntax reference, see the official MySQL JOIN documentation. In the next chapter, you will learn SQL GROUP BY to group rows and calculate aggregates.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a FULL OUTER JOIN query that shows every employee and every department, along with the department location. Include unmatched rows from both sides.
SELECT e.Name, d.DeptName, d.Location
FROM Employees AS e
FULL OUTER JOIN Departments AS d
ON e.DeptID = d.ID;Expected Output:
+-----------+-----------+-----------+ | Name | DeptName | Location | +-----------+-----------+-----------+ | Priya K. | IT | Delhi | | John D. | Marketing | London | | Arun M. | IT | Delhi | | Emma W. | Sales | Mumbai | | Ravi S. | NULL | NULL | | NULL | HR | New York | +-----------+-----------+-----------+
📝 What You Learned in This Chapter
FULL OUTER JOINreturns all rows from both tables- Unmatched rows on either side are filled with NULL
- It combines LEFT JOIN and RIGHT JOIN behavior
FULL JOINandFULL OUTER JOINare identical- MySQL does not support FULL JOIN natively — use LEFT JOIN + UNION + RIGHT JOIN
- Use WHERE with IS NULL to find unmatched rows on either side
Frequently Asked Questions
FULL OUTER JOIN natively. You can simulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION. PostgreSQL, SQL Server, and Oracle all support FULL OUTER JOIN directly.FULL JOIN and FULL OUTER JOIN are the same. The keyword OUTER is optional and does not change the behavior.WHERE table1.key IS NULL OR table2.key IS NULL after the FULL JOIN. This returns only the rows from either table that had no corresponding match — useful for identifying orphan or missing records.📖 Chapter 24 of 30 — SQL Tutorial on Neotech Navigators
