SQL FULL OUTER JOIN keyword (full external connection)

SQL FULL OUTER JOIN keyword

When the left (Table 1) or right (Table 2) table records match, the FULL OUTER JOIN keyword returns all records.

Note: F ULL OUTER JOIN may return a very large result set!

SQL FULL OUTER JOIN syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data from the Customers table:

Data selected from the Orders table:

SQL FULL OUTER JOIN instance

The following SQL statement selects all customers and all orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

The data selected from this set of results are as follows:

Note: T he FULL OUTER JOIN keyword returns all rows in the left table (Customers) and all rows in the right table (Orders). If there are no matches in Orders in the rows in Customers, or if there are no matches in The Orders, the rows are also listed.