萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL RIGHT JOIN 右外連接完整教學:保留右表所有行、實際應用場景與改寫技巧

RIGHT JOIN 核心定義

回傳**右表所有行**,加上左表匹配的行。左表無匹配處填 NULL。RIGHT JOIN = RIGHT OUTER JOIN。

語法

SELECT select_list
FROM table1
RIGHT JOIN table2
    ON table1.column = table2.column
[WHERE condition];

範例資料與查詢

Persons 表 (左表)

P_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger

Orders 表 (右表)

O_IdOrderNoP_Id
1778953
2446783
3224561
4245621
53476415

查詢:所有訂單及其下單會員 (無會員也顯示)

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;

結果

LastNameFirstNameOrderNo
HansenOla22456
HansenOla24562
PettersenKari77895
PettersenKari44678
NULLNULL34764

關鍵觀察:OrderNo=34764 (P_Id=15) 在 Persons 中找不到對應會員,但仍保留在結果中,會員欄位為 NULL。

RIGHT JOIN 實際應用場景

1. 以「從表」為主視角的報表

-- 所有訂單明細,含會員資訊 (訂單是核心,會員可能已刪除)
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;

2. 發現外鍵完整性問題 (孤兒記錄檢測)

-- 找出無效會員的訂單 (外鍵指向不存在的主鍵)
SELECT o.* FROM members m
RIGHT JOIN orders o ON m.id = o.member_id
WHERE m.id IS NULL;

3. 權限/配置表為主的查詢

-- 所有角色及其擁有的用戶 (角色為主,用戶可能為空)
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 改寫為 LEFT JOIN (推薦做法)

慣例以「主表在左、從表在右」,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,閱讀性更符合直覺 (主→從),避免混淆。

RIGHT JOIN 中 ON vs WHERE 差異 (同 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 處理
  • 驅動表為右表,建議右表較小或有高選擇性 WHERE 條件
  • 關聯欄位索引、型別一致性要求同 LEFT JOIN

PHP 實作

<?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 保留右表所有行,左表無匹配填 NULL
  • 實務上**極少直接用 RIGHT JOIN**,慣例改寫為 LEFT JOIN 交換表順序
  • 典型場景:以從表(訂單/日誌/明細)為主視角、檢測外鍵完整性
  • 右表條件放 ON 而非 WHERE
  • MySQL 優化器內部統一轉換為 LEFT JOIN 執行
copyright © 萬盛學電腦網 all rights reserved