萬盛學電腦網

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

SQL INNER JOIN 內連接完整教學:匹配行查詢、多表關聯與效能優化

INNER JOIN 核心定義

只回傳**兩表皆有匹配**的行。無匹配的行被過濾掉。INNER JOIN = JOIN (關鍵字 INNER 可省略)。

語法

SELECT select_list
FROM table1
INNER 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
INNER JOIN Orders o ON p.P_Id = o.P_Id
ORDER BY p.LastName;

結果

LastNameFirstNameOrderNo
HansenOla22456
HansenOla24562
PettersenKari77895
PettersenKari44678

觀察:

  • Svendson (P_Id=2) 無訂單,**不出現在結果中**
  • Orders 中 P_Id=15 無對應會員,**不出現在結果中**
  • Hansen 有 2 筆訂單,出現 2 行 (一對多展開)

多表 INNER JOIN

SELECT u.username, o.order_no, p.name AS product, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.status = 1;

INNER JOIN vs WHERE 子查詢

-- 子查詢寫法 (舊版 MySQL 優化較差)
SELECT * FROM Persons WHERE P_Id IN (SELECT P_Id FROM Orders);

-- INNER JOIN 寫法 (推薦,可利用索引、優化器更好)
SELECT DISTINCT p.* FROM Persons p INNER JOIN Orders o ON p.P_Id = o.P_Id;

效能優化關鍵

  1. 關聯欄位雙向建索引:INDEX(P_Id) 在兩表
  2. 型別長度字元集完全一致:避免隱含轉換
  3. 小結果集驅動大表:優化器通常自動選擇,可用 STRAIGHT_JOIN 強制順序
  4. 只選需要欄位:避免 SELECT *,利用覆蓋索引
  5. WHERE 條件下推:WHERE 盡早過濾,減少 JOIN 參與行數

EXPLAIN 分析範例

EXPLAIN SELECT p.LastName, o.OrderNo 
FROM Persons p INNER JOIN Orders o ON p.P_Id = o.P_Id;
-- 關注:
-- type: ref (理想) / ALL (全表掃描)
-- key: 實際使用的索引
-- rows: 預估掃描行數
-- Extra: Using where / Using index

常見陷阱

  • 陷阱現象解決
    結果行數暴增一對多關聯導致行數乘積用 DISTINCT、GROUP BY、或子查詢聚合後再 JOIN
    NULL 值匹配NULL != NULL,不會被 JOIN 匹配用 COALESCE 或 IS NULL 處理
    字元集不匹配utf8mb4 vs latin1 導致索引失效統一字元集,ALTER TABLE ... CONVERT TO CHARSET utf8mb4
    隱含轉換INT 欄位 JOIN VARCHAR 欄位統一型別,或 CAST(col AS UNSIGNED)

    PHP 實作

    <?php
    $mysqli = new mysqli('localhost', 'user', 'pass', 'db');
    $mysqli->set_charset('utf8mb4');
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    
    $sql = "
        SELECT m.id, m.username, o.order_no, o.total_amount, o.created_at
        FROM members m
        INNER JOIN orders o ON m.id = o.member_id
        WHERE m.status = 1 AND o.status = 'completed'
        ORDER BY o.created_at DESC
        LIMIT 50
    ";
    
    $result = $mysqli->query($sql);
    echo "共 {$result->num_rows} 筆訂單記錄\n";
    
    while ($row = $result->fetch_assoc()) {
        printf("會員: %s | 訂單: %s | 金額: ¥%s | 時間: %s\n",
            $row['username'], $row['order_no'], $row['total_amount'], $row['created_at']
        );
    }
    
    $result->free();
    $mysqli->close();
    ?>

    總結

    • INNER JOIN = 交集,雙方必須有匹配
    • 關聯條件放 ON,過濾條件放 WHERE
    • 索引、型別一致、小表驅動大表是效能三要素
    • 結果行數可能 > 原表行數 (一對多展開),注意後續處理
    • MySQL 8.0+ 支援 LATERAL JOIN / CTE 更靈活處理複雜關聯
    copyright © 萬盛學電腦網 all rights reserved