Full Join
FULL (OUTER) JOIN: Selects all records that match either left or right table records.
FULL JOIN can potentially return very large datasets.
FULL JOIN and FULL OUTER JOIN are the same.
SQL FULL JOIN Syntax:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Example:
Lets use the “Products” and “Orders” table to perform a FULL JOIN on the “ProductID” column.
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 1 | Super Printer | 9000 series printer | 300 | TechOne | 1 |
| 2 | Laptop 5 | 4000 Laptop 5th generation | 700 | TechTwo | 2 |
| 3 | 3D Printer | 3D Print Pro | 900 | TechOne | 1 |
| 4 | Labtop 7 | 100 Labtop 7 processor | 1200 | TechTwo | 2 |
| 5 | Camera 400 | Camera Ultra 400 | 400 | TechThree | 3 |
| OrderID | ProductID | EmployeeID | OrderDate | CustomerID |
|---|---|---|---|---|
| 10 | 2 | 1 | 9/9/2009 | 2 |
| 11 | 3 | 2 | 1/9/2009 | 3 |
| 12 | 4 | 1 | 3/9/2009 | 4 |
| 13 | 1 | 3 | 9/9/2010 | 5 |
| 14 | 3 | 1 | 9/30/2009 | 3 |
| 15 | 5 | 2 | 6/12/2009 | 1 |
| 16 | 3 | 4 | 4/9/2009 | 6 |
SELECT
P.ProductID,
P.ProductName,
O.OrderID,
O.ProductID
FROM Products as P
FULL JOIN Orders as O
ON P.ProductID=O.ProductID
Results:
| ProductID | ProductName | OrderID | ProductID |
|---|---|---|---|
| 1 | Super Printer | 13 | 1 |
| 2 | Laptop 5 | 10 | 2 |
| 3 | 3D Printer | 11 | 3 |
| 4 | Labtop 7 | 12 | 4 |
| 5 | Camera 400 | 15 | 5 |
| null | null | 14 | 3 |
| null | null | 16 | 3 |
SQL Tutorial