What FULL JOIN Is and When to Use It

Learn about FULL JOIN, how to implement it, how it compares with the other types of SQL JOINs, and some of its unique use cases.

Before we jump into FULL JOINs, let’s quickly recap what an SQL JOIN is. At its core, a JOIN combines data from two or more tables within a database. Tables are usually linked together using unique identifiers in each table, i.e. primary and foreign keys.

The best way to review and practice all types of JOINs in SQL is our interactive SQL JOINs course. It contains over 90 hands-on practice exercises and covers all the different types of SQL JOINs. If you're looking for a handy reference for SQL JOINs, check out our SQL JOINs Cheat Sheet which provides a detailed overview of the different types of JOINs.

To demonstrate a simple SQL JOIN – also known as an INNER JOIN – in action, let’s consider the Products and OrderDetails tables from the well-known Northwind sample database. The Products table contains a list of all products and the OrderDetails table contains a list of all recent orders.

Here’s the query:

SELECT 
Products.ProductName, OrderDetails.OrderID, OrderDetails.Quantity
FROM Products
JOIN OrderDetails 
ON Products.ProductID=OrderDetails.ProductID
ORDER BY Quantity Desc;

And the result:

The above example uses ProductID (which is available in both tables) as the key to join the two tables and display each order sorted in descending order by Quantity. Note that a simple JOIN like the one above only returns matching rows from the two tables. In this case, the results would only include recently-ordered products and orders that are linked to a valid ProductID.

FULL JOIN: An Introduction

Unlike INNER JOIN, a FULL JOIN returns all the rows from both joined tables, whether they have a matching row or not. Hence, a FULL JOIN is also referred to as a FULL OUTER JOIN. A FULL JOIN returns unmatched rows from both tables as well as the overlap between them. When no matching rows exist for a row in the left table, the columns of the right table will have NULLs for those records. Similarly, when no matching rows exist for a row in the right table, the columns of the left table will have NULLs.

To demonstrate the difference between a simple SQL JOIN and a FULL OUTER JOIN, let’s consider the Projects and Employees tables as shown below. The Projects table contains a list of all projects undertaken by the company (in-house projects as well as outsourced), while the Employees table contains a list of all current employees and specifies if they are involved in any company projects.

If we were to do JOINs between these two tables by matching the tables through ProjectID as the primary key, the results of a simple INNER JOIN and a FULL OUTER JOIN would be quite different. An INNER JOIN would output a table containing only the results where there is a match between the two corresponding entries in both tables. The INNER JOIN result contains employee names and the corresponding project names:

On the other hand, a FULL OUTER JOIN will output both tables’ data, irrespective whether it has a match in the other table:

The result of FULL JOIN contains all employee names, regardless of whether they are assigned to a project, and all project names, even if there are no employees assigned to that project.

FULL JOIN Syntax

The basic syntax of a FULL JOIN is similar to other types of JOINs:

SELECT 
	left_table.column1, right_table.column2,...
FROM left_table
FULL OUTER JOIN right_table 
ON left_table.key = right_table.key;

The OUTER keyword is optional and may be omitted.  

Example

Now, let’s run through that example of a FULL OUTER JOIN that shows both the EmployeeName and ProjectName again. Here's the query:

SELECT 
Employees.EmployeeName, Projects.ProjectName
FROM Employees
FULL OUTER JOIN Projects 
ON Employees.ProjectID=Projects.ProjectID
ORDER BY EmployeeID;

And the result:

From the result table, we see that a FULL JOIN also outputs NULL results from both the  Projects and Employees tables – even though there were no matches on the other table. NULLs in the ProjectName column indicate that employee is not specifically assigned to any project and likely serves other functions in the company. On the other hand, a NULL in the EmployeeName column suggests that the specific project is likely outsourced and not directly managed by any company employee. Alternatively, a NULL value could also point to a potential error or inadvertent data deletion in the database or the system. (We will discuss this more below.)

It’s good to note that some databases, like MySQL, do not support full joins. In that case, you can use the UNION ALL operator to combine the LEFT JOIN and RIGHT JOIN.

Our prior article on SQL JOINs has a few more examples that you can examine. Our SQL JOINs course also provides comprehensive examples of FULL JOINs and includes practical exercises to polish your knowledge.

FULL JOIN Use Cases

Compared to the other types of SQL JOINs, you will likely use FULL JOIN less frequently. That being said, it is a very handy tool for a few unique situations. Some of its use cases are:

• To retrieve all records from both tables, whether there is a match or not. This is the example we saw above.
• To find mismatched or orphaned data from both tables. An orphanedrecord is a record whose foreign key value references a non-existent primary key value; this often happens in rapidly scaling or very old systems.
• To run exception reports. This is a form of data analysis comparing one set of data against a desired/expected base data set and highlighting items that don't match.

Although the application of FULL JOINs is quite unique, they are a great way to find and diagnose potential data integrity issues.

Types of JOINs: Recap

As we had mentioned above, there are a few more types of JOINs.

An INNER JOIN returns rows when the JOIN condition is satisfied in both the left and right tables. In other words, it returns only the matched records from the tables. This is the most common type of SQL JOIN and is the default when you haven’t specified the type of JOIN.

An OUTER JOIN returns all the rows from one table and some or all of the rows from another table (depending on the type of OUTER JOIN). Aside from the FULL OUTER JOIN, there are two other types:

• A LEFT OUTER JOIN returns all rows from the left table, even if no matching rows were found in the right table. If there are no matching records in the right table, the query will return NULL values for those columns.
• A RIGHT OUTER JOIN returns all rows from the right table. If there are no matching records in the left table, NULL values are returned for those columns – the reverse of a LEFT JOIN.

A CROSS JOIN (also called a Cartesian JOIN) returns every possible combination of rows from the tables that have been joined. Since it returns all possible combinations, this is the only JOIN type that does not need a JOIN condition and therefore does not require an ON clause.

For a more detailed look at each of the JOIN types, check out the article SQL JOIN Types Explained. Additionally, our SQL JOINs course covers all the different types and use cases of JOINs in great detail. It also goes into more advanced topics, such as non-equi JOINs and multiple JOINs.

JOINs are one of the most fundamental and commonly used features of SQL and an essential part of any SQL user’s toolkit. They’re featured in common interview questions as well; see our article The Top 10 SQL JOIN Interview Questions and How to Answer Them for helpful tips.

Increase Your Knowledge of SQL FULL JOINs

In this tutorial, you’ve learned the specifics of SQL FULL JOINs and their core use cases in diagnosing data integrity. You’ve also seen a brief comparison between FULL JOINs and the other types of JOINs.

To really get a grasp of FULL JOINs, you need to dive into the deep end, practice some exercises, and get challenged. I highly recommend trying our SQL JOINs course as you move to the next level of this very important SQL feature.

If you're looking for a handy reference for SQL JOINs, check out our SQL JOINs Cheat Sheet which provides a detailed overview of the different types of JOINs.