A full join in SQL is a powerful relational operation that combines rows from two or more tables based on a related column, while preserving unmatched rows from both sides. Even so, unlike an inner join, which returns only rows with matching values, a full join returns all rows from the left table and all rows from the right table. When a row has no match in the other table, the missing side is filled with NULL values. This makes a full join especially useful when you need a complete view of data from both tables, including records that do not have corresponding entries elsewhere.
People argue about this. Here's where I land on it That's the part that actually makes a difference..
What Is a Full Join in SQL?
A full join in SQL, also commonly called a FULL OUTER JOIN, is a type of outer join that returns:
- All rows from the first table.
- All rows from the second table.
- Matching rows combined into a single result row.
- Unmatched rows from either table, with
NULLvalues where data is missing.
In simple terms, a full join is the combination of a LEFT JOIN and a RIGHT JOIN. A left join keeps all rows from the left table, while a right join keeps all rows from the right table. A full join keeps all rows from both tables Still holds up..
Here's one way to look at it: imagine you have two tables: one containing employees and another containing departments. Some employees may not be assigned to a department, and some departments may not have any employees. If you use a full join, you can retrieve all employees and all departments in one result set, even if some records do not match That alone is useful..
Full Join Syntax
The basic syntax for a full join in SQL is:
SELECT column_list
FROM table1
FULL OUTER JOIN table2
ON table1.matching_column = table2.matching_column;
In many SQL databases, you can also write:
SELECT column_list
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
The keyword OUTER is optional in most database systems that support full joins. To give you an idea, FULL JOIN and FULL OUTER JOIN usually produce the same result Simple as that..
How a Full Join Works
To understand how a full join works, it helps to compare it with other join types.
Suppose you have two tables:
Employees Table
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | NULL |
| 4 | Diana | 30 |
Departments Table
| department_id | department_name |
|---|---|
| 10 | Sales |
| 20 | Marketing |
| 40 | Support |
In this example:
- Alice belongs to department 10.
- Bob belongs to department 20.
- Charlie has no department.
- Diana belongs to department 30, but department 30 does not exist in the departments table.
- Department 40 exists, but no employee is assigned to it.
If you run a full join between these tables using department_id as the matching column, the result will include:
- Alice with Sales.
- Bob with Marketing.
- Charlie with
NULLdepartment information. - Diana with
NULLdepartment information because department 30 is missing. - A row for Support with
NULLemployee information because no employee is assigned to it.
This is the main strength of a full join: it does not hide missing records from either side Worth keeping that in mind..
Example of a Full Join in SQL
Using the tables above, a full join query would look like this:
SELECT
e.employee_id,
e.employee_name,
d.department_id,
d.department_name
FROM employees AS e
FULL OUTER JOIN departments AS d
ON e.department_id = d.department_id;
The result might look like this:
| employee_id | employee_name | department_id | department_name |
|---|---|---|---|
| 1 | Alice | 10 | Sales |
| 2 | Bob | 20 | Marketing |
| 3 | Charlie | NULL | NULL |
| 4 | Diana | NULL | NULL |
| NULL | NULL | 40 | Support |
Notice how the full join preserves all records. Support appears even though no employee is assigned to it. Charlie appears even though he has no department. Diana appears even though her department ID does not match any department in the departments table.
Some disagree here. Fair enough.
Full Join vs Inner Join
An INNER JOIN returns only rows where there is a match in both tables. If a row exists in one table but not the other, it is excluded from the result.
A