FULL OUTER JOIN returns a result set that includes rows from the left and
right tables.
When no matching rows exist for the left table row, the right table
columns will have nulls.
Similarly, when there are no matching rows for the right table row, the
left table column will have nulls, other clauses you can use to do joins
between tables are INNER JOIN, LEFT JOIN, and RIGHT JOIN.
Here is a SELECT using FULL OUTER JOIN when joining two tables Table1 and
Table2:
SELECT select_list FROM Table 1 FULL OUTER JOIN Table2 ON
join_predicate;
The OUTER keyword is optional, so you can omit it as shown in the
following query:
SELECT select_list FROM Table 1 FULL JOIN Table2 ON join_predicate;
The above means:
First, the left table Table1 is specified in the FROM clause.
Second, the table Table2 on the right and a join predicate are specified.
The following diagram illustrates a FULL OUTER JOIN of two sets:
FULL OUTER JOIN example
For this example, you need to have SQL Server, express edition installed,
although any edition is useful, as well as SSMS, and you also need to
download and install the WideWorldImporters database.
At the end of this article you will find the links to download each
element used here.
Two tables will be taken as samples that contain a common field, such as
the purchase order ID, to show the Stock ID of each item, the description
and the purchase order date.
To make that JOIN, the following statement is used:
SELECT Purchasing.PurchaseOrderLines.StockItemID,
Purchasing.PurchaseOrderLines.Description,
Purchasing.PurchaseOrders.OrderDate FROM Purchasing.PurchaseOrderLines
FULL OUTER JOIN Purchasing.PurchaseOrders ON
PurchaseOrderLines.PurchaseOrderID = PurchaseOrders.PurchaseOrderID;
In this example, the query returned the items that appear in the purchase
order table, and also in the purchase order line table, below are the
results:
8367 records were obtained almost instantaneously, since the FULL OUTER JOIN
instruction works with few computer resources.