一、先明確：為什么必須掌握多表查詢？

1.你是否能區(qū)分不同多表查詢方式的差異？
2.能否根據(jù)業(yè)務場景選擇合適的查詢方式？
3.能否獨立寫出正確的多表查詢 SQL？（避免語法錯誤）

二、先鋪墊：多表查詢的基礎前提

先明確多表查詢的核心：表與表之間必須有 “關聯(lián)關系”（通常是主鍵 - 外鍵關聯(lián)，比如用戶表user的id（主鍵）對應訂單表order的user_id（外鍵）），沒有關聯(lián)關系的多表查詢會產(chǎn)生 “笛卡爾積”（數(shù)據(jù)冗余，無實際業(yè)務意義）。

先定義兩個測試表：

-- 用戶表（主表）
CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT, -- 用戶ID（主鍵）
  `username` VARCHAR(50) NOT NULL,     -- 用戶名
  `age` INT DEFAULT 0                  -- 年齡
);

-- 訂單表（從表）
CREATE TABLE `order` (
  `id` INT PRIMARY KEY AUTO_INCREMENT, -- 訂單ID（主鍵）
  `order_no` VARCHAR(30) NOT NULL,     -- 訂單編號
  `user_id` INT NOT NULL,              -- 關聯(lián)用戶表的用戶ID（外鍵）
  `price` DECIMAL(10,2) NOT NULL,      -- 訂單金額
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) -- 外鍵約束
);

三、核心多表查詢方式拆解

方式 1：JOIN 連接查詢

JOIN是多表查詢的核心方式，通過關聯(lián)字段將多個表 “拼接” 在一起查詢，核心分為內(nèi)連接、外連接（左外連接、右外連接）、交叉連接（極少用，僅作了解）。

1.1 內(nèi)連接（INNER JOIN / JOIN）

通俗定義：只查詢 “兩張表中滿足關聯(lián)條件” 的數(shù)據(jù)，不滿足條件的記錄會被過濾掉（相當于 “取兩張表的交集”）。

語法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.關聯(lián)字段 = 表2.關聯(lián)字段 [WHERE 條件]（INNER可省略，直接寫JOIN）。

-- 簡潔寫法（省略INNER）
SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
JOIN `order` o ON u.id = o.user_id; -- 關聯(lián)條件：用戶ID=訂單的用戶ID

考察點：你是否知道ON用于指定表關聯(lián)條件，WHERE用于過濾查詢結果（避免把關聯(lián)條件寫在WHERE中）。

1.2 左外連接（LEFT JOIN / LEFT OUTER JOIN）

通俗定義：以 “左表” 為基準，查詢左表的所有數(shù)據(jù)，右表中滿足關聯(lián)條件的數(shù)據(jù)會被匹配顯示，不滿足條件的右表字段顯示NULL（相當于 “左表全量數(shù)據(jù) + 右表交集數(shù)據(jù)”）。

語法：SELECT 字段 FROM 左表 LEFT JOIN 右表 ON 關聯(lián)條件 [WHERE 條件]（OUTER可省略）。

SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
LEFT JOIN `order` o ON u.id = o.user_id;

考察點：能否區(qū)分左表和右表，理解 “右表不滿足條件顯示 NULL” 的特性。

1.3 右外連接（RIGHT JOIN / RIGHT OUTER JOIN）

通俗定義：以 “右表” 為基準，查詢右表的所有數(shù)據(jù)，左表中滿足關聯(lián)條件的數(shù)據(jù)會被匹配顯示，不滿足條件的左表字段顯示NULL（相當于 “右表全量數(shù)據(jù) + 左表交集數(shù)據(jù)”），功能上可通過左連接互換表位置實現(xiàn)。

語法：SELECT 字段 FROM 左表 RIGHT JOIN 右表 ON 關聯(lián)條件 [WHERE 條件]（OUTER可省略）。

SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
RIGHT JOIN `order` o ON u.id = o.user_id;

考察點：知道右連接的存在，理解其與左連接的對稱關系。

1.4 交叉連接（CROSS JOIN，極少用）

通俗定義：無關聯(lián)條件的連接，會產(chǎn)生兩張表的 “笛卡爾積”（數(shù)據(jù)行數(shù) = 表 1 行數(shù) × 表 2 行數(shù)），通常無實際業(yè)務意義，僅在特殊場景（比如生成測試數(shù)據(jù)）使用。

SQL 示例：

-- 產(chǎn)生笛卡爾積，慎用
SELECT u.username, o.order_no
FROM `user` u
CROSS JOIN `order` o;

方式 2：子查詢（嵌套查詢，適合簡單場景）

通俗定義：將一個查詢語句（子查詢 / 內(nèi)層查詢）嵌套在另一個查詢語句（主查詢 / 外層查詢）中，子查詢的結果作為主查詢的條件或數(shù)據(jù)源，分為 “關聯(lián)子查詢” 和 “非關聯(lián)子查詢”。

非關聯(lián)子查詢：子查詢可獨立執(zhí)行，結果不依賴主查詢（適合簡單條件過濾）

• 示例：查詢 “購買過訂單金額大于 100 元的用戶” 信息

SELECT id, username, age
FROM `user`
WHERE id IN ( -- 子查詢：獲取訂單金額>100的所有用戶ID
    SELECT DISTINCT user_id FROM `order` WHERE price > 100
);

關聯(lián)子查詢：子查詢依賴主查詢的字段，無法獨立執(zhí)行（適合 “按主表每條記錄匹配子表” 的場景）

• 示例：查詢 “每個用戶的最新訂單”（按訂單 ID 倒序取第一條）

SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
JOIN `order` o ON u.id = o.user_id
WHERE o.id = ( -- 子查詢：關聯(lián)主表的user_id，取該用戶最大訂單ID（最新訂單）
    SELECT MAX(id) FROM `order` WHERE user_id = u.id
);

考察點：能否區(qū)分關聯(lián) / 非關聯(lián)子查詢，知道子查詢的適用邊界（避免多層嵌套導致性能問題）。

方式 3：聯(lián)合查詢（UNION / UNION ALL，結果集合并）

通俗定義：將多個SELECT查詢的結果集合并成一個結果集，要求所有查詢的 “字段數(shù)量、字段類型、字段順序” 一致，分為UNION（去重，會過濾重復記錄）和UNION ALL（不去重，性能更高）。

語法：SELECT 字段 FROM 表1 [WHERE 條件] UNION [ALL] SELECT 字段 FROM 表2 [WHERE 條件];

-- UNION：去重（自動過濾重復的用戶記錄）
SELECT id, username, age FROM `user` WHERE age > 25
UNION
SELECT u.id, u.username, u.age FROM `user` u
JOIN `order` o ON u.id = o.user_id WHERE o.price > 200;

-- UNION ALL：不去重（性能更高，適合確定無重復數(shù)據(jù)的場景）
SELECT id, username, age FROM `user` WHERE age > 25
UNION ALL
SELECT u.id, u.username, u.age FROM `user` u
JOIN `order` o ON u.id = o.user_id WHERE o.price > 200;

考察點：知道UNION和UNION ALL的區(qū)別，明確聯(lián)合查詢的字段要求。

加分項

1、結合項目舉例：“我在實訓項目中，用LEFT JOIN查詢所有用戶及對應的訂單列表，用INNER JOIN查詢有支付記錄的用戶信息”；

2、性能意識：“復雜多表查詢優(yōu)先用JOIN，不用多層子查詢；合并結果集時，確定無重復數(shù)據(jù)就用UNION ALL，比UNION快”；

3、細節(jié)把控：“JOIN的關聯(lián)條件寫在ON里，過濾條件寫在WHERE里，避免笛卡爾積冗余”。

舉一反三

1、“如何查詢‘沒有訂單記錄的用戶’？”（答案：LEFT JOIN后判斷訂單字段為NULL，SELECT u.* FROM user u LEFT JOIN order o ON u.id=o.user_id WHERE o.id IS NULL）；

2. “ON和WHERE在JOIN查詢中有什么區(qū)別？”（答案：ON是表關聯(lián)條件，先執(zhí)行；WHERE是結果過濾條件，在表關聯(lián)后執(zhí)行；LEFT JOIN中，ON不過濾左表數(shù)據(jù)，WHERE會過濾）；

3. “多表查詢時，如何提高性能？”（答案：給關聯(lián)字段（主鍵 / 外鍵）建索引；避免SELECT *，只查需要的字段；減少笛卡爾積產(chǎn)生）。

以上就是MySQL中多表查詢的方式總結的詳細內(nèi)容，更多關于MySQL多表查詢方式的資料請關注腳本之家其它相關文章！

最新評論