自连接、交叉连接与复杂 JOIN：一条问题链讲透

我曾把员工查经理的 SQL 里的LEFT JOIN写成INNER JOIN，结果 CEO 整个人从报表里消失了，我对着结果数了半小时人头。

这篇文章把自连接、交叉连接、复杂 JOIN 串成一条递进问题链，读完你能独立拆解多层关联查询，并避开我踩过的每一个坑。

一、场景：答案不在另一张表，而在同一张表内部

你写 JOIN 时默认要拼两张表，但如果问题的答案就藏在同一张表里呢？

这些场景的共同点，是关联的两方本质上为同一张表的不同行。这种用法叫自连接（Self Join）。

自连接不是新语法，只是在FROM子句里给同一张表取两个别名，然后像连接两张表一样连接它们。

那同一张表到底怎么连自己？先看我踩的第一个坑。

二、踩坑：CEO 为什么从结果里消失

我当时的写法，CEO 直接没了：

SELECTe.nameASemployee_name,m.nameASmanager_nameFROMemployees eJOINemployees mONe.manager_id=m.employee_id;

把 JOIN 换成 LEFT JOIN，CEO 回来了：

SELECTe.nameASemployee_name,m.nameASmanager_nameFROMemployees eLEFTJOINemployees mONe.manager_id=m.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。

三、比较与去重：同表的行怎么比、怎么不重复

根节点保住了，但同一张表还会遇到两类问题：两行之间怎么比较，重复行怎么找。

同表去重的标准写法：

SELECTa.user_id,a.name,a.emailFROMusers aJOINusers bONa.email=b.emailANDa.user_id<b.user_id;

我在a.user_id < b.user_id这个条件上翻过车，三种写法结果完全不同：

同表比较用窗口函数，只需扫描一次表，找工资高于本部门均值的员工：

SELECTname,salary,department_idFROM(SELECTname,salary,department_id,AVG(salary)OVER(PARTITIONBYdepartment_id)ASavg_salaryFROMemployees)tWHEREsalary>avg_salary;

要查 CEO 到基层员工的完整层级路径，用递归公用表表达式（Recursive CTE），锚点查询找根，递归部分找下一层：

WITHRECURSIVE orgAS(SELECTemployee_id,name,manager_id,1ASlevelFROMemployeesWHEREmanager_idISNULLUNIONALLSELECTe.employee_id,e.name,e.manager_id,o.level+1FROMemployees eJOINorg oONe.manager_id=o.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。

所有 JOIN 都等价于“先做交叉连接，再按条件过滤”。内连接过滤后即为结果；外连接再把未匹配的左表行补回，右表列填NULL。

交叉连接的合法用途：

• 生成组合：颜色表 × 尺寸表，直接生成电商 SKU
• 生成序列：数字表自交叉得到 100 个数，再配合日期函数补齐缺失日期
• 理解ON与WHERE：ON在连接过程中过滤，WHERE在连接完成后过滤；内连接下两者等价，外连接下结果可能完全不同

生成颜色与尺寸的全部组合：

SELECTc.color,s.sizeFROMcolors cCROSSJOINsizes s;

用数字表交叉连接生成 1 到 100 的序列：

WITHdigitsAS(SELECT0ASdUNIONALLSELECT1UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9)SELECTa.d+b.d*10+1ASnFROMdigits 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_id=dm.department_idANDe.salary=dm.max_salaryJOINdepartments dONe.department_id=d.department_idLEFTJOINemployees mONe.manager_id=m.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_id=ct.category_id)SELECTct.category_id,ct.name,ct.path,COUNT(p.product_id)ASproduct_countFROMcategory_tree ctLEFTJOINproducts pONp.category_id=ct.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_id=b.user_idANDb.order_date=DATE_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_date=DATE_SUB(order_date,INTERVAL1DAY)ANDprev2_date=DATE_SUB(order_date,INTERVAL2DAY);

从“能跑”到“可维护”，手段就这四类：

注意：复杂 JOIN 不靠一把梭，靠 CTE 拆解；每步有名字，才谈得上可维护。

六、总结延伸：JOIN 没有玄学，只有三步

JOIN 的本质是“笛卡尔积 + 选择 + 投影”：交叉连接提供全部组合，ON条件做选择，SELECT做投影。

三类问题各有一个关键：

• 自连接：别名、方向、根节点
• 交叉连接：先降数据量，再做配对
• 复杂 JOIN：CTE 拆解、窗口函数、索引、EXPLAIN

我现在写 JOIN 固定三个习惯：

1. 永远写清别名与连接条件，不依赖数据库默认行为
2. 能用 CTE 就不写巨型嵌套子查询
3. 先拿小数据量验证结果，再用 EXPLAIN 看执行计划

术语速查表

参考链接

• MySQL 8.0 Reference Manual - JOIN Syntax
• MySQL 8.0 Reference Manual - WITH (Common Table Expressions)
• MySQL 8.0 Reference Manual - Window Functions
• PostgreSQL Documentation - Using EXPLAIN