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 JOIN
s in SQL is our interactive SQL JOINs course. It contains over 90 hands-on practice exercises and covers all the different types of SQL JOIN
s. 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.
PRODUCTS | |||||
---|---|---|---|---|---|
ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 |
3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 |
... | ... | ... | ... | ... | ... |
ORDERDETAILS | |||||
---|---|---|---|---|---|
OrderDetaiID | OrderID | ProductID | Quantity | ||
1 | 10248 | 11 | 12 | ||
2 | 10248 | 42 | 10 | ||
3 | 10248 | 72 | 5 | ||
... | ... | ... | ... |
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:
ProductName | OrderID | Quantity |
---|---|---|
Pâté chinois | 10398 | 120 |
Steeleye Stout | 10286 | 100 |
Sirop d'érable | 10440 | 90 |
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.
PROJECTS | |||
---|---|---|---|
ProjectID | ProjectName | CostYTD | BusinessUnit |
1 | CRM Upgrade | 45640 | Customer Relations |
2 | Cybersecurity Protocol Implementation | 80200 | Cybersecurity |
3 | HQ Office Renovations | 145000 | Facilities |
4 | ERP Integration | 110000 | Corporate |
5 | Database Stack Upgrade | 25000 | Engineering |
... | ... | ... | ... |
15 | Automated QA | 10000 | Engineering |
EMPLOYEES | ||||
---|---|---|---|---|
EmployeeID | EmployeeName | ProjectID | StartDate | BusinessUnit |
001 | Albert Ross | 3 | 2012-02-11 | Facilities |
002 | Hummer Baird | NULL | 2012-02-11 | Corporate |
003 | Matthias Dias | 15 | 2012-07-15 | Engineering |
004 | Al Cooper | 1 | 2014-04-15 | Customer Relations |
005 | Macron Ralf | NULL | 2014-04-15 | Legal |
... | ... | ... | ... | ... |
025 | Hamza Imran | NULL | 2020-07-11 | Engineering |
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:
EmployeeName | ProjectName |
---|---|
Albert Ross | HQ Office Renovations |
Matthias Dias | Automated QA |
Al Cooper | CRM Upgrade |
On the other hand, a FULL OUTER JOIN
will output both tables’ data, irrespective whether it has a match in the other table:
EmployeeName | ProjectName |
---|---|
Albert Ross | HQ Office Renovations |
Hummer Baird | NULL |
Matthias Dias | Automated QA |
Al Cooper | CRM Upgrade |
.... | ... |
Hamza Imran | NULL |
NULL | ERP Integration |
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:
EmployeeName | ProjectName |
---|---|
Albert Ross | HQ Office Renovations |
Hummer Baird | NULL |
Matthias Dias | Automated QA |
Al Cooper | CRM Upgrade |
.... | ... |
Hamza Imran | NULL |
NULL | ERP Integration |
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 aLEFT 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.