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

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

二、先鋪墊：多表查詢的基礎(chǔ)前提

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

先定義兩個(gè)測(cè)試表：

-- 用戶表（主表）
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,     -- 訂單編號(hào)
  `user_id` INT NOT NULL,              -- 關(guān)聯(lián)用戶表的用戶ID（外鍵）
  `price` DECIMAL(10,2) NOT NULL,      -- 訂單金額
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) -- 外鍵約束
);

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

方式 1：JOIN 連接查詢

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

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

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

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

-- 簡(jiǎn)潔寫法（省略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; -- 關(guān)聯(lián)條件：用戶ID=訂單的用戶ID

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

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

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

語(yǔ)法：SELECT 字段 FROM 左表 LEFT JOIN 右表 ON 關(guān)聯(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;

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

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

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

語(yǔ)法：SELECT 字段 FROM 左表 RIGHT JOIN 右表 ON 關(guān)聯(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;

考察點(diǎn)：知道右連接的存在，理解其與左連接的對(duì)稱關(guān)系。

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

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

SQL 示例：

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

方式 2：子查詢（嵌套查詢，適合簡(jiǎn)單場(chǎng)景）

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

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

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

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

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

• 示例：查詢 “每個(gè)用戶的最新訂單”（按訂單 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 = ( -- 子查詢：關(guān)聯(lián)主表的user_id，取該用戶最大訂單ID（最新訂單）
    SELECT MAX(id) FROM `order` WHERE user_id = u.id
);

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

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

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

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

-- UNION：去重（自動(dòng)過(guò)濾重復(fù)的用戶記錄）
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：不去重（性能更高，適合確定無(wú)重復(fù)數(shù)據(jù)的場(chǎng)景）
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;

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

加分項(xiàng)

1、結(jié)合項(xiàng)目舉例：“我在實(shí)訓(xùn)項(xiàng)目中，用LEFT JOIN查詢所有用戶及對(duì)應(yīng)的訂單列表，用INNER JOIN查詢有支付記錄的用戶信息”；

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

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

舉一反三

1、“如何查詢‘沒(méi)有訂單記錄的用戶’？”（答案：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是表關(guān)聯(lián)條件，先執(zhí)行；WHERE是結(jié)果過(guò)濾條件，在表關(guān)聯(lián)后執(zhí)行；LEFT JOIN中，ON不過(guò)濾左表數(shù)據(jù)，WHERE會(huì)過(guò)濾）；

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

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

最新評(píng)論