MySQL复杂SQL之多表联查/子查询详细介绍(最新整理)

mysql 中复杂 sql 的核心部分：多表联查和子查询。这是数据库操作中处理关联数据的强大工具。

核心目标： 从多个相互关联的表中组合和提取所需的数据。

第一部分：多表联查 (join operations)

当你的数据模型设计良好（遵循规范化原则）时，数据会分散在多个表中，通过主键-外键关系连接。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 关键字通常可省略

• 示例： 查询所有订单及其对应的客户信息（包括那些可能关联到无效客户的订单 - 这种情况在良好设计的数据模型中较少见，但语法支持）

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)

• 可以连续使用多个 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) 来区分同一个表的两个“实例”。
• 示例： 查询员工及其经理的名字

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 子句中，并且必须有别名。
• 作用： 简化复杂查询，创建临时中间结果集。
• 示例： 计算每个类别的平均价格，并找出高于其类别平均价格的产品

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):

• 子查询可以独立运行，不依赖于外部查询。
• 执行过程：先执行子查询得到结果集，然后外部查询使用这个结果集。
• 上面大部分示例都是非相关的。

相关子查询 (correlated subquery):

• 子查询不能独立运行，它引用了外部查询中的列。
• 执行过程：外部查询取出一行，传递给子查询；子查询基于外部行中的值执行；外部查询根据子查询返回的结果判断是否保留该行；重复此过程处理外部查询的每一行。
• 效率提示： 相关子查询通常比非相关子查询或 join 慢，因为它需要对外部查询的每一行都执行一次子查询。优化时需谨慎。
• 示例： 查询那些订单总额超过 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 中的子查询）可能会阻止某些优化。有时可以用 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）可能更直观或更高效。
  • 分析执行计划 (explain) 是确定哪种方式性能更好的最终手段。

总结

掌握多表联查 (inner join, left join, right join, full join) 和子查询（标量、列、行、表子查询、相关/非相关、exists/not exists）是进行复杂数据库查询的基础。理解它们的工作原理、适用场景以及性能影响至关重要。通过实践、关注索引、编写清晰的 sql 并利用 explain 分析，你将能够高效地从关联的数据库表中提取所需的信息。记住，清晰性和性能往往是相辅相成的。

到此这篇关于mysql复杂sql（多表联查/子查询）详细讲解的文章就介绍到这了,更多相关mysql多表联查/子查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！