Slide 14.20: The FULL (OUTER) JOIN clause

Slide 14.19: The RIGHT (OUTER) JOIN clause
Slide 14.21: Pros and cons of SQL JOIN clause
Home Print version

The FULL (OUTER) JOIN Clause

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

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

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

Then, look at the 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

The FULL OUTER JOIN clause 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.

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

and it will produce something like this:

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

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support