MySQL 中復(fù)雜 SQL 的核心部分：多表聯(lián)查和子查詢。這是數(shù)據(jù)庫操作中處理關(guān)聯(lián)數(shù)據(jù)的強(qiáng)大工具。

核心目標(biāo)： 從多個(gè)相互關(guān)聯(lián)的表中組合和提取所需的數(shù)據(jù)。

第一部分：多表聯(lián)查 (JOIN Operations)

當(dāng)你的數(shù)據(jù)模型設(shè)計(jì)良好（遵循規(guī)范化原則）時(shí)，數(shù)據(jù)會(huì)分散在多個(gè)表中，通過主鍵-外鍵關(guān)系連接。JOIN 操作就是用來基于這些關(guān)系將多個(gè)表中的行組合起來。

1. 連接的類型 (JOIN Types)

a. INNER JOIN (內(nèi)連接 / 等值連接)

• 作用： 返回兩個(gè)表中連接字段值相等的所有行組合。如果某行在其中一個(gè)表中沒有匹配的行，則不會(huì)出現(xiàn)在結(jié)果中。
• 語法：

SELECT 列名列表
FROM 表1
[INNER] JOIN 表2 ON 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段
[WHERE 條件];
-- INNER 關(guān)鍵字通常可省略

示例： 查詢所有有訂單的客戶信息（假設(shè) customers 表有 customer_id，orders 表有 customer_id 外鍵）

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- 結(jié)果只包含那些在customers表中有記錄且在orders表中至少有一個(gè)訂單的客戶。

圖示： 兩個(gè)集合的交集部分。

b. LEFT [OUTER] JOIN (左外連接)

• 作用： 返回左表 (表1) 的所有行，即使在右表 (表2) 中沒有匹配的行。對(duì)于左表中存在而右表中沒有匹配的行，右表相關(guān)的列將顯示為 NULL。
• 語法：

SELECT 列名列表
FROM 表1
LEFT [OUTER] JOIN 表2 ON 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段
[WHERE 條件];
-- OUTER 關(guān)鍵字通?？墒÷?/pre>

示例： 查詢所有客戶及其訂單（包括沒有下過單的客戶）

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- 結(jié)果包含所有客戶。對(duì)于沒有訂單的客戶，o.order_id 和 o.order_date 會(huì)是 NULL。

圖示： 整個(gè)左集合 + 與右集合的交集部分。右集合獨(dú)有的部分被舍棄。

c. RIGHT [OUTER] JOIN (右外連接)

• 作用： 與 LEFT JOIN 相反。返回右表 (表2) 的所有行，即使在左表 (表1) 中沒有匹配的行。對(duì)于右表中存在而左表中沒有匹配的行，左表相關(guān)的列將顯示為 NULL。
• 語法：

SELECT 列名列表
FROM 表1
RIGHT [OUTER] JOIN 表2 ON 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段
[WHERE 條件];
-- OUTER 關(guān)鍵字通常可省略

• 示例： 查詢所有訂單及其對(duì)應(yīng)的客戶信息（包括那些可能關(guān)聯(lián)到無效客戶的訂單 - 這種情況在良好設(shè)計(jì)的數(shù)據(jù)模型中較少見，但語法支持）

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
-- 結(jié)果包含所有訂單。如果某個(gè)訂單的 customer_id 在 customers 表中找不到，則 c.customer_id 和 c.name 會(huì)是 NULL。

• 圖示： 整個(gè)右集合 + 與左集合的交集部分。左集合獨(dú)有的部分被舍棄。
• 注意： RIGHT JOIN 在實(shí)際應(yīng)用中不如 LEFT JOIN 常見，因?yàn)橥ǔ？梢酝ㄟ^調(diào)整表順序使用 LEFT JOIN 達(dá)到相同目的。

d. FULL [OUTER] JOIN (全外連接)

• 作用： 返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒有匹配行時(shí)，則另一個(gè)表相關(guān)的列將顯示為 NULL。如果兩個(gè)表中有匹配的行，則進(jìn)行連接。
• 語法 (MySQL 不支持直接的 FULL OUTER JOIN，需用 UNION 模擬)：

SELECT 列名列表
FROM 表1
LEFT JOIN 表2 ON 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段
UNION [ALL] -- 通常用 UNION 去重，如果確定不會(huì)有重復(fù)或需要保留重復(fù)則用 UNION ALL
SELECT 列名列表
FROM 表1
RIGHT JOIN 表2 ON 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段
WHERE 表1.關(guān)聯(lián)字段 IS NULL; -- 排除掉左連接中已包含的匹配行

示例： 查詢所有客戶和所有訂單（包括沒有訂單的客戶和沒有對(duì)應(yīng)客戶的訂單）

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL; -- 只取右連接中左表為NULL的部分（即orders有而customers沒有的行）

• 圖示： 左集合 + 右集合的并集。

e. CROSS JOIN (交叉連接 / 笛卡爾積)

• 作用： 返回兩個(gè)表中所有可能的行組合。結(jié)果集的行數(shù)是 表1行數(shù) * 表2行數(shù)。通常不是你想要的結(jié)果，除非明確需要所有組合。
• 語法：

SELECT 列名列表
FROM 表1
CROSS JOIN 表2;
-- 或者使用隱式連接（不推薦）：
SELECT 列名列表
FROM 表1, 表2;

示例： 生成所有產(chǎn)品和所有尺寸的組合

SELECT p.product_name, s.size_name
FROM products p
CROSS JOIN sizes s;

2. 多表連接 (Joining More Than Two Tables)

• 可以連續(xù)使用多個(gè) JOIN 子句連接多個(gè)表。
• 語法：

SELECT ...
FROM 表1
JOIN 表2 ON 條件
JOIN 表3 ON 條件 -- 條件可以是表2和表3的關(guān)系，或者表1和表3的關(guān)系（較少見）
...
[WHERE ...];

示例： 查詢訂單的詳細(xì)信息（客戶名、訂單日期、產(chǎn)品名、數(shù)量）

SELECT c.name, o.order_date, p.product_name, od.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

3. 自連接 (Self Join)

• 作用： 將表與其自身連接。常用于表示層次結(jié)構(gòu)（如員工-經(jīng)理關(guān)系、類別-父類別）。
• 技巧： 需要使用表別名 (Alias) 來區(qū)分同一個(gè)表的兩個(gè)“實(shí)例”。
• 示例： 查詢員工及其經(jīng)理的名字

SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
-- 使用 LEFT JOIN 是因?yàn)轫敿?jí)經(jīng)理沒有上級(jí)（manager_id 為 NULL）

4. 自然連接 (NATURAL JOIN) 和 USING 子句

• NATURAL JOIN： 自動(dòng)連接所有同名列。強(qiáng)烈不推薦使用！ 因?yàn)樗蕾囉诹忻ヅ?，不明確且容易出錯(cuò)。

SELECT ... FROM table1 NATURAL JOIN table2; -- 避免使用

USING 子句： 當(dāng)連接的兩個(gè)表具有完全相同名稱的關(guān)聯(lián)字段時(shí)，可以用 USING 簡化 ON。

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
JOIN orders o USING (customer_id); -- 等價(jià)于 ON c.customer_id = o.customer_id

第二部分：子查詢 (Subqueries)

子查詢是指嵌套在另一個(gè) SQL 查詢（主查詢）內(nèi)部的查詢。子查詢的結(jié)果被外部查詢使用。

1. 子查詢的位置 (Where Subqueries Can Be Used)

• SELECT 子句（標(biāo)量子查詢）
• FROM 子句（派生表/內(nèi)聯(lián)視圖）
• WHERE 子句（最常用）
• HAVING 子句INSERT / UPDATE / DELETE 語句的 VALUES 或 SET 部分

2. 子查詢的主要類型

a. 標(biāo)量子查詢 (Scalar Subquery)

• 特點(diǎn)： 返回單個(gè)值（一行一列）。
• 用途： 可以出現(xiàn)在任何期望單個(gè)值的地方（如 SELECT 列表、WHERE 條件中的比較運(yùn)算符右側(cè)）。
• 示例： 查詢價(jià)格高于平均價(jià)格的產(chǎn)品

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

示例： 在 SELECT 列表中使用（為每行計(jì)算一個(gè)相關(guān)值）

SELECT order_id, order_date,
       (SELECT COUNT(*) FROM order_details od WHERE od.order_id = o.order_id) AS item_count
FROM orders o;

b. 列子查詢 (Column Subquery)

• 特點(diǎn)： 返回單列多行。
• 用途： 常與 IN, ANY/SOME, ALL 運(yùn)算符一起用在 WHERE 或 HAVING 子句中。
• 示例 (IN)： 查詢至少訂購過一次’Coffee’產(chǎn)品的客戶

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    WHERE p.product_name = 'Coffee'
);

• 示例 (ANY/SOME)： 查詢價(jià)格大于任何電子產(chǎn)品價(jià)格的非電子產(chǎn)品 (> ANY 等價(jià)于 > (SELECT MIN(price) FROM ... WHERE category='Electronics'))

SELECT product_name, price
FROM products
WHERE category <> 'Electronics'
  AND price > ANY (
      SELECT price
      FROM products
      WHERE category = 'Electronics'
  );

• 示例 (ALL)： 查詢價(jià)格大于所有電子產(chǎn)品價(jià)格的非電子產(chǎn)品 (> ALL 等價(jià)于 > (SELECT MAX(price) FROM ... WHERE category='Electronics'))

SELECT product_name, price
FROM products
WHERE category <> 'Electronics'
  AND price > ALL (
      SELECT price
      FROM products
      WHERE category = 'Electronics'
  );

c. 行子查詢 (Row Subquery)

• 特點(diǎn)： 返回單行多列。
• 用途： 與行比較運(yùn)算符一起使用（較少見）。
• 示例： 查找與特定員工（ID=123）在同一個(gè)部門和同一個(gè)職位級(jí)別的員工

SELECT employee_id, name, department, job_level
FROM employees
WHERE (department, job_level) = (
    SELECT department, job_level
    FROM employees
    WHERE employee_id = 123
)
AND employee_id <> 123; -- 排除自己

d. 表子查詢 / 派生表 (Table Subquery / Derived Table)

• 特點(diǎn)： 返回一個(gè)結(jié)果集（多行多列）。
• 用途： 必須出現(xiàn)在 FROM 子句中，并且必須有別名。
• 作用： 簡化復(fù)雜查詢，創(chuàng)建臨時(shí)中間結(jié)果集。
• 示例： 計(jì)算每個(gè)類別的平均價(jià)格，并找出高于其類別平均價(jià)格的產(chǎn)品

SELECT p.product_id, p.product_name, p.category, p.price, cat_avg.avg_price
FROM products p
JOIN (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) cat_avg ON p.category = cat_avg.category
WHERE p.price > cat_avg.avg_price;

3. 相關(guān)子查詢 vs. 非相關(guān)子查詢

非相關(guān)子查詢 (Uncorrelated Subquery):

• 子查詢可以獨(dú)立運(yùn)行，不依賴于外部查詢。
• 執(zhí)行過程：先執(zhí)行子查詢得到結(jié)果集，然后外部查詢使用這個(gè)結(jié)果集。
• 上面大部分示例都是非相關(guān)的。

相關(guān)子查詢 (Correlated Subquery):

• 子查詢不能獨(dú)立運(yùn)行，它引用了外部查詢中的列。
• 執(zhí)行過程：外部查詢?nèi)〕鲆恍校瑐鬟f給子查詢；子查詢基于外部行中的值執(zhí)行；外部查詢根據(jù)子查詢返回的結(jié)果判斷是否保留該行；重復(fù)此過程處理外部查詢的每一行。
• 效率提示： 相關(guān)子查詢通常比非相關(guān)子查詢或 JOIN 慢，因?yàn)樗枰獙?duì)外部查詢的每一行都執(zhí)行一次子查詢。優(yōu)化時(shí)需謹(jǐn)慎。
• 示例： 查詢那些訂單總額超過 1000 的客戶 (在 WHERE 中使用相關(guān)子查詢)

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id -- 關(guān)聯(lián)條件
    GROUP BY o.customer_id
    HAVING SUM(o.total_amount) > 1000
);
-- 或者更高效的方式可能是使用 JOIN + GROUP BY + HAVING

示例： 在 SELECT 列表中使用相關(guān)子查詢 (如之前的 item_count 例子)

4. EXISTS 和 NOT EXISTS

專門用于相關(guān)子查詢（但也可以用于非相關(guān)）。

• EXISTS (subquery): 如果子查詢返回至少一行，則結(jié)果為 TRUE。
• NOT EXISTS (subquery): 如果子查詢返回零行，則結(jié)果為 TRUE。
• 非常高效，因?yàn)橹灰硬樵冋业揭恍衅ヅ洌?code>EXISTS 就立即返回 TRUE，不需要處理所有結(jié)果。
• 示例 (EXISTS)： 查詢至少下過一個(gè)訂單的客戶 (等價(jià)于前面的 IN 示例，但可能更高效)

SELECT customer_id, name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id -- 關(guān)聯(lián)條件
);

示例 (NOT EXISTS)： 查詢從未下過訂單的客戶

SELECT customer_id, name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id -- 關(guān)聯(lián)條件
);

關(guān)鍵注意事項(xiàng)與最佳實(shí)踐

• 明確連接條件 (ON Clause): 總是顯式地寫出連接條件 (ON 或 USING)。避免隱式連接（逗號(hào)分隔表名）和 NATURAL JOIN，它們?nèi)菀壮鲥e(cuò)且不清晰。
• 表別名 (Aliases): 在多表查詢和子查詢中，使用簡短、有意義的表別名 (FROM table AS alias 或 FROM table alias)。這能極大提高可讀性和避免列名歧義。
• 性能考慮:
  • 索引是關(guān)鍵！ 確保連接字段 (ON 子句中的列) 和 WHERE 子句中頻繁過濾的列上有索引。
  • 理解 INNER JOIN 通常比 OUTER JOIN 快。
  • 相關(guān)子查詢可能導(dǎo)致性能問題（Nested Loops）。如果可能，嘗試將其重寫為 JOIN。
  • 大型 IN 子查詢可能效率低下，考慮用 JOIN 或 EXISTS 替代。
  • 派生表（FROM 中的子查詢）可能會(huì)阻止某些優(yōu)化。有時(shí)可以用 WITH (Common Table Expression - CTE) 在 MySQL 8.0+ 中更清晰地表達(dá)。
• NULL 值處理: 在連接條件 (ON) 或 WHERE 子句中使用涉及可能為 NULL 的列進(jìn)行比較時(shí)（如 col1 = col2），如果 col1 或 col2 為 NULL，該行通常不會(huì)匹配（因?yàn)?NULL = NULL 是 UNKNOWN/NULL）。如果需要匹配 NULL，需使用 IS NULL 顯式處理。
• 可讀性和維護(hù)性:
  • 合理縮進(jìn)和格式化復(fù)雜的 SQL。
  • 分解非常復(fù)雜的查詢。使用 CTE (WITH 子句，MySQL 8.0+) 或臨時(shí)視圖（如果支持）將查詢步驟模塊化。
  • 注釋解釋復(fù)雜的邏輯。
• 測(cè)試: 逐步構(gòu)建復(fù)雜查詢。先從一個(gè)簡單的部分開始，驗(yàn)證結(jié)果，然后逐步添加 JOIN 或子查詢。使用 LIMIT 測(cè)試大數(shù)據(jù)集查詢的性能。
• 選擇 JOIN 還是子查詢？ 沒有絕對(duì)答案。通常：
  • 需要組合多個(gè)表的數(shù)據(jù)顯示時(shí)，JOIN 更自然。
  • 用于過濾或計(jì)算聚合值的條件檢查時(shí)，子查詢（尤其是 EXISTS/NOT EXISTS）可能更直觀或更高效。
  • 分析執(zhí)行計(jì)劃 (EXPLAIN) 是確定哪種方式性能更好的最終手段。

總結(jié)

掌握多表聯(lián)查 (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN) 和子查詢（標(biāo)量、列、行、表子查詢、相關(guān)/非相關(guān)、EXISTS/NOT EXISTS）是進(jìn)行復(fù)雜數(shù)據(jù)庫查詢的基礎(chǔ)。理解它們的工作原理、適用場(chǎng)景以及性能影響至關(guān)重要。通過實(shí)踐、關(guān)注索引、編寫清晰的 SQL 并利用 EXPLAIN 分析，你將能夠高效地從關(guān)聯(lián)的數(shù)據(jù)庫表中提取所需的信息。記住，清晰性和性能往往是相輔相成的。

到此這篇關(guān)于MySQL復(fù)雜SQL（多表聯(lián)查/子查詢）詳細(xì)講解的文章就介紹到這了,更多相關(guān)mysql多表聯(lián)查/子查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論