【MySQL全面教学】MySQL多表查询与JOIN Day6（2026年）

写在前面

大家好，欢迎来到MySQL全面教学系列的第6天！昨天我们学习了聚合函数与分组查询，掌握了数据统计分析的核心技能。今天，我们将进入更加实用的领域——多表查询与JOIN。

在实际业务中，数据往往分散在多个表中。用户表、订单表、商品表、分类表……如何将这些表的数据关联起来进行查询，是每个开发者必须掌握的技能。JOIN操作是SQL中最重要、最常用的操作之一。

让我们开始今天的学习！

一、为什么需要多表查询

1.1 数据库范式与数据拆分

为了避免数据冗余和保持数据一致性，我们遵循数据库范式将数据拆分到不同的表中：

1.2 实际业务场景

假设我们要查询"用户张三的所有订单详情"，数据分散在多个表中：

-- 用户表users(user_id,username,email)-- 订单表orders(order_id,user_id,order_date,total_amount)-- 订单商品表order_items(item_id,order_id,product_id,quantity,price)-- 商品表products(product_id,product_name,category_id)

这就需要多表查询来关联这些分散的数据。

二、INNER JOIN内连接

INNER JOIN返回两个表中匹配的行，是最常用的JOIN类型。

2.1 基本语法

SELECTcolumnsFROMtable1INNERJOINtable2ONtable1.column=table2.column;

2.2 实战示例

-- 查询用户及其订单信息SELECTu.user_id,u.username,u.email,o.order_id,o.order_date,o.total_amountFROMusers uINNERJOINorders oONu.user_id=o.user_id;

2.3 多表连接

-- 查询订单详情（用户+订单+商品）SELECTu.username,o.order_id,o.order_date,p.product_name,oi.quantity,oi.price,(oi.quantity*oi.price)ASsubtotalFROMusers uINNERJOINorders oONu.user_id=o.user_idINNERJOINorder_items oiONo.order_id=oi.order_idINNERJOINproducts pONoi.product_id=p.product_id;

2.4 使用USING简化

当两个表的关联字段名称相同时，可以使用USING简化语法：

-- 等同于 ON u.user_id = o.user_idSELECTu.username,o.order_idFROMusers uINNERJOINorders oUSING(user_id);

三、LEFT JOIN和RIGHT JOIN外连接

外连接会保留一个表的所有行，即使另一个表没有匹配的行。

3.1 LEFT JOIN左连接

保留左表的所有行，右表没有匹配的行用NULL填充。

-- 查询所有用户及其订单（包括没有订单的用户）SELECTu.user_id,u.username,o.order_id,o.total_amountFROMusers uLEFTJOINorders oONu.user_id=o.user_id;

结果示例：

3.2 RIGHT JOIN右连接

保留右表的所有行，左表没有匹配的行用NULL填充。

-- 查询所有订单及对应的用户（包括用户已被删除的订单）SELECTu.user_id,u.username,o.order_id,o.total_amountFROMusers uRIGHTJOINorders oONu.user_id=o.user_id;

经验之谈：实际开发中LEFT JOIN使用频率远高于RIGHT JOIN，因为RIGHT JOIN可以通过交换表的位置转换为LEFT JOIN，更易理解。

3.3 各种JOIN对比

四、FULL JOIN全连接

FULL JOIN返回两个表的所有行，没有匹配的行用NULL填充。

4.1 MySQL不支持FULL JOIN

MySQL不直接支持FULL JOIN语法，但可以用UNION模拟：

-- 模拟FULL JOINSELECTu.user_id,u.username,o.order_idFROMusers uLEFTJOINorders oONu.user_id=o.user_idUNIONSELECTu.user_id,u.username,o.order_idFROMusers uRIGHTJOINorders oONu.user_id=o.user_id;

4.2 使用UNION ALL优化

如果确定不会有重复数据，使用UNION ALL性能更好：

-- 找出所有用户和所有订单（无关联关系）SELECT'user'AStype,user_idASid,usernameASnameFROMusersUNIONALLSELECT'order'AStype,order_idASid,CAST(total_amountASCHAR)FROMorders;

五、CROSS JOIN笛卡尔积

CROSS JOIN返回两个表的笛卡尔积，即所有可能的组合。

-- 笛卡尔积（慎用！数据量会爆炸）SELECT*FROMusersCROSSJOINorders;-- 如果users有1000条，orders有10000条，结果将是1000万条！

实际应用场景：生成测试数据、排列组合计算

-- 生成所有可能的尺码和颜色组合SELECTs.size,c.colorFROMsizes sCROSSJOINcolors c;

踩坑提醒：忘记写JOIN条件会导致隐式笛卡尔积！

-- 错误！忘记ON条件，产生笛卡尔积SELECT*FROMusers uJOINorders o;-- 危险！-- 正确写法SELECT*FROMusers uJOINorders oONu.user_id=o.user_id;

六、自连接

自连接是一个表与自身的连接，常用于查询层级关系。

6.1 员工-上级关系

-- 员工表（包含上级ID）CREATETABLEemployees(emp_idINTPRIMARYKEY,emp_nameVARCHAR(50),manager_idINT,departmentVARCHAR(50));-- 插入数据INSERTINTOemployeesVALUES(1,'总经理',NULL,'管理层'),(2,'技术总监',1,'技术部'),(3,'销售总监',1,'销售部'),(4,'开发组长',2,'技术部'),(5,'开发工程师',4,'技术部');

6.2 查询员工及其上级

-- 查询每个员工及其上级SELECTe.emp_id,e.emp_nameASemployee,m.emp_nameASmanagerFROMemployees eLEFTJOINemployees mONe.manager_id=m.emp_id;

结果：

6.3 查询层级结构

-- 查询某员工的所有下级WITHRECURSIVE subordinatesAS(-- 基准：从指定员工开始SELECTemp_id,emp_name,manager_id,0ASlevelFROMemployeesWHEREemp_id=2-- 技术总监UNIONALL-- 递归：查找下级SELECTe.emp_id,e.emp_name,e.manager_id,s.level+1FROMemployees eINNERJOINsubordinates sONe.manager_id=s.emp_id)SELECT*FROMsubordinates;

七、实战：电商系统多表查询

7.1 表结构

-- 用户表CREATETABLEusers(user_idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100),phoneVARCHAR(20),cityVARCHAR(50),register_dateDATE);-- 商品分类表CREATETABLEcategories(category_idINTPRIMARYKEY,category_nameVARCHAR(50),parent_idINT);-- 商品表CREATETABLEproducts(product_idINTPRIMARYKEY,product_nameVARCHAR(100),category_idINT,priceDECIMAL(10,2),stockINT);-- 订单表CREATETABLEorders(order_idINTPRIMARYKEY,user_idINT,order_statusVARCHAR(20),total_amountDECIMAL(10,2),create_timeDATETIME);-- 订单商品表CREATETABLEorder_items(item_idINTPRIMARYKEY,order_idINT,product_idINT,quantityINT,unit_priceDECIMAL(10,2));

7.2 用户订单统计

-- 查询用户的订单统计信息SELECTu.user_id,u.username,u.city,COUNT(DISTINCTo.order_id)ASorder_count,COALESCE(SUM(o.total_amount),0)AStotal_spent,COALESCE(AVG(o.total_amount),0)ASavg_order_amount,MAX(o.create_time)ASlast_order_timeFROMusers uLEFTJOINorders oONu.user_id=o.user_idANDo.order_status!='cancelled'GROUPBYu.user_id,u.username,u.city;

7.3 商品销售分析

-- 各类别商品销售统计SELECTc.category_name,COUNT(DISTINCTp.product_id)ASproduct_count,COUNT(oi.item_id)ASsold_count,SUM(oi.quantity)AStotal_quantity,SUM(oi.quantity*oi.unit_price)AStotal_revenueFROMcategories cLEFTJOINproducts pONc.category_id=p.category_idLEFTJOINorder_items oiONp.product_id=oi.product_idLEFTJOINorders oONoi.order_id=o.order_idANDo.order_status='completed'GROUPBYc.category_id,c.category_nameORDERBYtotal_revenueDESC;

7.4 复杂业务查询

-- 查询购买了特定商品的用户列表SELECTDISTINCTu.user_id,u.username,u.email,o.order_id,o.create_timeFROMusers uINNERJOINorders oONu.user_id=o.user_idINNERJOINorder_items oiONo.order_id=oi.order_idWHEREoi.product_id=1001ANDo.order_status='completed'ORDERBYo.create_timeDESC;-- 查询购买了"手机"类别商品的用户的其他购买记录SELECTDISTINCTu.username,p2.product_name,c.category_nameFROMusers uINNERJOINorders oONu.user_id=o.user_idINNERJOINorder_items oiONo.order_id=oi.order_idINNERJOINproducts pONoi.product_id=p.product_idINNERJOINcategories cONp.category_id=c.category_idINNERJOINorder_items oi2ONo.order_id=oi2.order_idINNERJOINproducts p2ONoi2.product_id=p2.product_idINNERJOINcategories c2ONp2.category_id=c2.category_idWHEREc.category_name='手机'ANDc2.category_name!='手机';

八、踩坑提醒与经验之谈

8.1 JOIN条件写错导致笛卡尔积

错误示例：

-- 危险！忘记ON条件SELECT*FROMusers uJOINorders o;-- 产生笛卡尔积！-- 危险！关联条件错误SELECT*FROMusers uJOINorders oONu.user_id=o.order_id;-- 关联字段错误！

经验之谈：写JOIN时，先写ON条件，再写SELECT字段。多表JOIN时，建议一次只加一个表，逐步验证结果。

8.2 ON和WHERE的区别

-- LEFT JOIN + ON条件：保留左表所有行SELECTu.username,o.order_id,o.total_amountFROMusers uLEFTJOINorders oONu.user_id=o.user_idANDo.order_date>'2024-01-01';-- ON中的条件不影响左表-- LEFT JOIN + WHERE条件：过滤最终结果SELECTu.username,o.order_id,o.total_amountFROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREo.order_date>'2024-01-01';-- WHERE会过滤掉NULL行

关键区别：

• ON条件：在JOIN时过滤右表数据，不影响左表
• WHERE条件：在JOIN完成后过滤整个结果集

8.3 多表JOIN的性能优化

-- 低效写法：大表在前SELECT*FROMbig_table bJOINsmall_table sONb.id=s.id;-- 高效写法：小表在前（MySQL优化器通常会处理，但显式指定更好）SELECT*FROMsmall_table sJOINbig_table bONs.id=b.id;-- 确保关联字段有索引CREATEINDEXidx_user_idONorders(user_id);

8.4 NULL值处理

-- 使用COALESCE处理NULLSELECTu.username,COALESCE(SUM(o.total_amount),0)AStotal_spentFROMusers uLEFTJOINorders oONu.user_id=o.user_idGROUPBYu.user_id,u.username;-- 过滤NULL值SELECTu.username,o.order_idFROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREo.order_idISNOTNULL;-- 只保留有订单的用户

九、面试高频考点

9.1 INNER JOIN和LEFT JOIN的区别？

答案：

• INNER JOIN只返回两个表中匹配的行
• LEFT JOIN返回左表的所有行，右表没有匹配的行用NULL填充
• 使用场景：只关心有关联的数据用INNER JOIN；需要保留主表全部数据用LEFT JOIN

9.2 如何找出没有订单的用户？

答案：使用LEFT JOIN + IS NULL

SELECTu.user_id,u.usernameFROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREo.order_idISNULL;

或者使用NOT EXISTS：

SELECTu.user_id,u.usernameFROMusers uWHERENOTEXISTS(SELECT1FROMorders oWHEREo.user_id=u.user_id);

9.3 ON和WHERE在JOIN中的区别？

答案：

• ON：定义JOIN的匹配条件，决定哪些行可以关联
• WHERE：在JOIN完成后过滤结果集
• 对于OUTER JOIN，ON中的条件不会过滤主表的行，WHERE会

9.4 如何优化多表JOIN查询？

答案：

1. 确保关联字段有索引
2. 小表驱动大表
3. 只SELECT需要的字段，避免SELECT *
4. 使用EXPLAIN分析执行计划
5. 考虑反范式设计，适当冗余字段

9.5 什么是笛卡尔积？如何避免？

答案：笛卡尔积是两个表所有行的组合，数据量会爆炸式增长。避免方法：

1. 确保JOIN语句有ON条件
2. 检查关联条件是否正确
3. 使用EXPLAIN检查执行计划

十、总结

今天我们学习了MySQL多表查询与JOIN的核心知识：

1. INNER JOIN：内连接，只返回匹配的行，最常用
2. LEFT/RIGHT JOIN：外连接，保留一侧表的所有行
3. FULL JOIN：MySQL不支持，用UNION模拟
4. CROSS JOIN：笛卡尔积，慎用
5. 自连接：表与自身的连接，用于层级关系查询
6. 实战应用：电商系统的多表关联查询

下一步预告

Day7：MySQL子查询与高级查询

明天我们将学习更高级的查询技巧——子查询、窗口函数和CTE。这些功能强大的工具将帮助你解决更复杂的业务查询需求。子查询可以嵌套在其他查询中，窗口函数可以进行复杂的分析计算，CTE可以让你的SQL更加清晰易读。敬请期待！

参考资料

MySQL 8.0 Reference Manual - JOIN Syntax

互动话题

1. 你在使用JOIN时遇到过哪些性能问题？是如何解决的？
2. 你更喜欢使用JOIN还是子查询？为什么？
3. 在实际项目中，你遇到过哪些复杂的JOIN场景？

如果觉得本文对你有帮助，请点赞收藏！明天见！