回傳**右表所有行**,加上左表匹配的行。左表無匹配處填 NULL。RIGHT JOIN = RIGHT OUTER JOIN。
SELECT select_list
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column
[WHERE condition];
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o ON p.P_Id = o.P_Id
ORDER BY p.LastName;
| LastName | FirstName | OrderNo |
|---|---|---|
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| NULL | NULL | 34764 |
關鍵觀察:OrderNo=34764 (P_Id=15) 在 Persons 中找不到對應會員,但仍保留在結果中,會員欄位為 NULL。
-- 所有訂單明細,含會員資訊 (訂單是核心,會員可能已刪除)
SELECT o.order_no, o.total_amount, o.created_at,
m.username, m.email, m.phone
FROM members m
RIGHT JOIN orders o ON m.id = o.member_id
ORDER BY o.created_at DESC;
-- 找出無效會員的訂單 (外鍵指向不存在的主鍵)
SELECT o.* FROM members m
RIGHT JOIN orders o ON m.id = o.member_id
WHERE m.id IS NULL;
-- 所有角色及其擁有的用戶 (角色為主,用戶可能為空)
SELECT r.role_name, u.username
FROM users u
RIGHT JOIN roles r ON u.role_id = r.id
ORDER BY r.role_name;
慣例以「主表在左、從表在右」,RIGHT JOIN 可直接交換表順序改寫:
-- 原 RIGHT JOIN
SELECT p.LastName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o ON p.P_Id = o.P_Id;
-- 等價 LEFT JOIN (交換表順序)
SELECT p.LastName, o.OrderNo
FROM Orders o
LEFT JOIN Persons p ON p.P_Id = o.P_Id;
建議:統一使用 LEFT JOIN,閱讀性更符合直覺 (主→從),避免混淆。
-- ❌ WHERE 過濾右表條件:會過濾掉左表無匹配的行
SELECT * FROM Persons p RIGHT JOIN Orders o ON p.P_Id = o.P_Id WHERE o.amount > 1000;
-- ✅ ON 中加入右表條件:保留所有訂單
SELECT * FROM Persons p RIGHT JOIN Orders o ON p.P_Id = o.P_Id AND o.amount > 1000;
RIGHT JOIN 轉換為 LEFT JOIN 處理<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// 訂單列表含會員資訊 (以訂單為主)
$sql = "
SELECT o.id, o.order_no, o.total_amount, o.status, o.created_at,
m.username, m.email, m.phone
FROM orders o
LEFT JOIN members m ON o.member_id = m.id -- 改寫為 LEFT JOIN
ORDER BY o.created_at DESC
LIMIT 50
";
$result = $mysqli->query($sql);
echo "<table><tr><th>訂單號</th><th>金額</th><th>狀態</th><th>會員</th><th>Email</th><th>下單時間</th></tr>";
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . htmlspecialchars($row['order_no']) . "</td>";
echo "<td>¥" . number_format($row['total_amount'], 2) . "</td>";
echo "<td>" . htmlspecialchars($row['status']) . "</td>";
echo "<td>" . ($row['username'] ?? '會員已刪除') . "</td>";
echo "<td>" . ($row['email'] ?? '-') . "</td>";
echo "<td>" . $row['created_at'] . "</td>";
echo "</tr>";
}
echo "</table>";
$result->free();
$mysqli->close();
?>
RIGHT JOIN 保留右表所有行,左表無匹配填 NULLLEFT JOIN 交換表順序ON 而非 WHERE