【MySQL】多表连接查询

个人主页：Guiat
归属专栏：MySQL

正文

1. 多表连接查询概述

多表连接查询是关系型数据库中最重要的特性之一，它允许我们从多个相关表中检索数据，将它们按照指定的关系组合在一起显示。MySQL支持多种连接类型，使开发者能够灵活地进行数据检索和分析。

1.1 连接查询的作用

• 整合分散在不同表中的相关数据
• 减少数据冗余，提高存储效率
• 支持复杂的业务逻辑和数据分析
• 实现数据的完整性和一致性

1.2 MySQL支持的连接类型

MySQL支持以下几种主要的表连接类型：

2. 内连接 (INNER JOIN)

2.1 内连接的特点

• 只返回两个表中匹配的记录
• 不包含任何不匹配的记录
• 是最常用的连接类型
• 可以使用关键字INNER JOIN或简写为JOIN

2.2 内连接语法

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- 或使用简写形式
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

2.3 内连接实例

-- 创建示例表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 基本内连接查询
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- 多条件内连接
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id AND o.total_amount > 1000;

-- 使用WHERE子句进一步过滤
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01' AND c.customer_name LIKE 'A%';

2.4 多表内连接

-- 创建额外示例表
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    description TEXT
);

-- 三表内连接
SELECT c.customer_name, o.order_id, o.order_date, p.product_name, 
       oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price) AS line_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_date, o.order_id;

-- 四表及以上连接
SELECT c.customer_name, o.order_id, p.product_name, 
       oi.quantity, s.company_name AS supplier
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31';

3. 左外连接 (LEFT JOIN)

3.1 左外连接的特点

• 返回左表中的所有记录，无论是否匹配
• 如果右表中没有匹配项，则显示为NULL
• 用于查找主表中的所有记录及其关联信息
• 也称为LEFT OUTER JOIN

3.2 左外连接语法

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- 完整形式
SELECT columns
FROM table1
LEFT OUTER JOIN table2 ON table1.column = table2.column;

3.3 左外连接实例

-- 基本左连接：查找所有客户及其订单（包括没有订单的客户）
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 查找没有订单的客户
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- 带聚合函数的左连接
SELECT c.customer_id, c.customer_name, 
       COUNT(o.order_id) AS order_count,
       IFNULL(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

3.4 多表左外连接

-- 多表左外连接示例
SELECT c.customer_name, o.order_id, o.order_date, 
       p.product_name, oi.quantity
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
ORDER BY c.customer_name, o.order_date;

-- 查找特定分类下的产品以及相关订单信息（包括未售出的产品）
SELECT p.product_id, p.product_name, p.category,
       o.order_id, c.customer_name, oi.quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE p.category = 'Electronics'
ORDER BY p.product_name;

4. 右外连接 (RIGHT JOIN)

4.1 右外连接的特点

• 返回右表中的所有记录，无论是否匹配
• 如果左表中没有匹配项，则显示为NULL
• 功能上与左连接类似，只是表的方向相反
• 也称为RIGHT OUTER JOIN

4.2 右外连接语法

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- 完整形式
SELECT columns
FROM table1
RIGHT OUTER JOIN table2 ON table1.column = table2.column;

4.3 右外连接实例

-- 基本右连接：查找所有订单及其客户信息（包括没有客户信息的订单）
SELECT o.order_id, o.order_date, o.total_amount, 
       c.customer_id, c.customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

-- 查找没有客户信息的订单（例如已删除的客户）
SELECT o.order_id, o.order_date, o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- 注意：右连接通常可以转换为左连接
-- 下面两个查询是等价的
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

SELECT c.customer_name, o.order_id, o.order_date
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

4.4 多表右外连接

-- 多表右外连接示例
SELECT p.product_name, oi.quantity, o.order_id, c.customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
RIGHT JOIN order_items oi ON o.order_id = oi.order_id
RIGHT JOIN products p ON oi.product_id = p.product_id
ORDER BY p.product_name;

-- 查找所有产品及其订单情况（包括未被订购的产品）
SELECT p.product_id, p.product_name, p.category,
       oi.order_id, oi.quantity, o.order_date
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
ORDER BY p.product_id;

5. 全外连接 (FULL JOIN)

5.1 全外连接的特点

• 返回左表和右表中的所有记录
• 如果任一表中没有匹配项，则显示为NULL
• MySQL不直接支持FULL JOIN语法
• 可以通过UNION组合LEFT JOIN和RIGHT JOIN模拟

5.2 模拟全外连接语法

-- MySQL中模拟全外连接
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column
WHERE table1.column IS NULL;

5.3 模拟全外连接实例

-- 模拟全外连接：查找所有客户和订单（包括没有订单的客户和没有客户信息的订单）
SELECT c.customer_id, c.customer_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.customer_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;

-- 查找不匹配的记录（只存在于一个表中的记录）
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
UNION
SELECT c.customer_id, c.customer_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;

5.4 多表模拟全外连接

-- 多表模拟全外连接示例（产品和订单）
SELECT p.product_id, p.product_name, oi.order_id, oi.quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
UNION
SELECT p.product_id, p.product_name, oi.order_id, oi.quantity
FROM products p
RIGHT JOIN order_items oi ON p.product_id = oi.product_id
WHERE p.product_id IS NULL;

6. 交叉连接 (CROSS JOIN)

6.1 交叉连接的特点

• 返回两个表的笛卡尔积（所有可能的组合）
• 每一行都与另一个表的每一行组合
• 结果集行数 = 第一个表行数 × 第二个表行数
• 通常需要加条件限制，否则结果集可能非常大

6.2 交叉连接语法

-- 使用CROSS JOIN关键字
SELECT columns
FROM table1
CROSS JOIN table2;

-- 使用逗号分隔表（隐式交叉连接）
SELECT columns
FROM table1, table2;

6.3 交叉连接实例

-- 基本交叉连接：生成所有产品与类别的组合
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL
);

-- 显式交叉连接
SELECT p.product_id, p.product_name, c.category_id, c.category_name
FROM products p
CROSS JOIN categories c;

-- 隐式交叉连接（不推荐，因为容易与内连接混淆）
SELECT p.product_id, p.product_name, c.category_id, c.category_name
FROM products p, categories c;

-- 有条件的交叉连接
SELECT p.product_id, p.product_name, c.category_name
FROM products p
CROSS JOIN categories c
WHERE p.product_id < 100 AND c.category_id IN (1, 2, 3);

6.4 交叉连接应用场景

-- 生成日期范围内的所有日期
CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT DATE_ADD('2023-01-01', INTERVAL (n1.n + n10.n*10 + n100.n*100) DAY) AS date
FROM numbers n1
CROSS JOIN numbers n10
CROSS JOIN numbers n100
WHERE DATE_ADD('2023-01-01', INTERVAL (n1.n + n10.n*10 + n100.n*100) DAY) <= '2023-12-31'
ORDER BY date;

-- 创建价格矩阵
SELECT p1.product_name AS product, p2.product_name AS compared_with,
       p1.price AS price, p2.price AS compared_price,
       ROUND((p1.price - p2.price), 2) AS price_difference
FROM products p1
CROSS JOIN products p2
WHERE p1.product_id <> p2.product_id
ORDER BY p1.product_name, price_difference DESC;

7. 自连接 (Self Join)

7.1 自连接的特点

• 表与自身进行连接
• 在同一个表中查找相关记录
• 通常用于处理层次结构或递归关系
• 需要为表指定不同的别名

7.2 自连接语法

-- 自连接基本语法
SELECT a.column, b.column
FROM table a
JOIN table b ON a.column = b.another_column;

7.3 自连接实例

-- 创建示例员工表（含管理者关系）
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- 查找每个员工及其直接经理
SELECT e.employee_id, e.employee_name, e.department,
       m.employee_id AS manager_id, m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.department, e.employee_id;

-- 查找同一部门的员工
SELECT e1.employee_name AS employee1, 
       e2.employee_name AS employee2,
       e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department
WHERE e1.employee_id < e2.employee_id  -- 避免重复组合
ORDER BY e1.department, e1.employee_name;

-- 查找薪资比经理高的员工
SELECT e.employee_name AS employee, e.salary AS employee_salary,
       m.employee_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

7.4 层次结构查询

-- 创建示例类别表（树形结构）
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

-- 查找直接子类别
SELECT p.category_id AS parent_id, 
       p.category_name AS parent_category,
       c.category_id AS child_id, 
       c.category_name AS child_category
FROM categories p
LEFT JOIN categories c ON p.category_id = c.parent_id
ORDER BY p.category_name, c.category_name;

-- 使用递归CTE查询完整层次结构（MySQL 8.0+）
WITH RECURSIVE category_tree AS (
    -- 起始点：顶级类别
    SELECT category_id, category_name, parent_id, 0 AS level,
           category_name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归部分：查找子类别
    SELECT c.category_id, c.category_name, c.parent_id, ct.level + 1,
           CONCAT(ct.path, ' > ', c.category_name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT level, category_id, category_name, path
FROM category_tree
ORDER BY path;

8. 高级连接技术

8.1 USING 子句

当连接列具有相同的名称时，可以使用USING子句简化JOIN语法：

-- 使用ON子句的连接
SELECT c.customer_id, c.customer_name, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

-- 使用USING子句的连接（更简洁）
SELECT customer_id, c.customer_name, o.order_date
FROM customers c
JOIN orders o USING (customer_id);

-- USING子句处理多个相同列名
SELECT order_id, product_id, c.customer_name, p.product_name
FROM orders o
JOIN order_items oi USING (order_id)
JOIN customers c USING (customer_id)
JOIN products p USING (product_id);

8.2 NATURAL JOIN

NATURAL JOIN自动基于两个表中所有同名列进行连接：

-- 自然连接示例
SELECT customer_id, customer_name, order_id, order_date
FROM customers
NATURAL JOIN orders;

-- 注意：自然连接可能导致意外结果，谨慎使用
-- 如果表有多个同名列，所有这些列都会被用于连接条件

8.3 连接条件与过滤条件

理解JOIN条件和WHERE条件的区别：

-- JOIN条件决定哪些行被连接
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.total_amount > 1000;

-- WHERE条件在连接后过滤结果
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 1000;

-- 这两个查询的结果不同：
-- 第一个查询会保留所有客户，即使他们没有订单或订单金额不超过1000
-- 第二个查询只保留有订单且订单金额超过1000的客户

8.4 子查询与连接

在某些情况下，子查询可以替代连接：

-- 使用连接查询高价值客户的最近订单
SELECT c.customer_name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 10000
)
AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

-- 使用相关子查询查找每个客户的最近订单
SELECT c.customer_id, c.customer_name, 
    (SELECT order_id FROM orders 
     WHERE customer_id = c.customer_id 
     ORDER BY order_date DESC LIMIT 1) AS latest_order_id,
    (SELECT order_date FROM orders 
     WHERE customer_id = c.customer_id 
     ORDER BY order_date DESC LIMIT 1) AS latest_order_date
FROM customers c;

8.5 STRAIGHT_JOIN

在特定情况下，可以使用STRAIGHT_JOIN提示优化器使用指定的表连接顺序：

-- 强制连接顺序
SELECT c.customer_name, o.order_id, oi.product_id
FROM customers STRAIGHT_JOIN orders o ON c.customer_id = o.customer_id
STRAIGHT_JOIN order_items oi ON o.order_id = oi.order_id;

9. 连接优化技术

9.1 使用EXPLAIN分析连接查询

-- 使用EXPLAIN分析查询执行计划
EXPLAIN SELECT c.customer_name, o.order_id, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.customer_name LIKE 'A%' AND o.order_date > '2023-01-01';

9.2 索引优化

为连接列创建适当的索引可以显著提高JOIN性能：

-- 为连接列创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 创建复合索引以优化特定查询
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

9.3 查询重写技术

-- 拆分复杂连接为更简单的查询
-- 原复杂查询
SELECT c.customer_name, COUNT(DISTINCT o.order_id) AS order_count,
       SUM(oi.quantity * p.price) AS total_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.customer_id;

-- 拆分为更简单的查询
-- 首先计算每个订单的价值
CREATE TEMPORARY TABLE order_values AS
SELECT o.order_id, o.customer_id, SUM(oi.quantity * p.price) AS order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY o.order_id, o.customer_id;

-- 然后汇总客户数据
SELECT c.customer_name, COUNT(ov.order_id) AS order_count,
       SUM(ov.order_value) AS total_value
FROM customers c
JOIN order_values ov ON c.customer_id = ov.customer_id
GROUP BY c.customer_id;

9.4 分页优化

大结果集的分页查询可以通过优化连接顺序提高效率：

-- 普通分页查询（可能较慢）
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC
LIMIT 1000, 50;

-- 优化的分页查询
SELECT c.customer_name, o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC
LIMIT 1000, 50;

-- 进一步优化（先获取ID，再获取完整数据）
SELECT c.customer_name, o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_id IN (
    SELECT order_id
    FROM orders
    ORDER BY order_date DESC
    LIMIT 1000, 50
);

10. 实际应用案例

10.1 销售报表查询

-- 按月份、产品类别和销售区域的销售报表
SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    p.category,
    c.region,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.customer_id) AS customer_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_sales,
    AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
      AND o.status != 'Cancelled'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m'), p.category, c.region
ORDER BY month, p.category, total_sales DESC;

10.2 库存管理查询

-- 库存周转分析
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.current_stock,
    IFNULL(SUM(oi.quantity), 0) AS total_sold,
    CASE
        WHEN p.current_stock > 0 AND SUM(oi.quantity) IS NOT NULL 
        THEN ROUND(SUM(oi.quantity) / p.current_stock, 2)
        ELSE 0
    END AS turnover_ratio
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND 
                      o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY p.product_id, p.product_name, p.category, p.current_stock
ORDER BY turnover_ratio DESC;

-- 需要补货的产品
SELECT 
    p.product_id,
    p.product_name,
    p.current_stock,
    p.reorder_level,
    s.supplier_name,
    s.contact_person,
    s.phone
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS ordered_qty
    FROM purchase_orders po
    JOIN purchase_order_items poi ON po.po_id = poi.po_id
    WHERE po.status = 'Pending'
    GROUP BY product_id
) pending ON p.product_id = pending.product_id
WHERE p.current_stock - IFNULL(pending.ordered_qty, 0) <= p.reorder_level
ORDER BY (p.current_stock - p.reorder_level);

结语
感谢您的阅读！期待您的一键三连！欢迎指正！