Full Outer Join SQL

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

SELECT  select_list FROM Table 1 FULL OUTER JOIN Table2 ON

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:


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.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

8367 records were obtained almost instantaneously, since the FULL OUTER JOIN
instruction works with few computer resources.

Download links

Leave a Reply

Your email address will not be published.