A FULL OUTER JOIN is a type of join in SQL that combines the results of both a LEFT JOIN and a RIGHT JOIN. It retrieves all rows from both tables involved in the join. If there are matching rows between the two tables, the results include those as well. If there are unmatched rows in one or both tables, those rows will still be included in the result set, with NULL values for the columns from the table where there is no match.
Syntax
The basic syntax for a FULL OUTER JOIN is as follows:
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
Example
Suppose you have two tables:
1. Employees
+----+---------+
| ID | Name |
+----+---------+
| 1 | John |
| 2 | Jane |
| 3 | Mike |
+----+---------+
2. Departments
+------+------------+
| DeptID | DeptName |
+------+------------+
| 1 | HR |
| 2 | IT |
| 4 | Marketing |
+------+------------+
If you want to combine these two tables based on a common column (for example, ID from Employees and DeptID from Departments), your query might look like this:
SELECT Employees.ID, Employees.Name, Departments.DeptID, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.ID = Departments.DeptID;
Expected Result
The output of the above query would look like this:
+------+---------+--------+------------+
| ID | Name | DeptID | DeptName |
+------+---------+--------+------------+
| 1 | John | 1 | HR |
| 2 | Jane | 2 | IT |
| 3 | Mike | NULL | NULL |
| NULL | NULL | 4 | Marketing |
+------+---------+--------+------------+
Explanation of Results
- The rows where there are matches (John with HR, and Jane with IT) include data from both Employees and Departments.
- Mike is included with NULL values for DeptID and DeptName because there is no matching department.
- The department Marketing is included with NULL values for ID and Name because there is no matching employee.
By using FULL OUTER JOIN, you ensure that all data from both tables is included in the result set, regardless of whether there's a match on the join condition.