🔹 FULL JOIN in SQL
Definition:
The FULL JOIN clause returns all records when there is a match in either left or right table. Records without a match in either table will have NULLs for the missing side.
Syntax:
SELECT table1.column, table2.column
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;
Example:
SELECT Employees.name, Departments.department_name
FROM Employees
FULL JOIN Departments
ON Employees.department_id = Departments.department_id;
Sample Employees Table:
| id | name | department_id |
|---|
| 1 | Alice | 101 |
| 2 | Bob | 102 |
| 3 | Charlie | 103 |
Sample Departments Table:
| department_id | department_name |
|---|
| 101 | HR |
| 102 | Engineering |
| 104 | Finance |
Output:
| name | department_name |
|---|
| Alice | HR |
| Bob | Engineering |
| Charlie | NULL |
| NULL | Finance |
Explanation:
The query returns all rows from both Employees and Departments:
- Alice (101) → HR
- Bob (102) → Engineering
- Charlie (103) → No match in Departments → **NULL**
- Finance (104) → No matching employee → **NULL**
FULL JOIN includes all rows from both tables, with NULLs where no match exists.
Usage Tips:
- Use
FULL JOIN when you want all rows from both tables, matched or unmatched.
- Helps identify gaps or mismatches between two related datasets.
Common Mistake:
Assuming FULL JOIN behaves like INNER JOIN. Unlike INNER JOIN, FULL JOIN includes unmatched rows from both sides.
📘 Note: FULL JOIN is also called FULL OUTER JOIN; both mean the same.