我曾把员工查经理的 SQL 里的LEFT JOIN写成INNER JOIN结果 CEO 整个人从报表里消失了我对着结果数了半小时人头。这篇文章把自连接、交叉连接、复杂 JOIN 串成一条递进问题链读完你能独立拆解多层关联查询并避开我踩过的每一个坑。一、场景答案不在另一张表而在同一张表内部你写 JOIN 时默认要拼两张表但如果问题的答案就藏在同一张表里呢业务场景表结构关联字段指向员工与经理employees(employee_id, name, manager_id)manager_id→ 本表employee_id分类父子层级categories(category_id, name, parent_id)parent_id→ 本表category_id同用户同天订单orders(order_id, user_id, order_date)user_id相等且order_date相等这些场景的共同点是关联的两方本质上为同一张表的不同行。这种用法叫自连接Self Join。自连接不是新语法只是在FROM子句里给同一张表取两个别名然后像连接两张表一样连接它们。那同一张表到底怎么连自己先看我踩的第一个坑。二、踩坑CEO 为什么从结果里消失我当时的写法CEO 直接没了SELECTe.nameASemployee_name,m.nameASmanager_nameFROMemployees eJOINemployees mONe.manager_idm.employee_id;把 JOIN 换成 LEFT JOINCEO 回来了SELECTe.nameASemployee_name,m.nameASmanager_nameFROMemployees eLEFTJOINemployees mONe.manager_idm.employee_id;别名让同一张表在逻辑上变成两张表e扮演员工m扮演经理。CEO 的manager_id为NULL内连接找不到匹配行根节点被丢弃外连接保留左表全部行右表列填充NULL。employees e employees m ┌────┬──────┬────────┐ ┌────┬──────┐ │ id │ name │ mgr_id │ │ id │ name │ ├────┼──────┼────────┼────►├────┼──────┤ │ 1 │ CEO │ NULL │ ✗ 无匹配行 │ 2 │ 张三 │ 1 │────►│ 1 │ CEO │ │ 3 │ 李四 │ 1 │────►│ 1 │ CEO │ └────┴──────┴────────┘ └────┴──────┘ INNER JOIN第 1 行被丢弃 LEFT JOIN第 1 行保留右侧为 NULL注意自连接先定角色再定连接类型根节点要保留就用 LEFT JOIN。三、比较与去重同表的行怎么比、怎么不重复根节点保住了但同一张表还会遇到两类问题两行之间怎么比较重复行怎么找。模式典型问题推荐写法最容易错的点层级查询员工及其经理同表 LEFT JOIN内连接丢根节点同表比较工资高于部门均值窗口函数 / 子查询比较时漏掉部门条件同表去重找同名同邮箱用户同表 JOIN 不等号不等号方向导致翻倍同表去重的标准写法SELECTa.user_id,a.name,a.emailFROMusers aJOINusers bONa.emailb.emailANDa.user_idb.user_id;我在a.user_id b.user_id这个条件上翻过车三种写法结果完全不同连接条件自己配自己重复对输出结果行数a.user_id b.user_id是全部无效—全是噪声a.user_id b.user_id否(1,2) 与 (2,1) 各一次2 倍a.user_id b.user_id否只保留一个方向1 倍同表比较用窗口函数只需扫描一次表找工资高于本部门均值的员工SELECTname,salary,department_idFROM(SELECTname,salary,department_id,AVG(salary)OVER(PARTITIONBYdepartment_id)ASavg_salaryFROMemployees)tWHEREsalaryavg_salary;要查 CEO 到基层员工的完整层级路径用递归公用表表达式Recursive CTE锚点查询找根递归部分找下一层WITHRECURSIVE orgAS(SELECTemployee_id,name,manager_id,1ASlevelFROMemployeesWHEREmanager_idISNULLUNIONALLSELECTe.employee_id,e.name,e.manager_id,o.level1FROMemployees eJOINorg oONe.manager_ido.employee_id)SELECT*FROMorgORDERBYlevel,employee_id;还要注意连接方向e.manager_id m.employee_id表示“e 的经理是 m”反写成m.manager_id e.employee_id整条上下级关系就倒了。注意自连接去重靠不等号定方向** 只留一条结果翻倍。**四、交叉连接漏掉 ON 是灾难还是另一种原材料连接条件写错会丢行、会翻倍那如果干脆不写条件会发生什么我有一次漏写ON测试库瞬间返回几十万行客户端直接卡死。这背后就是交叉连接Cross Join不指定任何连接条件左表每行与右表每行逐一配对结果为笛卡尔积Cartesian Product行数等于 m × n。左表 m 行右表 n 行结果 m × n 行10101001,0001,0001,000,000100,000100,00010,000,000,000所有 JOIN 都等价于“先做交叉连接再按条件过滤”。内连接过滤后即为结果外连接再把未匹配的左表行补回右表列填NULL。交叉连接的合法用途生成组合颜色表 × 尺寸表直接生成电商 SKU生成序列数字表自交叉得到 100 个数再配合日期函数补齐缺失日期理解ON与WHEREON在连接过程中过滤WHERE在连接完成后过滤内连接下两者等价外连接下结果可能完全不同生成颜色与尺寸的全部组合SELECTc.color,s.sizeFROMcolors cCROSSJOINsizes s;用数字表交叉连接生成 1 到 100 的序列WITHdigitsAS(SELECT0ASdUNIONALLSELECT1UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9)SELECTa.db.d*101ASnFROMdigits aCROSSJOINdigits bORDERBYn;控制风险只有一条先用 CTE 过滤、降数据量再 CROSS JOIN绝不让两张原始大表直接交叉。注意交叉连接是 JOIN 的原材料不是废物但大表直接交叉等于给数据库埋雷。五、复杂 JOIN三个真实业务案例逐个拆单点都清楚了可真实业务是多层关联叠在一起该怎么下手案例一找出每个部门工资最高的员工及其经理。employees │ GROUP BY department_id ▼ dept_max部门最高工资 │ JOIN e.department_id dm.department_id │ AND e.salary dm.max_salary ▼ JOIN departments ── 取部门名 ▼ LEFT JOIN employees m ── 取经理名WITHdept_maxAS(SELECTdepartment_id,MAX(salary)ASmax_salaryFROMemployeesGROUPBYdepartment_id)SELECTe.nameASemployee_name,e.salary,d.department_name,m.nameASmanager_nameFROMemployees eJOINdept_max dmONe.department_iddm.department_idANDe.salarydm.max_salaryJOINdepartments dONe.department_idd.department_idLEFTJOINemployees mONe.manager_idm.employee_id;我漏过department_id条件只写salary max_salary结果别的部门同薪资的员工被串了进来。“本部门”和“最高工资”两个条件必须同时成立。案例二分类树展开祖先路径并统计每个分类的商品数。WITHRECURSIVE category_treeAS(SELECTcategory_id,name,parent_id,nameASpathFROMcategoriesWHEREparent_idISNULLUNIONALLSELECTc.category_id,c.name,c.parent_id,CONCAT(ct.path, ,c.name)FROMcategories cJOINcategory_tree ctONc.parent_idct.category_id)SELECTct.category_id,ct.name,ct.path,COUNT(p.product_id)ASproduct_countFROMcategory_tree ctLEFTJOINproducts pONp.category_idct.category_idGROUPBYct.category_id,ct.name,ct.pathORDERBYct.path;LEFT JOIN保证没有商品的分类不消失COUNT(p.product_id)统计的是商品数写成COUNT(*)会把补出来的NULL行也算进去。案例三找出连续下单的用户。连续两天用自连接SELECTDISTINCTa.user_id,a.order_dateFROMorders aJOINorders bONa.user_idb.user_idANDb.order_dateDATE_ADD(a.order_date,INTERVAL1DAY);连续三天再自连接就很绕用窗口函数LAG更清晰WITHdailyAS(SELECTDISTINCTuser_id,order_dateFROMorders)SELECTuser_id,order_dateFROM(SELECTuser_id,order_date,LAG(order_date,1)OVER(PARTITIONBYuser_idORDERBYorder_date)ASprev_date,LAG(order_date,2)OVER(PARTITIONBYuser_idORDERBYorder_date)ASprev2_dateFROMdaily)tWHEREprev_dateDATE_SUB(order_date,INTERVAL1DAY)ANDprev2_dateDATE_SUB(order_date,INTERVAL2DAY);从“能跑”到“可维护”手段就这四类手段做法作用CTE 拆分每步中间结果命名可读、可单独调试窗口函数AVG OVER、LAG、LEAD单次扫描替代部分自连接索引manager_id、department_id、user_id等连接字段连接提速可达数量级EXPLAIN查看执行计划确认驱动表与连接顺序注意复杂 JOIN 不靠一把梭靠 CTE 拆解每步有名字才谈得上可维护。六、总结延伸JOIN 没有玄学只有三步JOIN 的本质是“笛卡尔积 选择 投影”交叉连接提供全部组合ON条件做选择SELECT做投影。三类问题各有一个关键自连接别名、方向、根节点交叉连接先降数据量再做配对复杂 JOINCTE 拆解、窗口函数、索引、EXPLAIN我现在写 JOIN 固定三个习惯永远写清别名与连接条件不依赖数据库默认行为能用 CTE 就不写巨型嵌套子查询先拿小数据量验证结果再用 EXPLAIN 看执行计划术语速查表术语英文一句话解释自连接Self Join同一张表取两个别名互相连接交叉连接Cross Join不写连接条件返回两表全部组合笛卡尔积Cartesian Product两表行两两配对行数为 m × n递归公用表表达式Recursive CTE锚点查询加递归查询用于层级展开窗口函数Window Function不折叠行的聚合计算如AVG OVER、LAG内连接Inner Join只保留两表匹配成功的行外连接Outer Join保留左表全部行未匹配处右表填NULL执行计划Execution Plan数据库执行 SQL 的步骤与连接顺序说明参考链接MySQL 8.0 Reference Manual - JOIN SyntaxMySQL 8.0 Reference Manual - WITH (Common Table Expressions)MySQL 8.0 Reference Manual - Window FunctionsPostgreSQL Documentation - Using EXPLAIN 自连接、交叉连接与复杂 JOIN：一条问题链讲透
我曾把员工查经理的 SQL 里的LEFT JOIN写成INNER JOIN结果 CEO 整个人从报表里消失了我对着结果数了半小时人头。这篇文章把自连接、交叉连接、复杂 JOIN 串成一条递进问题链读完你能独立拆解多层关联查询并避开我踩过的每一个坑。一、场景答案不在另一张表而在同一张表内部你写 JOIN 时默认要拼两张表但如果问题的答案就藏在同一张表里呢业务场景表结构关联字段指向员工与经理employees(employee_id, name, manager_id)manager_id→ 本表employee_id分类父子层级categories(category_id, name, parent_id)parent_id→ 本表category_id同用户同天订单orders(order_id, user_id, order_date)user_id相等且order_date相等这些场景的共同点是关联的两方本质上为同一张表的不同行。这种用法叫自连接Self Join。自连接不是新语法只是在FROM子句里给同一张表取两个别名然后像连接两张表一样连接它们。那同一张表到底怎么连自己先看我踩的第一个坑。二、踩坑CEO 为什么从结果里消失我当时的写法CEO 直接没了SELECTe.nameASemployee_name,m.nameASmanager_nameFROMemployees eJOINemployees mONe.manager_idm.employee_id;把 JOIN 换成 LEFT JOINCEO 回来了SELECTe.nameASemployee_name,m.nameASmanager_nameFROMemployees eLEFTJOINemployees mONe.manager_idm.employee_id;别名让同一张表在逻辑上变成两张表e扮演员工m扮演经理。CEO 的manager_id为NULL内连接找不到匹配行根节点被丢弃外连接保留左表全部行右表列填充NULL。employees e employees m ┌────┬──────┬────────┐ ┌────┬──────┐ │ id │ name │ mgr_id │ │ id │ name │ ├────┼──────┼────────┼────►├────┼──────┤ │ 1 │ CEO │ NULL │ ✗ 无匹配行 │ 2 │ 张三 │ 1 │────►│ 1 │ CEO │ │ 3 │ 李四 │ 1 │────►│ 1 │ CEO │ └────┴──────┴────────┘ └────┴──────┘ INNER JOIN第 1 行被丢弃 LEFT JOIN第 1 行保留右侧为 NULL注意自连接先定角色再定连接类型根节点要保留就用 LEFT JOIN。三、比较与去重同表的行怎么比、怎么不重复根节点保住了但同一张表还会遇到两类问题两行之间怎么比较重复行怎么找。模式典型问题推荐写法最容易错的点层级查询员工及其经理同表 LEFT JOIN内连接丢根节点同表比较工资高于部门均值窗口函数 / 子查询比较时漏掉部门条件同表去重找同名同邮箱用户同表 JOIN 不等号不等号方向导致翻倍同表去重的标准写法SELECTa.user_id,a.name,a.emailFROMusers aJOINusers bONa.emailb.emailANDa.user_idb.user_id;我在a.user_id b.user_id这个条件上翻过车三种写法结果完全不同连接条件自己配自己重复对输出结果行数a.user_id b.user_id是全部无效—全是噪声a.user_id b.user_id否(1,2) 与 (2,1) 各一次2 倍a.user_id b.user_id否只保留一个方向1 倍同表比较用窗口函数只需扫描一次表找工资高于本部门均值的员工SELECTname,salary,department_idFROM(SELECTname,salary,department_id,AVG(salary)OVER(PARTITIONBYdepartment_id)ASavg_salaryFROMemployees)tWHEREsalaryavg_salary;要查 CEO 到基层员工的完整层级路径用递归公用表表达式Recursive CTE锚点查询找根递归部分找下一层WITHRECURSIVE orgAS(SELECTemployee_id,name,manager_id,1ASlevelFROMemployeesWHEREmanager_idISNULLUNIONALLSELECTe.employee_id,e.name,e.manager_id,o.level1FROMemployees eJOINorg oONe.manager_ido.employee_id)SELECT*FROMorgORDERBYlevel,employee_id;还要注意连接方向e.manager_id m.employee_id表示“e 的经理是 m”反写成m.manager_id e.employee_id整条上下级关系就倒了。注意自连接去重靠不等号定方向** 只留一条结果翻倍。**四、交叉连接漏掉 ON 是灾难还是另一种原材料连接条件写错会丢行、会翻倍那如果干脆不写条件会发生什么我有一次漏写ON测试库瞬间返回几十万行客户端直接卡死。这背后就是交叉连接Cross Join不指定任何连接条件左表每行与右表每行逐一配对结果为笛卡尔积Cartesian Product行数等于 m × n。左表 m 行右表 n 行结果 m × n 行10101001,0001,0001,000,000100,000100,00010,000,000,000所有 JOIN 都等价于“先做交叉连接再按条件过滤”。内连接过滤后即为结果外连接再把未匹配的左表行补回右表列填NULL。交叉连接的合法用途生成组合颜色表 × 尺寸表直接生成电商 SKU生成序列数字表自交叉得到 100 个数再配合日期函数补齐缺失日期理解ON与WHEREON在连接过程中过滤WHERE在连接完成后过滤内连接下两者等价外连接下结果可能完全不同生成颜色与尺寸的全部组合SELECTc.color,s.sizeFROMcolors cCROSSJOINsizes s;用数字表交叉连接生成 1 到 100 的序列WITHdigitsAS(SELECT0ASdUNIONALLSELECT1UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9)SELECTa.db.d*101ASnFROMdigits aCROSSJOINdigits bORDERBYn;控制风险只有一条先用 CTE 过滤、降数据量再 CROSS JOIN绝不让两张原始大表直接交叉。注意交叉连接是 JOIN 的原材料不是废物但大表直接交叉等于给数据库埋雷。五、复杂 JOIN三个真实业务案例逐个拆单点都清楚了可真实业务是多层关联叠在一起该怎么下手案例一找出每个部门工资最高的员工及其经理。employees │ GROUP BY department_id ▼ dept_max部门最高工资 │ JOIN e.department_id dm.department_id │ AND e.salary dm.max_salary ▼ JOIN departments ── 取部门名 ▼ LEFT JOIN employees m ── 取经理名WITHdept_maxAS(SELECTdepartment_id,MAX(salary)ASmax_salaryFROMemployeesGROUPBYdepartment_id)SELECTe.nameASemployee_name,e.salary,d.department_name,m.nameASmanager_nameFROMemployees eJOINdept_max dmONe.department_iddm.department_idANDe.salarydm.max_salaryJOINdepartments dONe.department_idd.department_idLEFTJOINemployees mONe.manager_idm.employee_id;我漏过department_id条件只写salary max_salary结果别的部门同薪资的员工被串了进来。“本部门”和“最高工资”两个条件必须同时成立。案例二分类树展开祖先路径并统计每个分类的商品数。WITHRECURSIVE category_treeAS(SELECTcategory_id,name,parent_id,nameASpathFROMcategoriesWHEREparent_idISNULLUNIONALLSELECTc.category_id,c.name,c.parent_id,CONCAT(ct.path, ,c.name)FROMcategories cJOINcategory_tree ctONc.parent_idct.category_id)SELECTct.category_id,ct.name,ct.path,COUNT(p.product_id)ASproduct_countFROMcategory_tree ctLEFTJOINproducts pONp.category_idct.category_idGROUPBYct.category_id,ct.name,ct.pathORDERBYct.path;LEFT JOIN保证没有商品的分类不消失COUNT(p.product_id)统计的是商品数写成COUNT(*)会把补出来的NULL行也算进去。案例三找出连续下单的用户。连续两天用自连接SELECTDISTINCTa.user_id,a.order_dateFROMorders aJOINorders bONa.user_idb.user_idANDb.order_dateDATE_ADD(a.order_date,INTERVAL1DAY);连续三天再自连接就很绕用窗口函数LAG更清晰WITHdailyAS(SELECTDISTINCTuser_id,order_dateFROMorders)SELECTuser_id,order_dateFROM(SELECTuser_id,order_date,LAG(order_date,1)OVER(PARTITIONBYuser_idORDERBYorder_date)ASprev_date,LAG(order_date,2)OVER(PARTITIONBYuser_idORDERBYorder_date)ASprev2_dateFROMdaily)tWHEREprev_dateDATE_SUB(order_date,INTERVAL1DAY)ANDprev2_dateDATE_SUB(order_date,INTERVAL2DAY);从“能跑”到“可维护”手段就这四类手段做法作用CTE 拆分每步中间结果命名可读、可单独调试窗口函数AVG OVER、LAG、LEAD单次扫描替代部分自连接索引manager_id、department_id、user_id等连接字段连接提速可达数量级EXPLAIN查看执行计划确认驱动表与连接顺序注意复杂 JOIN 不靠一把梭靠 CTE 拆解每步有名字才谈得上可维护。六、总结延伸JOIN 没有玄学只有三步JOIN 的本质是“笛卡尔积 选择 投影”交叉连接提供全部组合ON条件做选择SELECT做投影。三类问题各有一个关键自连接别名、方向、根节点交叉连接先降数据量再做配对复杂 JOINCTE 拆解、窗口函数、索引、EXPLAIN我现在写 JOIN 固定三个习惯永远写清别名与连接条件不依赖数据库默认行为能用 CTE 就不写巨型嵌套子查询先拿小数据量验证结果再用 EXPLAIN 看执行计划术语速查表术语英文一句话解释自连接Self Join同一张表取两个别名互相连接交叉连接Cross Join不写连接条件返回两表全部组合笛卡尔积Cartesian Product两表行两两配对行数为 m × n递归公用表表达式Recursive CTE锚点查询加递归查询用于层级展开窗口函数Window Function不折叠行的聚合计算如AVG OVER、LAG内连接Inner Join只保留两表匹配成功的行外连接Outer Join保留左表全部行未匹配处右表填NULL执行计划Execution Plan数据库执行 SQL 的步骤与连接顺序说明参考链接MySQL 8.0 Reference Manual - JOIN SyntaxMySQL 8.0 Reference Manual - WITH (Common Table Expressions)MySQL 8.0 Reference Manual - Window FunctionsPostgreSQL Documentation - Using EXPLAIN