SQL Full Join

The FULL JOIN (or FULL OUTER JOIN) keyword returns all rows from both tables, with matching rows from both sides where available. If there is no match, the result is NULL on the side where there is no match.

Syntax

The basic syntax for a FULL JOIN is:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

        

Example

Consider two tables: Employees and Departments. To perform a FULL JOIN on these tables based on the department ID, use the following query:

SELECT Employees.name, Departments.department_name
FROM Employees
FULL JOIN Departments
ON Employees.department_id = Departments.department_id;

        

Explanation

• FULL JOIN: Returns all rows from both tables, with NULLs where there is no match.
• ON: Specifies the condition for the join, matching rows from both tables based on the specified column.
• NULL Values: Rows without a match in one of the tables will have NULL values for columns from that table.