第6课: 多表查询与连接

INNER JOIN、LEFT JOIN、RIGHT JOIN 完整指南

连接查询原理与基础

连接查询的本质：

连接查询是将两个或多个表基于共同的字段关联起来，形成一个临时的结果集。连接的核心是通过连接条件来匹配不同表中的记录。

连接的工作原理：

笛卡尔积阶段： 首先生成两个表的笛卡尔积（所有可能的组合）
过滤阶段： 根据连接条件过滤掉不匹配的记录
结果生成阶段： 选择需要的列，应用其他条件（WHERE、GROUP BY等）

连接查询执行流程图

内连接 (INNER JOIN)

内连接返回两个表中满足连接条件的记录，是最常用的连接方式。内连接只包含匹配的记录，不匹配的记录会被过滤掉。

-- 示例1: 基本内连接
SELECT users.username, orders.order_id, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 示例2: 使用表别名简化
SELECT u.username, o.order_id, o.total_amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 100;

-- 示例3: 使用USING子句（当连接字段名相同时）
SELECT u.username, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o USING (id);

内连接的应用场景：

查询相关联的数据，如用户和其订单
多表关联查询，如订单、商品和分类
需要确保两个表都有匹配记录的场景

-- 示例3: 多表连接
SELECT
    u.username,
    o.order_id,
    p.product_name,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

左连接 (LEFT JOIN)

左连接返回左表的所有记录，即使右表中没有匹配的记录。对于右表中没有匹配的记录，右表的字段会显示为NULL。

-- 示例4: 查询所有用户及其订单（包括没有订单的用户）
SELECT u.username, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 示例5: 查询没有下过订单的用户
SELECT u.username, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

-- 示例6: 查询用户及其最新订单
SELECT u.username, o.order_id, o.total_amount, o.order_date
FROM users u
LEFT JOIN (
    SELECT user_id, MAX(order_id) AS latest_order
    FROM orders
    GROUP BY user_id
) lo ON u.id = lo.user_id
LEFT JOIN orders o ON lo.user_id = o.user_id AND lo.latest_order = o.order_id;

左连接的应用场景：

查询主表的所有记录，包括没有关联记录的情况
统计分析，如用户活跃度、商品销售情况
需要保留左表全部数据的场景

右连接 (RIGHT JOIN)

右连接返回右表的所有记录，即使左表中没有匹配的记录。对于左表中没有匹配的记录，左表的字段会显示为NULL。

-- 示例7: 查询所有订单及对应用户信息
SELECT u.username, o.order_id, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 示例8: 查询所有订单，包括没有关联用户的订单（如匿名订单）
SELECT COALESCE(u.username, '匿名用户') AS 用户名, o.order_id, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

右连接的应用场景：

查询从表的所有记录，包括没有主表关联的情况
处理外部数据导入的情况
需要保留右表全部数据的场景

注意： 右连接可以通过交换表的顺序转换为左连接，因此在实际应用中左连接更为常用。

交叉连接 (CROSS JOIN)

交叉连接返回两个表的笛卡尔积，即左表的每一行与右表的每一行都组合成一条记录。

-- 示例7: 笛卡尔积（慎用）
SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p
LIMIT 10;

-- 示例8: 生成日期序列
SELECT DATE_ADD('2024-01-01', INTERVAL n DAY) AS date
FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) AS numbers
CROSS JOIN (
    SELECT 0 AS m UNION ALL SELECT 7 UNION ALL SELECT 14 UNION ALL SELECT 21
) AS weeks
WHERE DATE_ADD('2024-01-01', INTERVAL (n + m) DAY) <= '2024-01-31';

交叉连接的应用场景：

生成测试数据或序列数据
需要所有可能组合的场景
与小表配合生成报表数据

注意： 交叉连接会产生大量数据，使用时务必谨慎，尤其是对大表操作。

自连接 (Self Join)

自连接是表与自身进行连接，常用于处理层级关系、树状结构或同一表内的关联查询。

-- 示例9: 查询员工及其上级
SELECT
    e1.name AS 员工姓名,
    e2.name AS 上级姓名
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

-- 示例10: 查询同一分类下的商品对比
SELECT
    p1.product_name AS 商品1,
    p2.product_name AS 商品2,
    p1.price AS 价格1,
    p2.price AS 价格2
FROM products p1
INNER JOIN products p2 ON p1.category = p2.category
WHERE p1.product_id < p2.product_id;

-- 示例11: 查询部门同事
SELECT
    e1.name AS 员工1,
    e2.name AS 员工2,
    e1.department
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department
WHERE e1.employee_id < e2.employee_id;

自连接的应用场景：

层级结构查询，如员工-上级关系
树状结构遍历，如组织架构、分类层级
同一表内的关联分析，如商品对比、用户相似度
历史数据比较，如同一用户的不同时间数据

连接查询最佳实践：

使用表别名：简化 SQL 语句，提高可读性和执行效率
明确连接条件：使用 ON 子句指定连接条件，避免在 WHERE 子句中隐含连接
选择合适的连接类型：根据业务需求选择 INNER/LEFT/RIGHT JOIN
索引优化：在连接字段上建立索引，特别是外键字段
避免笛卡尔积：确保连接条件正确，使用 LIMIT 限制结果集
小表驱动大表：将小表放在左边（LEFT JOIN），减少循环次数
减少连接表数量：只连接必要的表，避免不必要的关联
使用子查询优化：对于复杂连接，考虑使用子查询或临时表
合理使用 STRAIGHT_JOIN：在某些情况下强制连接顺序

连接查询性能优化

性能优化策略：

索引优化：
- 在连接字段上创建索引
- 确保连接字段的数据类型一致
- 避免在连接字段上使用函数
查询计划分析：
- 使用 EXPLAIN 分析连接查询的执行计划
- 关注 type、key、rows、Extra 等字段
- 避免出现 ALL（全表扫描）和临时表
连接顺序优化：
- MySQL 会自动优化连接顺序，但在复杂情况下可以手动指定
- 使用 STRAIGHT_JOIN 强制连接顺序
- 将选择性高的表放在前面
数据量控制：
- 使用 WHERE 条件过滤数据
- 合理使用 LIMIT 限制结果集
- 考虑分页查询，避免一次性返回过多数据

-- 示例：使用EXPLAIN分析连接查询
EXPLAIN SELECT
    u.username,
    o.order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 100
ORDER BY o.order_date DESC;

-- 优化示例：添加索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_total_amount (total_amount);
ALTER TABLE orders ADD INDEX idx_order_date (order_date);

子查询与连接

-- 示例10: 使用子查询
SELECT username, email
FROM users
WHERE id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE total_amount > 1000
);

-- 等价的连接查询（性能更好）
SELECT DISTINCT u.username, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 1000;

实战场景：电商订单分析

-- 查询用户的订单统计
SELECT
    u.username,
    u.email,
    COUNT(o.order_id) AS 订单数量,
    COALESCE(SUM(o.total_amount), 0) AS 总消费金额,
    MAX(o.order_date) AS 最后下单时间
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
ORDER BY 总消费金额 DESC;

-- 查询商品销售明细
SELECT
    p.product_name AS 商品名称,
    c.category_name AS 分类,
    COUNT(oi.order_item_id) AS 销售次数,
    SUM(oi.quantity) AS 总销量,
    SUM(oi.quantity * oi.price) AS 总销售额
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, c.category_name
ORDER BY 总销售额 DESC
LIMIT 20;

练习题：

查询所有用户及其订单信息（用户名、订单号、订单金额）
查询购买过商品的用户列表（去重）
查询从未下过订单的用户
查询每个用户的订单数量和总消费金额
查询商品及其所属分类名称
查询2024年每个月的订单数量和总金额
查询购买了"iPhone"的用户列表
查询同时购买了"手机"和"耳机"的用户

上一课下一课