A full outer join in SQL combines the results of both left and right outer joins. It returns all records when there is a match in either left or right table records. If there are no matches, NULL values are returned for the table that does not have a match.
Here’s how you can perform a full outer join in SQL with the standard syntax:
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
Breakdown of the Query:
- SELECT \*: This part specifies that you want to retrieve all columns from the result.
- FROM table1: This indicates the first table in the join.
- FULL OUTER JOIN table2: This specifies that you are performing a full outer join with the second table (in this case, table2).
- ON table1.common_column = table2.common_column: This condition specifies the columns on which the join is based (i.e., the common column where the join occurs).
Example:
Consider two tables, employees and departments:
employees
| id | name | department_id |
|----|-----------|---------------|
| 1 | John Doe | 10 |
| 2 | Jane Smith| 20 |
| 3 | Alice Lee | NULL |
departments
| department_id | department_name |
|---------------|------------------|
| 10 | HR |
| 20 | Sales |
| 30 | IT |
A full outer join query for the employees and departments tables could look like this:
SELECT e.id, e.name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
Result:
The result of the above query would be:
| id | name | department_id | department_name |
|----|-----------|---------------|------------------|
| 1 | John Doe | 10 | HR |
| 2 | Jane Smith| 20 | Sales |
| 3 | Alice Lee | NULL | NULL |
| NULL | NULL | 30 | IT |
In this result:
- John Doe and Jane Smith have corresponding department names.
- Alice Lee does not belong to any department (department_id is NULL), so the department name is also NULL.
- The department IT has no employees, so the employee fields are NULL.
Note:
Make sure that your SQL database supports full outer joins, as some database management systems (DBMS) like MySQL do not support them directly. In such cases, you might need to simulate a full outer join using a combination of left and right joins along with a union. Here’s how it can be done in MySQL:
SELECT e.id, e.name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.id, e.name, e.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;