A Comprehensive Step-by-Step Guide to Full Outer Join in SQL

The FULL OUTER JOIN is a very useful but often overlooked SQL join. In this article, we’ll discuss how it works, when to use it, and give practical examples.

Ever wondered how to combine data from two tables, even when some records don't have a match? SQL's FULL OUTER JOIN lets you do just that by merging datasets while preserving unmatched rows.

This join type ensures no data is left behind, making it a valuable tool for comprehensive reporting. In this guide, I'll walk you through everything you need to know, from syntax to real-world use cases.

What is a Full Outer Join?

The FULL OUTER JOIN is one among several types of SQL JOINS. More precisely, it’s an outer join type, the other two being LEFT JOIN and RIGHT JOIN.

It can be seen as LEFT JOIN and RIGHT JOIN combined in a single operation. How so?

LEFT JOIN returns all records from the first (left) table and only the matching rows from the second (right) table. RIGHT JOIN is the opposite – returns all records from the second (right) table and only the matching rows from the first (left) table.

If we combine these two, we get a join that returns all records from both tables, filling in NULL values where no match exists. That is FULL OUTER JOIN.

It’s useful when you want a complete view of two datasets, even if some entries have no corresponding record in the other table. For example, if you have a table of customers and another of orders, a full outer join ensures you see all customers (even those who haven't ordered) and all orders (even those without a recorded customer).

Syntax of a Full Outer Join

The FULL OUTER JOIN syntax follows this standard SQL format.

SELECT column_names
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

You probably noticed that the syntax is the same as for all other outer joins or an inner join:

• Reference the first table in FROM
• Write a join type (FULL OUTER JOIN)
• Reference the second table
• Join the tables on the common column using the ON clause

How Does a Full Outer Join Work?

A FULL OUTER JOIN combines both LEFT JOIN and RIGHT JOIN, meaning:

1. It goes through the left table and for each of its rows checks if there are matching rows in the right table:

• IF YES – Returns a matching row from the right table
• IF NO – Fills missing value with NULL

2. Adds the remaining rows from the right table that have no matching rows in the left table.

You can see a visual representation in the GIF below.

Let’s now move on to a simple example and code.

Example

We’ll join two tables. The first is called customers.

The second table is orders.

Joining the two tables with FULL OUTER JOIN looks like this.

SELECT c.customer_id, 
 c.first_name,
 c.last_name,
 o.order_id,
 o.product
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;

The query will give you the output that shows all customers and all orders, even if there’s no direct match.

When to Use a Full Outer Join?

Use FULL OUTER JOIN when:

• You need a complete dataset from two tables.
• You want to track missing data from either table.
• You’re working with reports where you need to include both matching and non-matching records.

Let’s walk through an example from common SQL JOIN interview questions to see how this works in practice.

Real-World Example

This is a question from Nvidia and Microsoft interviews.

Link to the question: https://platform.stratascratch.com/coding/10163-product-transaction-count

We’ll change the requirements a little. This will stay the same: we’ll find the number of transactions for each product, output the product name and the number of transactions, and order the output by the product ID in ascending order, but we’ll not ignore products without transactions.

We have these two tables at our disposal: excel_sql_inventory_data…

…and excel_sql_transaction_data.

As we’ll write a code that will also include products without transactions, we’ll use FULL OUTER JOIN instead of INNER JOIN, which the official solution uses.

We start by selecting the product name and calculating the number of transactions with the COUNT() aggregate function.

Next, we reference the table excel_sql_inventory_data in the FROM clause and FULL OUTER JOIN it with excel_sql_transaction_data. The tables are joined on the product_id column.

Then, we group the output by the product name and ID.

Finally, the output is sorted ascendingly by product ID.

Run the code in the editor above to see the output.

Common Pitfalls and How to Avoid Them

There are two main pitfalls you’ll want to avoid when using FULL OUTER JOIN.

Pitfall #1: Large Dataset Performance Issues
Solution: Use indexing on join columns.

Pitfall #2: NULL Value Handling
Solution: Use COALESCE() to replace NULL values.

Real-World Example

I’ll show you an example of how to deal with NULLs using COALESCE(). Here’s a question from Etsy and Amazon interviews.

Link to the question: https://platform.stratascratch.com/coding/10183-total-cost-of-orders

The first table I’ll use is customers.

The second one is orders.

We’ll change this question slightly, too. Yes, we’ll find the total cost of each customer’s orders, but we also want to show those customers with no orders; we will show their order cost as 0. We’ll output customer’s ID, first name, last name, and the total order cost, while ordering records by customer’s last name alphabetically.

We first select customers’ IDs, first, and last names.

Then, we use the SUM() function to calculate the total amount spent by each customer.

Next, we FULL OUTER JOIN the orders and customers tables on the customer ID.

We can now group the output by customer’s IDs, first, and last names, and sort it by last names alphabetically.

SELECT c.id,
       c.first_name,
       c.last_name,
       SUM(total_order_cost) AS total_spent
FROM orders o
FULL OUTER JOIN customers c ON c.id = o.cust_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY c.last_name ASC;

This is the code output.

As you can see, there are NULLs representing customers without orders. The thing is we want these to be shown as zeros. For example, so it’s clear NULL means zero spending, not that the data is missing or is unknown. We’ll need help from COALESCE().

The code below didn’t change much. We only embedded the SUM() function in COALESCE(), a function that returns the first non-null value from a list of expressions. As a second argument in COALESCE() – the value that’ll replace NULL value – we specify zero.

Run the code to see the output.

Advanced Use Cases and Techniques

In this section, I’ll discuss two advanced techniques of using FULL OUTER JOIN.

1. Using WHERE Clause to Filter NULLs

Sometimes, you may need to find only unmatched records from either table. A FULL OUTER JOIN helps, but you need to filter further to show only the non-matching records. This is where you need to use WHERE.

Comparing two tables that way is useful for data validation during data migrations; it’s a simple method for detecting missing or inconsistent records between two datasets.

Real-World Example

We’ll demonstrate this specific use of FULL OUTER JOIN in the Wine Magazine interview question example.

Link to the question: https://platform.stratascratch.com/coding/10025-find-all-possible-varieties-which-occur-in-either-of-the-winemag-datasets

You’re given two datasets: winemag_p1 and winemag_p2.

This is the first dataset’s preview.

Here’s the second dataset.

We won’t follow the question’s requirements. Instead, we’ll write code that shows all wine varieties recorded in only one of the two datasets.

To achieve that, we need to FULL OUTER JOIN the given tables. We join them on the variety column. To output records that can be found in only one of the two tables, we filter data using WHERE. The filtering condition is that the variety column from either the first or the second table is NULL.

Finally, we select the variety column from both tables.

SELECT p1.variety,
       p2.variety
FROM winemag_p1 p1
FULL OUTER JOIN winemag_p2 p2
ON p1.variety = p2.variety
WHERE p1.variety IS NULL OR p2.variety IS NULL;

In the output, the first column shows all the wine varieties found in the first table, but not the second. The second column shows the opposite.

The output has too many records to show them all. So, I will display the first five rows, and then the last five rows. This is to prove that the output really shows data in the first column and NULLs in the second, and vice versa.

Here are the first five rows.

Here are also the last five rows.

The output is functional but can be improved to be more readable. The code below employs COALESCE() and DISTINCT() to show all wine varieties in one column and remove duplicates.

Run the code above to see the improved output.

2. Using CASE WHEN for Custom Conditional Outputs

You can classify matched and unmatched records by adding a new column that flags whether a match was found. This is where CASE WHEN comes in.

Real-World Example

The example is based on the IBM interview question.

Link to the question: https://platform.stratascratch.com/coding/10542-interaction-summary

There are two tables. The first one is customer_interactions.

The second table is user_content.

We will write code that extracts customer IDs from both tables. Then, we will label each row using CASE WHEN, depending on whether it has matching or non-matching values.

In the code, we use FULL OUTER JOIN to join the tables on the customer ID, and order the output by the customer IDs from the customer_interactions table.

The SELECT statement will output the customer IDs from the tables. The third output column is match_status, whose output will change depending on the conditions in CASE WHEN.

Speaking of which, records where the customer ID is missing from the table customer_interactions will be labeled as 'Customer without interaction'. When the customer ID is missing from user_content, those rows will get the 'Customer without content' label.

Run the code in the code editor to see the output.

Alternatives to Full Outer Join

The most straightforward alternative to FULL OUTER JOIN is using set operators, two of which are suitable for this.

Here’s a quick overview of how these approaches compare with FULL OUTER JOIN.

I’ll explain each of those approaches using the generic example we used earlier with FULL OUTER JOIN.

SELECT c.customer_id, 
 c.first_name,
 c.last_name,
 o.order_id,
 o.product
FROM customer c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;

Using UNION

This approach is based on writing two queries – one with LEFT JOIN, the other with RIGHT JOIN – then merging their outputs using UNION.

Since we’re using UNION, it will automatically remove duplicates, if there are any.

Example

The LEFT JOIN ensures all customers appear, even if they don’t have an order. The RIGHT JOIN ensures all orders appear, even if they don’t have a customer.

We write these two separate SELECT statements and merge their outputs with UNION.

SELECT c.customer_id, 
       c.first_name,
       c.last_name,
       o.order_id,
       o.product
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.customer_id, 
       c.first_name,
       c.last_name,
       o.order_id,
       o.product
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Using UNION ALL

This is similar to the UNION approach, but UNION ALL is used to avoid duplicate elimination.

Example

The last code can be rewritten like this using UNION ALL instead of UNION. It’s the same code, but with a different set operator.

SELECT c.customer_id, 
       c.first_name,
       c.last_name,
       o.order_id,
       o.product
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION ALL

SELECT c.customer_id, 
       c.first_name,
       c.last_name,
       o.order_id,
       o.product
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- this is optional, use when you want to remove duplicates

Conclusion

A FULL OUTER JOIN is your ultimate safety net—it ensures no record is left behind, whether it finds a match or not.

When used strategically, it can transform disjointed datasets into meaningful insights, making it invaluable for reporting, data analysis, and business intelligence. Mastering these techniques will make you a more efficient SQL developer, unlocking cleaner, faster, and more insightful data handling.

FAQs

1. What is the difference between a full outer join and an inner join?

An inner join returns only matching records, whereas a full outer join includes all records from both tables, filling unmatched values with NULL.

2. How is a full outer join different from a union?

A UNION combines results from multiple SELECT queries, while a FULL OUTER JOIN merges datasets based on a condition. Essentially, you can look at FULL OUTER JOIN as a horizontal joining method (columns from one table besides the other table’s columns), while UNION is vertical (records from one query are added below the other query’s records).

3. What does a NULL value mean in the result of a full outer join?

A NULL means the record exists in one table but has no match in the other.

4. Is a full outer join supported in all databases?

Not all databases support it directly (e.g., MySQL). Workarounds like UNION of LEFT and RIGHT JOINs are used.

5. How can I optimize a full outer join for large tables?

• Index join columns.
• Limit results using WHERE and LIMIT.
• Use COALESCE() to clean up NULL values.

6. Can I combine a full outer join with other joins?

Yes! You can mix it with INNER, LEFT, RIGHT, or CROSS JOIN for complex queries.

7. What happens if multiple rows match in a full outer join?

You get multiple rows in the result, reflecting all possible matches.