MySQL 中復雜 SQL 的核心部分：多表聯查和子查詢。這是數據庫操作中處理關聯數據的強大工具。

核心目標： 從多個相互關聯的表中組合和提取所需的數據。

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

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

1. 連接的類型 (JOIN Types)

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

• 作用： 返回兩個表中連接字段值相等的所有行組合。如果某行在其中一個表中沒有匹配的行，則不會出現在結果中。
• 語法：

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

示例： 查詢所有有訂單的客戶信息（假設 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;
-- 結果只包含那些在customers表中有記錄且在orders表中至少有一個訂單的客戶。

圖示： 兩個集合的交集部分。

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

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

SELECT 列名列表
FROM 表1
LEFT [OUTER] JOIN 表2 ON 表1.關聯字段 = 表2.關聯字段
[WHERE 條件];
-- OUTER 關鍵字通常可省略

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

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;
-- 結果包含所有客戶。對于沒有訂單的客戶，o.order_id 和 o.order_date 會是 NULL。

圖示： 整個左集合 + 與右集合的交集部分。右集合獨有的部分被舍棄。

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

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

SELECT 列名列表
FROM 表1
RIGHT [OUTER] JOIN 表2 ON 表1.關聯字段 = 表2.關聯字段
[WHERE 條件];
-- OUTER 關鍵字通?？墒÷?/pre>

• 示例： 查詢所有訂單及其對應的客戶信息（包括那些可能關聯到無效客戶的訂單 - 這種情況在良好設計的數據模型中較少見，但語法支持）

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;
-- 結果包含所有訂單。如果某個訂單的 customer_id 在 customers 表中找不到，則 c.customer_id 和 c.name 會是 NULL。

• 圖示： 整個右集合 + 與左集合的交集部分。左集合獨有的部分被舍棄。
• 注意： RIGHT JOIN 在實際應用中不如 LEFT JOIN 常見，因為通?？梢酝ㄟ^調整表順序使用 LEFT JOIN 達到相同目的。

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

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

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

示例： 查詢所有客戶和所有訂單（包括沒有訂單的客戶和沒有對應客戶的訂單）

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 (交叉連接 / 笛卡爾積)

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

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

示例： 生成所有產品和所有尺寸的組合

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

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

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

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

示例： 查詢訂單的詳細信息（客戶名、訂單日期、產品名、數量）

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)

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

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 是因為頂級經理沒有上級（manager_id 為 NULL）

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

• NATURAL JOIN： 自動連接所有同名列。強烈不推薦使用！ 因為它依賴于列名匹配，不明確且容易出錯。

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

USING 子句： 當連接的兩個表具有完全相同名稱的關聯字段時，可以用 USING 簡化 ON。

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

第二部分：子查詢 (Subqueries)

子查詢是指嵌套在另一個 SQL 查詢（主查詢）內部的查詢。子查詢的結果被外部查詢使用。

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

• SELECT 子句（標量子查詢）
• FROM 子句（派生表/內聯視圖）
• WHERE 子句（最常用）
• HAVING 子句INSERT / UPDATE / DELETE 語句的 VALUES 或 SET 部分

2. 子查詢的主要類型

a. 標量子查詢 (Scalar Subquery)

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

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

示例： 在 SELECT 列表中使用（為每行計算一個相關值）

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)

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

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)： 查詢價格大于任何電子產品價格的非電子產品 (> ANY 等價于 > (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)： 查詢價格大于所有電子產品價格的非電子產品 (> ALL 等價于 > (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)

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

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)

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

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. 相關子查詢 vs. 非相關子查詢

非相關子查詢 (Uncorrelated Subquery):

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

相關子查詢 (Correlated Subquery):

• 子查詢不能獨立運行，它引用了外部查詢中的列。
• 執(zhí)行過程：外部查詢取出一行，傳遞給子查詢；子查詢基于外部行中的值執(zhí)行；外部查詢根據子查詢返回的結果判斷是否保留該行；重復此過程處理外部查詢的每一行。
• 效率提示： 相關子查詢通常比非相關子查詢或 JOIN 慢，因為它需要對外部查詢的每一行都執(zhí)行一次子查詢。優(yōu)化時需謹慎。
• 示例： 查詢那些訂單總額超過 1000 的客戶 (在 WHERE 中使用相關子查詢)

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

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

4. EXISTS 和 NOT EXISTS

專門用于相關子查詢（但也可以用于非相關）。

• EXISTS (subquery): 如果子查詢返回至少一行，則結果為 TRUE。
• NOT EXISTS (subquery): 如果子查詢返回零行，則結果為 TRUE。
• 非常高效，因為只要子查詢找到一行匹配，EXISTS 就立即返回 TRUE，不需要處理所有結果。
• 示例 (EXISTS)： 查詢至少下過一個訂單的客戶 (等價于前面的 IN 示例，但可能更高效)

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

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

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

關鍵注意事項與最佳實踐

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

總結

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

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

最新評論