写在前面大家好欢迎来到MySQL全面教学系列的第6天昨天我们学习了聚合函数与分组查询掌握了数据统计分析的核心技能。今天我们将进入更加实用的领域——多表查询与JOIN。在实际业务中数据往往分散在多个表中。用户表、订单表、商品表、分类表……如何将这些表的数据关联起来进行查询是每个开发者必须掌握的技能。JOIN操作是SQL中最重要、最常用的操作之一。让我们开始今天的学习目录写在前面一、为什么需要多表查询1.1 数据库范式与数据拆分1.2 实际业务场景二、INNER JOIN内连接2.1 基本语法2.2 实战示例2.3 多表连接2.4 使用USING简化三、LEFT JOIN和RIGHT JOIN外连接3.1 LEFT JOIN左连接3.2 RIGHT JOIN右连接3.3 各种JOIN对比四、FULL JOIN全连接4.1 MySQL不支持FULL JOIN4.2 使用UNION ALL优化五、CROSS JOIN笛卡尔积六、自连接6.1 员工-上级关系6.2 查询员工及其上级6.3 查询层级结构七、实战电商系统多表查询7.1 表结构7.2 用户订单统计7.3 商品销售分析7.4 复杂业务查询八、踩坑提醒与经验之谈8.1 JOIN条件写错导致笛卡尔积8.2 ON和WHERE的区别8.3 多表JOIN的性能优化8.4 NULL值处理九、面试高频考点9.1 INNER JOIN和LEFT JOIN的区别9.2 如何找出没有订单的用户9.3 ON和WHERE在JOIN中的区别9.4 如何优化多表JOIN查询9.5 什么是笛卡尔积如何避免十、总结下一步预告参考资料互动话题一、为什么需要多表查询1.1 数据库范式与数据拆分为了避免数据冗余和保持数据一致性我们遵循数据库范式将数据拆分到不同的表中表名存储内容避免的问题users用户信息重复存储用户信息orders订单信息订单与用户解耦products商品信息商品信息统一管理categories分类信息分类信息复用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.columntable2.column;2.2 实战示例-- 查询用户及其订单信息SELECTu.user_id,u.username,u.email,o.order_id,o.order_date,o.total_amountFROMusers uINNERJOINorders oONu.user_ido.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_ido.user_idINNERJOINorder_items oiONo.order_idoi.order_idINNERJOINproducts pONoi.product_idp.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_ido.user_id;结果示例user_idusernameorder_idtotal_amount1张三1001199.001张三1002299.002李四NULLNULL3王五1003150.003.2 RIGHT JOIN右连接保留右表的所有行左表没有匹配的行用NULL填充。-- 查询所有订单及对应的用户包括用户已被删除的订单SELECTu.user_id,u.username,o.order_id,o.total_amountFROMusers uRIGHTJOINorders oONu.user_ido.user_id;经验之谈实际开发中LEFT JOIN使用频率远高于RIGHT JOIN因为RIGHT JOIN可以通过交换表的位置转换为LEFT JOIN更易理解。3.3 各种JOIN对比JOIN类型结果集使用场景INNER JOIN只返回匹配的行只关心有关联的数据LEFT JOIN返回左表所有行需要保留左表全部数据RIGHT JOIN返回右表所有行需要保留右表全部数据四、FULL JOIN全连接FULL JOIN返回两个表的所有行没有匹配的行用NULL填充。4.1 MySQL不支持FULL JOINMySQL不直接支持FULL JOIN语法但可以用UNION模拟-- 模拟FULL JOINSELECTu.user_id,u.username,o.order_idFROMusers uLEFTJOINorders oONu.user_ido.user_idUNIONSELECTu.user_id,u.username,o.order_idFROMusers uRIGHTJOINorders oONu.user_ido.user_id;4.2 使用UNION ALL优化如果确定不会有重复数据使用UNION ALL性能更好-- 找出所有用户和所有订单无关联关系SELECTuserAStype,user_idASid,usernameASnameFROMusersUNIONALLSELECTorderAStype,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_ido.user_id;六、自连接自连接是一个表与自身的连接常用于查询层级关系。6.1 员工-上级关系-- 员工表包含上级IDCREATETABLEemployees(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_idm.emp_id;结果emp_idemployeemanager1总经理NULL2技术总监总经理3销售总监总经理4开发组长技术总监5开发工程师开发组长6.3 查询层级结构-- 查询某员工的所有下级WITHRECURSIVE subordinatesAS(-- 基准从指定员工开始SELECTemp_id,emp_name,manager_id,0ASlevelFROMemployeesWHEREemp_id2-- 技术总监UNIONALL-- 递归查找下级SELECTe.emp_id,e.emp_name,e.manager_id,s.level1FROMemployees eINNERJOINsubordinates sONe.manager_ids.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_ido.user_idANDo.order_status!cancelledGROUPBYu.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_idp.category_idLEFTJOINorder_items oiONp.product_idoi.product_idLEFTJOINorders oONoi.order_ido.order_idANDo.order_statuscompletedGROUPBYc.category_id,c.category_nameORDERBYtotal_revenueDESC;7.4 复杂业务查询-- 查询购买了特定商品的用户列表SELECTDISTINCTu.user_id,u.username,u.email,o.order_id,o.create_timeFROMusers uINNERJOINorders oONu.user_ido.user_idINNERJOINorder_items oiONo.order_idoi.order_idWHEREoi.product_id1001ANDo.order_statuscompletedORDERBYo.create_timeDESC;-- 查询购买了手机类别商品的用户的其他购买记录SELECTDISTINCTu.username,p2.product_name,c.category_nameFROMusers uINNERJOINorders oONu.user_ido.user_idINNERJOINorder_items oiONo.order_idoi.order_idINNERJOINproducts pONoi.product_idp.product_idINNERJOINcategories cONp.category_idc.category_idINNERJOINorder_items oi2ONo.order_idoi2.order_idINNERJOINproducts p2ONoi2.product_idp2.product_idINNERJOINcategories c2ONp2.category_idc2.category_idWHEREc.category_name手机ANDc2.category_name!手机;八、踩坑提醒与经验之谈8.1 JOIN条件写错导致笛卡尔积错误示例-- 危险忘记ON条件SELECT*FROMusers uJOINorders o;-- 产生笛卡尔积-- 危险关联条件错误SELECT*FROMusers uJOINorders oONu.user_ido.order_id;-- 关联字段错误经验之谈写JOIN时先写ON条件再写SELECT字段。多表JOIN时建议一次只加一个表逐步验证结果。8.2 ON和WHERE的区别-- LEFT JOIN ON条件保留左表所有行SELECTu.username,o.order_id,o.total_amountFROMusers uLEFTJOINorders oONu.user_ido.user_idANDo.order_date2024-01-01;-- ON中的条件不影响左表-- LEFT JOIN WHERE条件过滤最终结果SELECTu.username,o.order_id,o.total_amountFROMusers uLEFTJOINorders oONu.user_ido.user_idWHEREo.order_date2024-01-01;-- WHERE会过滤掉NULL行关键区别ON条件在JOIN时过滤右表数据不影响左表WHERE条件在JOIN完成后过滤整个结果集8.3 多表JOIN的性能优化-- 低效写法大表在前SELECT*FROMbig_table bJOINsmall_table sONb.ids.id;-- 高效写法小表在前MySQL优化器通常会处理但显式指定更好SELECT*FROMsmall_table sJOINbig_table bONs.idb.id;-- 确保关联字段有索引CREATEINDEXidx_user_idONorders(user_id);8.4 NULL值处理-- 使用COALESCE处理NULLSELECTu.username,COALESCE(SUM(o.total_amount),0)AStotal_spentFROMusers uLEFTJOINorders oONu.user_ido.user_idGROUPBYu.user_id,u.username;-- 过滤NULL值SELECTu.username,o.order_idFROMusers uLEFTJOINorders oONu.user_ido.user_idWHEREo.order_idISNOTNULL;-- 只保留有订单的用户九、面试高频考点9.1 INNER JOIN和LEFT JOIN的区别答案INNER JOIN只返回两个表中匹配的行LEFT JOIN返回左表的所有行右表没有匹配的行用NULL填充使用场景只关心有关联的数据用INNER JOIN需要保留主表全部数据用LEFT JOIN9.2 如何找出没有订单的用户答案使用LEFT JOIN IS NULLSELECTu.user_id,u.usernameFROMusers uLEFTJOINorders oONu.user_ido.user_idWHEREo.order_idISNULL;或者使用NOT EXISTSSELECTu.user_id,u.usernameFROMusers uWHERENOTEXISTS(SELECT1FROMorders oWHEREo.user_idu.user_id);9.3 ON和WHERE在JOIN中的区别答案ON定义JOIN的匹配条件决定哪些行可以关联WHERE在JOIN完成后过滤结果集对于OUTER JOINON中的条件不会过滤主表的行WHERE会9.4 如何优化多表JOIN查询答案确保关联字段有索引小表驱动大表只SELECT需要的字段避免SELECT *使用EXPLAIN分析执行计划考虑反范式设计适当冗余字段9.5 什么是笛卡尔积如何避免答案笛卡尔积是两个表所有行的组合数据量会爆炸式增长。避免方法确保JOIN语句有ON条件检查关联条件是否正确使用EXPLAIN检查执行计划十、总结今天我们学习了MySQL多表查询与JOIN的核心知识INNER JOIN内连接只返回匹配的行最常用LEFT/RIGHT JOIN外连接保留一侧表的所有行FULL JOINMySQL不支持用UNION模拟CROSS JOIN笛卡尔积慎用自连接表与自身的连接用于层级关系查询实战应用电商系统的多表关联查询下一步预告Day7MySQL子查询与高级查询明天我们将学习更高级的查询技巧——子查询、窗口函数和CTE。这些功能强大的工具将帮助你解决更复杂的业务查询需求。子查询可以嵌套在其他查询中窗口函数可以进行复杂的分析计算CTE可以让你的SQL更加清晰易读。敬请期待参考资料MySQL 8.0 Reference Manual - JOIN Syntax互动话题你在使用JOIN时遇到过哪些性能问题是如何解决的你更喜欢使用JOIN还是子查询为什么在实际项目中你遇到过哪些复杂的JOIN场景如果觉得本文对你有帮助请点赞收藏明天见