{"id":107569,"date":"2025-08-26T11:00:00","date_gmt":"2025-08-26T11:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107569"},"modified":"2026-04-14T09:39:55","modified_gmt":"2026-04-14T09:39:55","slug":"the-poor-neglected-full-outer-join","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/the-poor-neglected-full-outer-join\/","title":{"rendered":"Unlocking the Power of FULL OUTER JOIN in SQL: Performance, Use Cases & Examples"},"content":{"rendered":"\n

FULL OUTER JOIN<\/code> returns every row from both tables in a join – matching rows where keys align and filling unmatched columns with NULL<\/code> values where they don’t. Unlike INNER JOIN<\/code>, which returns only rows with a match on both sides, or LEFT JOIN<\/code>, which prioritizes one table, FULL OUTER JOIN<\/code> gives you a complete picture of both datasets simultaneously. It is the right tool when merging partially-overlapping data, building exception reports to identify missing records, or tracking changes across two snapshots of the same dataset.<\/strong><\/p>\n\n\n\n

The JOIN<\/code> statement is one of the most common operations SQL<\/a> developers perform. Yet in a world ruled by Inner and Left Joins,<\/a> the poor Full Outer Join is like Cinderella before the ball \u2013 allowed out on only the rarest of occasions. In an (unscientific) survey I once performed of SQL developers at one Fortune 100<\/a> firm, half of the developers had never used a Full Outer Join in production code, and the rest had used it in a tiny handful of occasions.<\/p>\n\n\n\n

The FULL OUTER JOIN<\/code> (hereafter ‘FOJ’) is generally considered as a tool for rare corner cases such as comparing or merging different table versions, but the objective of this article is to show that its uses go beyond this, even as far as optimizing query performance in cases that most people would never consider. The FOJ is definitely an underappreciated tool. Cinderella has some tricks up her sleeve.<\/p>\n\n\n\n

Understanding SQL Join Types<\/h2>\n\n\n\n

Let’s start with a quick review of the various join types \u2026 and how better to do that than with Venn diagrams? The image below illustrates a basic key value join between two tables:<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

An INNER JOIN<\/code> on two tables returns all values common between them. (The ‘values’ in this case are those specified in the join condition).<\/em><\/p>\n\n\n\n

A LEFT JOIN<\/code> adds non-matching row from Table A, whereas a RIGHT JOIN<\/code> yields the mirror image of that. Finally, the FULL OUTER JOIN<\/code> returns everything: all rows from both tables are included, whether or not they match.<\/p>\n\n\n\n

When to Use FULL OUTER JOIN<\/strong><\/h2>\n\n\n\n

The most common use of the FOJ is in merging tables that contain some degree of overlap. Let’s consider a simple example: a school system database holds parents in one table and teachers in another. We want to combine these into an email list containing both — but some parents are also teachers, and some teachers are also parents. Because some rows exist in both tables, we can’t simply UNION<\/code> the two result sets together. A UNION DISTINCT<\/code> might work, but not only is this expensive performance-wise for larger tables, it requires that all retrieved columns be identical, which may not be the case.<\/p>\n\n\n\n

With a FOJ, though, this becomes easy. Our (unrealistically simple) example tables look like this:<\/p>\n\n\n\n

CREATE TABLE teachers (\n   email varchar(256), \n   workphone varchar(256) \n);\nCREATE TABLE parents( \n   email varchar(256), \n   cellphone varchar(256) \n);\n<\/pre><\/div>\n\n\n\n
We’ll assume two contacts are identical if their emails match, so our join condition is on email. We also want a contact phone number, which for teachers should be their work number, and for parents, their cell number:<\/p>\n\n\n\n
SELECT t.workphone, t.email, p.email, p.cellphone\nFROM Teachers t\nFULL OUTER JOIN Parents p ON t.email = p.email\n<\/pre><\/div>\n\n\n\n
(If you wish to experiment with this yourself, Appendix I contains a script that creates these tables and loads sample data)<\/em>.<\/p>\n\n\n\n
Structuring the query this way works, but gives a lot of null values — any row that doesn’t exist in both tables will contain nulls on one side:<\/p>\n\n\n\n
\"Any<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If this is a problem, we can eliminate it by using COALESCE<\/code> to “fold together” the left and right halves of the result set:<\/p>\n\n\n\n
SELECT COALESCE(t.workphone,p.cellphone), COALESCE(t.email, p.email)\nFROM Teachers t\nFULL OUTER JOIN Parents p ON t.email = p.email\n<\/pre><\/div>\n\n\n\n
This yields a much more compact result set:<\/p>\n\n\n\n
\"Using<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Fast, simple and concise: how can you not appreciate the FULL OUTER JOIN<\/code>?
<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
The fast and easy way to write, format and debug SQL<\/h3>\n                
\n                                            SQL Prompt, now with AI capabilities,  works alongside you to make your SQL coding fast, accurate and effortless.                                    <\/div>\n            <\/div>\n                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Real-World Examples of FULL OUTER JOIN in Action<\/strong><\/h2>\n\n\n\n
Now let’s look at a problem for which you might not consider a FOJ. Imagine a table of vehicles held in an extended-stay parking lot. Once per day an attendant goes through the lot and inventories all the vehicles, recording the date and tag number. Our table looks like this:<\/p>\n\n\n\n
CREATE TABLE parked_vehicles (\n  day          DATE,\n  veh_tag_no   VARCHAR(32) \n);\n<\/pre><\/div>\n\n\n\n
The task is to write a query that returns all the vehicles that either entered or exited the lot today.  In other words, we want two sets of rows:<\/p>\n\n\n
\n