What Is FULL OUTER JOIN in SQL?

If you’ve been learning SQL and wondering what is FULL OUTER JOIN in SQL, I will try to explain in this post. FULL OUTER JOIN is a powerful tool that lets you retrieve all records from two tables, whether they matching records or not. This makes it incredibly useful when you want a complete picture of data across two datasets, even when some values don’t line up perfectly. I often use FULL OUTER JOIN for data quality testing, especially when I am trying to compare two data sources (like staging vs. production, source vs. target, or old vs. new data) and need to find any of the following:

• Mismatched rows
• Missing records on either side
• Unexpected duplicates
• Data drift or structural changes

In this blog post, I’ll break down what FULL OUTER JOIN does, how it works, and show you examples to make it easy to understand and use.

What Is FULL OUTER JOIN in SQL?

In SQL, a FULL OUTER JOIN returns all records from both tables. Where there is a match between columns from both tables, it shows the matched data. Where there is no match, it returns NULL values for the missing side.

Think of it as a combination of LEFT JOIN and RIGHT JOIN. You get everything from both tables, with NULLs filled in where there’s no match.

Syntax of FULL OUTER JOIN

Here’s what the syntax of FULL OUTER JOIN look like:

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

Data Preparation

Let’s first prepare our database by adding a new table called EmployeeBonus. Run the following query in SQL Server Management Studio:

-- Create the EmployeeBonus table
CREATE TABLE EmployeeBonus (
    EmployeeID INT,
    Year INT,
    Amount DECIMAL(10, 2)
);

-- Insert records for EmployeeID 1 to 12
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (1, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (2, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (3, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (4, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (5, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (8, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (9, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (10, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (11, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (12, 2025, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));

The first part of this query will create the table, while the second part will insert records for employee bonuses, for year 2025 with random amounts for bonus.

If you run a SELECT statement on this table, you should see something similar to this:

Now we have two tables:

• Employees
  • EmployeeID
  • LastName
  • FirstName
  • Title
  • …
• EmployeeBonus
  • EmployeeID
  • Year
  • Amount

Let’s say we want to see all employees and all their bonuses. In this case, we will run the following query:

SELECT emp.EmployeeID, emp.FirstName, emp.LastName, empbonus.Amount 
FROM Employees emp
FULL OUTER JOIN EmployeeBonus empbonus
ON emp.EmployeeID = empbonus.EmployeeID

Here is the Sample Output:

• Nancy has a matching bonus (joined row).
• Michael didn’t receive the bonus (NULLs on the right side).
• Bonus with amount of 12139.35 doesn’t match any employee (NULLs on the left side).

Here is what this looks like in SSMS:

When Should You Use FULL OUTER JOIN?

• When you need to see everything from both tables.
• When analyzing gaps in relationships—e.g., missing employee records or orphaned bonuses.
• When doing data audits or reconciliation reports.

What’s the Difference from Other Joins?

Tips When Using FULL OUTER JOIN

• Always check for NULLs in your result to understand unmatched records.
• Not all databases support FULL OUTER JOIN natively (though SQL Server and PostgreSQL do).

Conclusion

Now you know what is FULL OUTER JOIN in SQL and how to use it to retrieve all records from both tables. Whether you’re performing data reconciliation or just trying to explore unmatched records, FULL OUTER JOIN can be an invaluable tool in your SQL toolkit.

Want more SQL tutorials like this? Stick around – I’m covering more JOIN types and practical SQL tips in future posts!

Recently I wrote a blog post on What Is the Difference Between LEFT JOIN and RIGHT JOIN, so make sure you check it out.

If you would like to learn more about SQL, make sure you read this book: T-SQL Fundamentals (Developer Reference)