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;

Assume the two tables Customers and Orders are given as follows:

Customers
CustomerID CustomerName
1 Raj
2 Neha
3 John
4 Anuj
Orders
OrderID CustomerID OrderAmount
101 1 4500
102 2 1200
103 1 2200
104 3 800

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 c.CustomerName, o.OrderAmount
  FROM Customers c FULL OUTER JOIN Orders o
  ON c.CustomerID = o.CustomerID;
⇒
CustomerName OrderAmount
Raj 4500
Raj 2200
Neha 1200
John 800
Anuj NULL


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.

SQL Statement:

Edit the SQL statement and click     to see the result, or  

Result:
The Database includes:
The Database includes:

TablenameRecord
Customers91
Categories8
Employees10
OrderDetails518
Orders196
Products77
Shippers3
Suppliers29





      “Life is like a box of chocolates.    
      It doesn’t last long if you’re fat.”    
      — Joe Lycett