SQL OUTER JOIN
The SQL OUTER JOIN clause is used to combine rows from two or more tables, including rows that do not have matching values in both tables. This join ensures that unmatched rows from one or both tables are included in the result set.
An OUTER JOIN can be further divided into:
- LEFT JOIN: Includes unmatched rows from the left table.
- RIGHT JOIN: Includes unmatched rows from the right table.
- FULL JOIN: Includes unmatched rows from both tables.
Tables Used
Orders Table:| OrderID | Product | CustomerID |
|---|---|---|
| 1 | Laptop | 101 |
| 2 | Phone | 102 |
| 3 | Tablet | NULL |
| CustomerID | CustomerName |
|---|---|
| 101 | Alice |
| 102 | Bob |
| 103 | Charlie |
Example
We’ll perform a LEFT OUTER JOIN to include all orders, even those without a matching customer in the Customers table.
Code Example
-- Perform a LEFT OUTER JOIN between Orders and Customers
SELECT Orders.OrderID, Orders.Product, Customers.CustomerName
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
-- View the resulting table
Output Table
| OrderID | Product | CustomerName |
|---|---|---|
| 1 | Laptop | Alice |
| 2 | Phone | Bob |
| 3 | Tablet | NULL |
Explanation
- The LEFT JOIN ensures all rows from the Orders table are included.
- For rows without a match in the Customers table, the CustomerName is NULL.
- This approach is helpful when you want to retain data from one table, even if there’s no corresponding match in another.