FULL JOIN

A FULL JOIN is a type of SQL join that returns all records from both tables involved. If there is a match between the tables, it combines the matching rows. If there is no match, it still returns all rows from both tables, with NULL values in places where there is no corresponding data.

Key Points

• Combines the results of LEFT JOIN and RIGHT JOIN.
• Includes all rows from both tables.
• Fills with NULL for missing matches.
• Useful when you need all data from both tables, not just matches.

Syntax

The basic syntax of a FULL JOIN is:

SELECT column_names
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

• table1 and table2 are the names of the tables you want to join.
• common_column is the column that exists in both tables and is used for matching.

Example

Suppose you have two tables:

Employees Table: Contains employee IDs and names
Departments Table: Contains employee IDs and department names

Using FULL JOIN:

SELECT Employees.ID, Employees.Name, Departments.Department
FROM Employees
FULL JOIN Departments
ON Employees.ID = Departments.ID;

Result:

• Shows all employees and their departments.
• Employees without departments will have NULL in the Department column.
• Departments without employees will have NULL in the Name column.

When to Use FULL JOIN

• When you need complete data from both tables.
• When you want to identify unmatched rows in either table.
• In reporting where missing data is important to include.

Summary

• FULL JOIN = LEFT JOIN + RIGHT JOIN combined
• Returns all records from both tables
• NULLs appear for unmatched rows
• Helps in comprehensive analysis of datasets