What Is Full Join In Sql

4 min read

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 NULL values 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 NULL department information.
  • Diana with NULL department information because department 30 is missing.
  • A row for Support with NULL employee 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

Dropping Now

Freshly Published

If You're Into This

Round It Out With These

Thank you for reading about What Is Full Join In Sql. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home