SQL FULL OUTER JOIN Keyword

Posted by

SQL

FULL OUTER JOIN

Keyword


SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in
left (table1) or right (table2) table records.


Note:

FULL OUTER JOIN can potentially return very large
result-sets!


Tip:

FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SELECT

column_name(s)

FROM

table1

FULL OUTER JOIN

table2

ON

table1.column_name

=

table2.column_name

WHERE

condition

;

SQL FULL OUTER JOIN


Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

And a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2




SQL FULL OUTER JOIN Example

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;

A selection from the result set may look like this:

CustomerName OrderID
Alfreds Futterkiste
Null
Ana Trujillo Emparedados y helados 10308
Antonio Moreno Taquería
Null


Note:

The FULL OUTER JOIN keyword returns all matching
records from both tables whether the other table matches or not. So, if
there are rows in “Customers” that do not have matches in “Orders”, or if there
are rows in “Orders” that do not have matches in “Customers”, those rows will be
listed as well.