This article first appeared in the SQLServerGeeks Magazine.
Author: Erland Sommarskog
Subscribe to get your copy.
You probably use both inner joins and left outer joins day in and day out, but the occasions when you need to use full outer joins are far less common. In a collection of some 5000 stored procedures I had lying around, I found that were around 14,000 inner joins, 4,280 left outer joins, but only 18 full outer joins.
This month we will look a little closer at this operator, because it’s one you easily can go wrong with, if you are not careful. I will also make some observations that are applicable to the other join types. I will first cover some things about inner joins and left outer joins as this also helps to illustrates some of the issues you can run into with full outer joins.
As a starter, let’s first repeat the different join types. Here are two very simple tables:
CREATE TABLE #a(aid int NOT NULL PRIMARY KEY,
adata nvarchar(20) NOT NULL)
INSERT #a(aid, adata) VALUES (1, N'First'), (2, N'Second')
CREATE TABLE #b(bid int NOT NULL PRIMARY KEY,
bdata nvarchar(20) NOT NULL)
INSERT #b(bid, bdata) VALUES (2, N'Second'),(3, N'Third')
Here is an inner join:
SELECT * FROM #a INNER JOIN #b ON #a.aid = #b.bid
We only get the rows with id = 2, as they are in both tables:
aid adata bid bdata ----------- -------------------- ----------- ------------------- 2 Second 2 Second
If we change INNER JOIN to LEFT OUTER JOIN, both rows on the left side are retained, and we get NULL where a row is missing on the right side:
aid adata bid bdata ----------- -------------------- ----------- -------------------- 1 First NULL NULL 2 Second 2 Second
And if we instead use RIGHT OUTER JOIN, both rows on the right side are retained, and we get NULL for the missing row on the left side:
aid adata bid bdata ----------- -------------------- ----------- -------------------- 2 Second 2 Second NULL NULL 3 Third
In the sample I mentioned above, there was only one single occurrence of a right outer join. A right outer join is the same as a left outer join except that it is written the other way around. I personally find right outer joins very confusing, and I suspect that I am not alone. I will not discuss right outer joins further in this article.
Finally, with FULL OUTER JOIN, all rows on both sides are retained, leaving NULLs on the side where there is no match:
aid adata bid bdata ----------- -------------------- ----------- -------------------- 1 First NULL NULL 2 Second 2 Second NULL NULL 3 Third
Let’s now consider when we actually use these join types in practice and we will start with inner join and left outer join. Here are some tables for an order system, extremely simplified to only have the columns needed for the examples.
CREATE TABLE Customers(CustomerID int NOT NULL,
CustomerName nvarchar(40) NOT NULL,
CONSTRAINT pk_Customers PRIMARY KEY (CustomerID)
)
INSERT Customers (CustomerID, CustomerName)
VALUES(1, N'Garima Kulkarni'), (2, N'Priya Pol'), (3, N'Manisha Mishra')
go
CREATE TABLE DiscountCodes (Code char(6) NOT NULL,
Discount decimal(5, 2) NOT NULL,
CONSTRAINT pk_DiscountCodes PRIMARY KEY (Code)
)
INSERT DiscountCodes(Code, Discount) VALUES ('ABCDEF', 5)
go
CREATE TABLE Orders (OrderID int NOT NULL,
OrderDate date NOT NULL,
CustomerID int NOT NULL,
DiscountCode char(6) NULL,
CONSTRAINT pk_Orders PRIMARY KEY (OrderID),
CONSTRAINT fk_Orders_DiscountCodes
FOREIGN KEY (DiscountCode) REFERENCES DiscountCodes(Code),
CONSTRAINT fk_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
)
INSERT Orders(OrderID, OrderDate, CustomerID, DiscountCode)
VALUES(11000, '2021-10-01', 1, 'ABCDEF'),
(10000, '2020-08-23', 1, NULL),
(10890, '2021-08-19', 2, NULL)
go
CREATE TABLE OrderDetails (OrderID int NOT NULL,
ProductID int NOT NULL,
CONSTRAINT pk_OrderDetails PRIMARY KEY (OrderID, ProductID),
CONSTRAINT fk_OrderDetails_Order
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
)
INSERT OrderDetails(OrderID, ProductID)
VALUES (11000, 1), (11000, 2),
(10000, 3),
(10890, 1), (10890, 4), (10890, 5)
Here is a query that illustrates the use of inner and left outer joins.
SELECT O.OrderID, O.OrderDate, C.CustomerName, OD.ProductID,
O.DiscountCode, D.Discount
FROM Orders O
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
LEFT JOIN DiscountCodes D ON D.Code = O.DiscountCode
ORDER BY O.OrderID, O.OrderDate
| Note: I have left out OUTER here from LEFT OUTER JOIN, as makes it easier to produce a nicely aligned query text that is easy to read |
There is an inner join from Orders to OrderDetails, because an order without details would be an anomaly. And we can make the join to Customers an inner join, since CustomerID is not nullable and there is also a foreign-key constraint. Thus, we know that there will always be a row in the Customers table to join to. With the DiscountCodes table it is different. This column is nullable, so we must use a left outer join to also include orders without a discount code. That is, the first join is from parent to child, and the other two joins are to lookup tables. You may not want to call these lookup tables “parents”, but nevertheless there are foreign-key constraints in that direction, and from that sense, they are parents.
Above we used an inner join to go from parent to child, because we had reason to assume that there will always be children. But if that is not the case, we need to use a left outer join. For instance, say that we want to list all customers and their orders, keeping in mind that there may be customers who have yet to place their first order:
SELECT C.CustomerName, O.OrderID, O.OrderDate FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID ORDER BY C.CustomerName, O.OrderID
This is the output:
CustomerName OrderID OrderDate ---------------- -------- ---------- Garima Kulkarni 10000 2020-08-23 Garima Kulkarni 11000 2021-10-01 Manisha Mishra NULL NULL Priya Pol 10890 2021-08-19
There is a common mistake with left outer joins, which is worth looking at before we turn to full outer joins, because understanding this mistake will help us to understand why need to write full outer joins according to a certain pattern.
Say that in the list above, we only want to list orders from this year. If we mainly have been writing inner joins, this may seem natural to write:
SELECT C.CustomerName, O.OrderID, O.OrderDate FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.OrderDate >= '2021-01-01' ORDER BY C.CustomerName, O.OrderID
However, the output is:
CustomerName OrderID OrderDate ---------------- -------- ---------- Garima Kulkarni 11000 2021-10-01 Priya Pol 10890 2021-08-19
As you can see, Manisha Mishra is missing. This happens because the left outer join is logically evaluated before the WHERE clause. So when the WHERE clause is evaluated, the OrderDate column is NULL for the row for Manisha Mishra. Thus, it is filtered out by the WHERE condition. Essentially, the WHERE clause changes the outer join to a plain inner join.
There is more than one way to change this. One is to extend the WHERE clause with the condition OR O.OrderDate IS NULL, but it is a little more verbose and may hamper readability in a more complex query. It may also be inefficient, because of limitations in the optimizer.
A better approach, both for readability and efficiency is to move the condition from the WHERE clause to the ON clause, so that the filter on OrderDate is evaluated as part of the join operation:
SELECT C.CustomerName, O.OrderID, O.OrderDate
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
AND O.OrderDate >= '2021-01-01'
ORDER BY C.CustomerName, O.OrderID
This lists all three customers:
CustomerName OrderID OrderDate ------------------ ----------- ---------- Garima Kulkarni 11000 2021-10-01 Manisha Mishra NULL NULL Priya Pol 10890 2021-08-19
After this lengthy introduction we now come to the main topic for this article, that is, full outer joins. We will however come back to these examples to apply the observations we will make when working with full outer joins.
In all the examples above, the joins were over a foreign-key relation and that is indeed very common. But in a full outer join there can be lone rows on both sides, something which normally cannot happen when there is a foreign-key constraint, since this enforces that there must be a row in the parent table. No, the tables in a full outer join are typically peers with the same set of keys. This is not exactly common.
Here is a prime example of tables being peers: We have two versions of the same query or stored procedure. We have made changes to the original to improve performance, and we want to ensure that the result did not change. We save the result of the original version and the modified version into temp tables, and to compare them we run a full outer join like this:
SELECT ...
FROM #ref r
FULL JOIN #new n ON r.keycol1 = n.keycol1
AND r.keycol2 = b.keycol2
AND ...
WHERE r.col1 <> n.col1 OR
r.col1 IS NULL AND n.col1 IS NOT NULL OR
r.col1 IS NOT NULL AND n.col1 IS NULL OR
r.col2 <> n.col2 OR
...
Because we have to check for the NULL values separately, the WHERE clause gets very tedious, but apart from that, the query does the job very well. (There is in fact a way to write this WHERE clause in a less tedious manner, but that is not a trick specific to full outer join, and this is something I will return to in the next issue of the SQLServerGeeks Magazine.) This initial example is straightforward, since we work with the tables in full. But if we only want to work with a subset of the tables, we need to be more careful.
A situation where full outer join often is needed is when we want to reconcile data from two different systems. Say that we have a need to mirror accounts from a couple of banks. We get transactions from the banks and we also send transactions to the banks when our clients buy or sell services or goods from us.
Every once in a while, we get files with the actual standings from the banks so that we can verify that our data is correct. Here is a script for these tables with sample data (as above, the tables are simplified to focus at what is essential):
CREATE TABLE OurData (CustomerID int NOT NULL,
BankID int NOT NULL,
Amount decimal(20,2) NOT NULL,
CONSTRAINT pk_OurData PRIMARY KEY (CustomerID, BankID)
)
go
INSERT OurData(CustomerID, BankID, Amount)
VALUES(1, 1, 1000.00), (2, 1, 1532.12), (3, 1, 1420.56),
(1, 2, 536.00), (2, 2, 499.00)
go
CREATE TABLE BankData (CustomerID int NOT NULL,
BankID int NOT NULL,
Amount decimal(20,2) NOT NULL,
CONSTRAINT pk_BankData PRIMARY KEY (CustomerID, BankID)
)
INSERT BankData(CustomerID, BankID, Amount)
VALUES(1, 1, 1000.00), (2, 1, 532.12), (4, 1, 1230.56),
(1, 2, 759.00), (2, 2, 499.00)
go
When running a reconciliation, we want to do that for one bank at a time, since the reconciliation is only meaningful when we just have received a file. It’s pointless to reconcile against data that is several days old.
A casual programmer who are in the auto-pilot mode for inner joins might try:
DECLARE @bank int = 1
SELECT coalesce(A.CustomerID, B.CustomerID) AS CustomerID,
A.Amount AS OurAmount, B.Amount AS BankAmount,
coalesce(A.Amount, 0) - coalesce(B.Amount, 0) AS DiffAmount
FROM OurData A
FULL JOIN BankData B ON A.CustomerID = B.CustomerID
AND A.BankID = B.BankID
WHERE A.BankID = @bank
AND coalesce(A.Amount, 0) <> coalesce(B.Amount, 0)
But the result is not correct:
CustomerID OurAmount BankAmount DiffAmount ----------- ----------- ------------ ----------- 2 1532.12 532.12 1000.00 3 1420.56 NULL 1420.56
There should be a row for customer 4 as well. And if you recall the example with left outer join where we filtered on OrderDate in the WHERE clause, you understand why we get the wrong result: The condition filters out rows where A.BankID is NULL, and the WHERE clause effectively transforms the full outer join to a right outer join.
Before we look at how to address this, let me first highlight a few things in this query that are correct, and which are typical for full outer joins. In the SELECT list, we have this:
coalesce(A.CustomerID, B.CustomerID) AS CustomerID
Since the CustomerID can come from any side, we need to use coalesce (or isnull) to be sure to get a value. This quite typical for how we display key columns in full-outer-join queries. Occasionally, though, you may prefer to have two columns to make it easier to see from which table(s) the data is coming, but the key is you cannot only rely one table. Recall that full outer join is a symmetrical operation and that the tables are peers!
We also have this in the SELECT list:
A.Amount AS OurAmount, B.Amount AS BankAmount
For this reconciliation we need to see the amounts on both sides. This is also quite typical for full outer joins: for non-key columns that are in both tables, we want to display both values, since we often to compare them against each other.
Also note this condition in the WHERE clause:
AND coalesce(A.Amount, 0) <> coalesce(B.Amount, 0)
Since Amount will be NULL when there is no row on that side, we must account for this in the WHERE condition. Exactly how to do this, depends on the business rules. In the example above, where we compared the result of two queries that should produce exactly the same result, we had to add specific conditions to test for NULL. But for our reconciliation, the business rules tell us that the absence of a row equates to an amount of 0, so we can apply coalesce (or isnull) for the task. You can also see the same pattern in the SELECT list for the DiffAmount column.
Let’s now get back to the broken filtering on the BankID. As with the case of the left outer join, we can solve the issue by changing the WHERE clause to have an OR condition, in this case (A.BankID = @bank OR B.BankID = @bank). This certainly treats the tables as peers, but it can become bulky if the conditions are more complex, and even more so if the conditions are different for the two tables. (Just because the tables are peers, they need not to be identical.) This gets even more apparent if we need to filter against other tables. Also, as with left outer join, the optimizer may not be able to find the best plan when we use OR.
For the left outer join, the simple fix was to move the condition from the WHERE clause to the ON clause, but if we try this with full outer join, the result is not usable. For instance, this query:
DECLARE @bank int = 1
SELECT coalesce(A.CustomerID, B.CustomerID) AS CustomerID,
A.Amount AS OurAmount, B.Amount AS BankAmount,
coalesce(A.Amount, 0) - coalesce(B.Amount, 0) AS DiffAmount
FROM OurData A
FULL JOIN BankData B ON A.CustomerID = B.CustomerID
AND A.BankID = B.BankID
AND A.BankID = @bank
AND B.BankID = @bank
WHERE coalesce(A.Amount, 0) <> coalesce(B.Amount, 0)
Produces this nonsense:
CustomerID OurAmount BankAmount DiffAmount ----------- ---------- ----------- ----------- 1 NULL 759.00 -759.00 1 536.00 NULL 536.00 2 1532.12 532.12 1000.00 2 NULL 499.00 -499.00 2 499.00 NULL 499.00 3 1420.56 NULL 1420.56 4 NULL 1230.56 -1230.56
I leave it as an exercise to the reader to figure out what is happening here.
No, to find a good solution, we need to think a little more of the logic of what we want to do. What we really want to do is to first extract the rows for the bank in question from both tables, and once we have done this, we want to compare these extractions. Thus, we must write the query so that logically the bank filter is applied before the full outer join. Here is a query to do this:
DECLARE @bank int = 1
SELECT coalesce(A.CustomerID, B.CustomerID) AS CustomerID,
A.Amount AS OurAmount, B.Amount AS BankAmount,
coalesce(A.Amount, 0) - coalesce(B.Amount, 0) AS DiffAmount
FROM (SELECT CustomerID, Amount FROM OurData WHERE BankID = @bank) A
FULL JOIN (SELECT CustomerID, Amount FROM BankData WHERE BankID = @bank) B
ON A.CustomerID = B.CustomerID
WHERE coalesce(A.Amount, 0) <> coalesce(B.Amount, 0)
That is, as table sources for the full outer join, we use two derived tables that filter out the bank. Now we get the correct result:
CustomerID OurAmount BankAmount DiffAmount ----------- ---------- ----------- ----------- 2 1532.12 532.12 1000.00 3 1420.56 NULL 1420.56 4 NULL 1230.56 -1230.56
Some readers may prefer to use common table expressions (CTEs) instead:
DECLARE @bank int = 1
; WITH A AS (
SELECT CustomerID, Amount FROM OurData WHERE BankID = @bank
), B AS (
SELECT CustomerID, Amount FROM BankData WHERE BankID = @bank
)
SELECT coalesce(A.CustomerID, B.CustomerID) AS CustomerID,
A.Amount AS OurAmount, B.Amount AS BankAmount,
coalesce(A.Amount, 0) - coalesce(B.Amount, 0) AS DiffAmount
FROM A
FULL JOIN B ON A.CustomerID = B.CustomerID
WHERE coalesce(A.Amount, 0) <> coalesce(B.Amount, 0)
Which you use is completely a matter of taste. The queries are equivalent and will always produce the same query plan in SQL Server.
Normally, when we work with inner joins and the left side of left outer joins, we typically put initial filtering in the WHERE clause. For instance, let’s take the initial query on orders, but add the condition that we only want see orders from this year. Most people would write this as:
SELECT O.OrderID, O.OrderDate, C.CustomerName, OD.ProductID,
O.DiscountCode, D.Discount
FROM Orders O
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
LEFT JOIN DiscountCodes D ON D.Code = O.DiscountCode
WHERE O.OrderDate >= '2021-01-01'
ORDER BY O.OrderID, O.OrderDate
But taking the above in regard, you could argue that it would be better to write it this way:
SELECT O.OrderID, O.OrderDate, C.CustomerName, OD.ProductID,
O.DiscountCode, D.Discount
FROM (SELECT * FROM Orders WHERE OrderDate > '2021-01-01') O
JOIN OrderDetails OD ON O.OrderID = OD.OrderID
JOIN Customers C ON O.CustomerID = C.CustomerID
LEFT JOIN DiscountCodes D ON D.Code = O.DiscountCode
ORDER BY O.OrderID, O.OrderDate
That is, we first filter out rows from the Orders want to work on, before we join to the rest of the tables.
Or take the query where we listed customers and their orders for this year. We can apply this technique to this query as well:
SELECT C.CustomerName, O.OrderID, O.OrderDate
FROM Customers C
LEFT JOIN (SELECT * FROM Orders WHERE OrderDate >= '2021-01-01') O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName, O.OrderID
This is somewhat more verbose that sticking the condition in the ON clause, but by separating the filter condition from the join condition, it makes our intentions clearer.
If we were to apply this model across the board, we would only use the WHERE clause to filter on conditions on non-key columns involving two or more tables in the join. One example of this is the comparison of the Amount columns in the full outer join. However, out of habit most people use the WHERE clause for this initial filtering, and I will need to confess that I normally do this myself too. As long it is a matter of inner joins, or outer tables in left outer joins, it does not matter. The logical result will be the same, and it also highly likely that the optimizer will get it right.
But when we work with full outer joins, or for that matter with the inner tables of left outer joins, we really need to think about what we are doing and make sure we apply them in right order:
- First extract the rows from the source tables we want to work on. This includes filtering that we need to do against other tables.
- Perform the join operation.
- Apply any filter on the result of the join.
Let’s now take this one step further and look at a three-table full outer join. This is even less common, and in the sample I mentioned in the beginning, there were no case of a three-way full outer join. However, I know that one of these procedures originally had exactly this, because I wrote it myself many years ago. And I still remember that it was absolutely not a walk in the park. It took me quite some time to get this right, and this experience is the inspiration for this article.
Let’s say that in our reconciliation problem that there is a transactions table. The effect of these transactions is reflected in OurData, but unsent transactions are not reflected in BankData, so we need to account for these transactions. And while unlikely, there could be a customer that is only in the transactions table. Then again, the purpose of a reconciliation is to find things that are out of order, so we need to account for this case. Here is a script for this table:
CREATE TABLE BankTransactions (TransactionID int NOT NULL,
CustomerID int NOT NULL,
BankID int NOT NULL,
IsSent bit NOT NULL,
Amount decimal(20,2) NOT NULL,
CONSTRAINT pk_BankTransactions PRIMARY KEY (TransactionID)
)
INSERT BankTransactions (TransactionID, CustomerID, BankID, IsSent, Amount)
VALUES(980, 1, 1, 1, 1000.00),
(981, 2, 1, 0, 100.00),
(982, 2, 1, 0, 900.00),
(983, 3, 1, 0, 700.00),
(984, 4, 1, 0, -1230.56),
(985, 5, 1, 0, -1900.00),
(900, 1, 2, 0, 200)
As we have learnt, to work with the BankTransactions table we need to filter it on Bank and also on the IsSent column, before we can join it to the other two tables. As it happens this comes naturally, since we need to aggregate the data per customer before we can join. So this leads is to:
DECLARE @bank int = 1
; WITH A AS (
SELECT CustomerID, Amount FROM OurData WHERE BankID = @bank
), B AS (
SELECT CustomerID, Amount FROM BankData WHERE BankID = @bank
), C AS (
SELECT CustomerID, SUM(Amount) AS Amount
FROM BankTransactions
WHERE BankID = @bank
AND IsSent = 0
GROUP BY CustomerID
)
SELECT coalesce(A.CustomerID, B.CustomerID, C.CustomerID) AS CustomerID,
A.Amount AS OurAmount, B.Amount AS BankAmount,
C.Amount AS TransAmount,
coalesce(A.Amount, 0) - coalesce(B.Amount, 0) AS DiffAmount,
coalesce(A.Amount, 0) -
(coalesce(B.Amount, 0) + coalesce(C.Amount, 0)) AS AdjDiffAmount
FROM A
FULL JOIN B ON A.CustomerID = B.CustomerID
FULL JOIN C ON C.CustomerID = ?
Let’s first look at the first element in the SELECT list. There are now three sources the CustomerID may come from, and we need to account for all of them. (And since coalesce accepts any number of arguments, it makes it a winner over isnull here.)
But what should we put in place of the question mark? Had this been a plain matter of two inner joins, the answer would be that it does not matter if we take A.CustomerID or B.CustomerID, as they are equivalent. But if you pick, say, A.CustomerID, you will find that you get two rows with CustomerID = 4, one with the data from B and with the data from C. And if you instead try B.CustomerID, you get two rows for CustomerID = 3.
No, again, in a full outer join, the tables are peers and we need to handle them symmetrically, for instance any of these two:
FULL JOIN C ON C.CustomerID = A.CustomerID OR
C.CustomerID = B.CustomerID
FULL JOIN C ON C.CustomerID = coalesce(A.CustomerID, B.CustomerID)
Yes, I have discouraged the use of OR earlier in this article, but there is not really any better option here.
This is the output with any of these conditions:
CustomerID OurAmount BankAmount TransAmount DiffAmount AdjDiffAmount ----------- ---------- ----------- ------------ ---------- -------------- 1 1000.00 1000.00 NULL 0.00 0.00 2 1532.12 532.12 1000.00 1000.00 0.00 4 NULL 1230.56 -1230.56 -1230.56 0.00 3 1420.56 NULL 700.00 1420.56 720.56 5 NULL NULL -1900.00 0.00 1900.00
Some readers may think that these queries can also be written with help of the UNION ALL operator. That is likely to be true, although I have not tried this myself, but I leave it as an exercise to the reader. It may also be that the UNION ALL queries are more efficient that the queries with full outer join, not the least in the example with the three-table join. However, not all queries with full outer join can easily be rewritten with UNION ALL. For this article, I wanted to use very simple examples to focus on my main points: you must identify the logical order you need for the operations in the query, and write your query accordingly. Filter source tables before joining them. And in the join conditions, the SELECT list and the WHERE clause handle table symmetrically. They are peers.
Finally, I should point out that there is one operation in SQL Server which includes a full outer join, but where you may not have thought of it, and that is the MERGE statement, particularly in the case where you have a branch with WHEN NOT MATCHED BY SOURCE. But at least, with MERGE there is no WHERE clause that can lead us astray, but it is more apparent that we need to apply the filtering first.
This article first appeared in the SQLServerGeeks Magazine.
Author: Erland Sommarskog
Subscribe to get your copy.
One Comment on “A Tip about Full Outer Join by Erland Sommarskog”