A FULL JOIN (or FULL OUTER JOIN) in SQL is used to combine records from two tables, showing all rows from both tables and matching them where possible. If there is no match, the result will contain NULL values for columns from the table where there is no match.
Here's the general syntax for a FULL JOIN:
SELECT columns FROM table1 FULL JOIN table2 ON table1.common_column = table2.common_column;
Example
Assume we have two tables, employees and departments:
employees
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | NULL |
departments
| department_id | department_name |
|---|---|
| 10 | HR |
| 20 | Engineering |
| 30 | Marketing |
If you want to get a combined list of all employees and all departments, regardless of whether they are linked, you can use a FULL JOIN:
SELECT e.employee_id, e.name, e.department_id, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;
Result
| employee_id | name | department_id | department_name |
|---|---|---|---|
| 1 | Alice | 10 | HR |
| 2 | Bob | 20 | Engineering |
| 3 | Charlie | NULL | NULL |
| NULL | NULL | 30 | Marketing |
In this result:
- Rows where there is a match between
employeesanddepartmentsshow values from both tables. - Rows from
employeesthat have no matching department showNULLfor the department columns. - Rows from
departmentsthat have no matching employee showNULLfor the employee columns.
This join is useful when you need to see all records from both tables, including those that do not have corresponding matches.