Note: SQL Right Outer Joins
UID: 202401291210-1
Tags: sql, joins, database
Definition
A RIGHT OUTER JOIN (or simply RIGHT JOIN) returns all records from the right table, along with matching records from the left table. If no match is found, columns from the left table are filled with NULL. This is the inverse of a LEFT JOIN, ensuring that all rows from the right table are preserved, even if no corresponding records exist in the left table.
Example
Given two tables:
Customers
| id | name | city |
|---|---|---|
| 1 | Alice | New York |
| 2 | Bob | Chicago |
| 3 | Charlie | San Diego |
| 4 | David | Miami |
Orders
| id | customer_id | product |
|---|---|---|
| 1 | 1 | Laptop |
| 2 | 1 | Keyboard |
| 3 | 3 | Monitor |
| 4 | 5 | Mouse |
Executing a RIGHT JOIN:
SELECT Customers.name, Orders.product
FROM Customers
RIGHT JOIN Orders ON Customers.id = Orders.customer_id;Result:
| name | product |
|---|---|
| Alice | Laptop |
| Alice | Keyboard |
| Charlie | Monitor |
| NULL | Mouse |
Explanation:
- Alice and Charlie had matching orders, so they appear with their respective products.
- The order for customer ID
5(Mouse) is included, but since there is no matching customer,NULLappears in thenamecolumn. - Bob and David are not included because they have no corresponding orders.
Key Differences from Other Joins
| Join Type | Includes Unmatched Rows? | Where Do NULLs Appear? |
|---|---|---|
| INNER JOIN | ❌ No | No unmatched rows |
| LEFT JOIN | ✅ Yes (left table) | Right table columns |
| RIGHT JOIN | ✅ Yes (right table) | Left table columns |
| FULL OUTER JOIN | ✅ Yes (both tables) | Both sides where no match exists |