How FULL JOIN works in SQL? Best FULL JOIN examples

How FULL JOIN works in SQL? Best FULL JOIN examples

When working with databases, one of the most essential concepts to understand is how to join tables efficiently to extract meaningful data. SQL offers several types of joins, and today, I’ll be diving deep into FULL JOIN, a powerful but sometimes overlooked technique.

What is FULL JOIN in SQL?

FULL JOIN, also known as FULL OUTER JOIN, is an SQL join type that returns all records from both tables involved in the join. If there is a match, the result combines corresponding rows. If there is no match, NULL values fill the gaps.

FULL JOIN is particularly useful when you want a complete view of both tables, including records that may not have corresponding matches in the other table.

FULL JOIN Syntax

The basic syntax of a FULL JOIN looks like this:

SELECT 
    table1.column1, table1.column2, 
    table2.column1, table2.column2
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

The key component here is the ON clause, which defines how the two tables relate based on a common column.

Example Database Tables

Let’s consider two simple tables: employees and departments.

employees Table

employee_id name department_id
1 John Doe 10
2 Jane Smith 20
3 Emma Brown NULL

departments Table

department_id department_name
10 IT
20 HR
30 Marketing

Applying FULL JOIN

Now, let’s apply a FULL JOIN to link these two tables based on their department_id column.

SELECT 
    employees.employee_id, employees.name, employees.department_id, 
    departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;

The result of this query will show:

employee_id name department_id department_name
1 John Doe 10 IT
2 Jane Smith 20 HR
3 Emma Brown NULL NULL
NULL NULL 30 Marketing

Breaking Down the Results

  • The first two rows join successfully, as they have matching department IDs.
  • The third row contains an employee with no associated department, resulting in NULL values from the departments table.
  • The last row includes a department that has no employees associated with it, so employee-related columns are NULL.

When to Use FULL JOIN?

FULL JOIN is particularly useful in scenarios such as:

  1. Generating reports that need a full dataset from two tables.
  2. Identifying records that don’t have a related entry in another table.
  3. Comparing datasets where missing values could indicate errors or incomplete data.

Performance Considerations

While FULL JOIN is powerful, it’s important to be mindful of performance issues:

  • If the datasets are large, FULL JOIN may be slower than INNER or LEFT/RIGHT JOINs.
  • Indexing the joined columns can significantly improve performance.
  • Consider filtering unnecessary rows using a WHERE clause post-join to minimize processing load.

Conclusion

Understanding how FULL JOIN works in SQL opens up opportunities to extract comprehensive insights from your databases. Whether you’re dealing with reporting, troubleshooting missing data, or comparing datasets, knowing when and how to use FULL JOIN can be a game-changer.

 

Other interesting article:

How RIGHT JOIN works in SQL? Best RIGHT JOIN examples