自连接查询应根据是否保留顶层节点选择join类型：需包含ceo等无上级者用left join，否则用inner join；别名须清晰（如e/m），on条件恒为e.manager_id = m.id；筛选父级条件应置于on而非where，且manager_id必须建索引。

自连接查询到底该用 INNER JOIN 还是 LEFT JOIN

取决于你是否允许“顶层节点”（比如没有上级的部门经理）出现在结果里。用 INNER JOIN 会自动过滤掉没匹配到父级记录的行；LEFT JOIN 则保留所有左表记录，父级字段为 NULL。

常见错误现象：查组织架构时漏掉 CEO（因为没人是他的上级），却以为数据丢了——其实是用了 INNER JOIN。

• 上级字段为空的记录，只在 LEFT JOIN 中可见
• INNER JOIN 更快，但语义上是“只看有明确上下级关系的对”
• 如果要统计每个员工的直属下级数，必须用 LEFT JOIN + GROUP BY + COUNT()，否则 CEO 会被跳过

别名不写清楚，ON 条件就容易写反

自连接本质是同一张表当两张用，字段名完全重复，不加别名或别名含糊，ON 里很容易把子级 manager_id 对成父级 id，反而写成 ON a.id = b.manager_id（这是错的）。

正确逻辑永远是：子级表的 manager_id 指向父级表的 id。

• 习惯性给子级表起名 e（employee），父级表起名 m（manager）
• ON e.manager_id = m.id —— 左表是员工，右表是其经理
• 如果反过来写成 ON m.manager_id = e.id，多数情况查不出数据，或结果完全错乱
• 某些数据库（如 MySQL 5.7）在 STRICT_TRANS_TABLES 模式下会直接报错

WHERE 放错位置导致自连接失效

想查“所有经理的姓名和他们带的员工数”，却在 WHERE 里写了 m.role = 'manager'，结果发现普通员工也出现在左表里——这是因为 WHERE 是在连接后过滤，而 ON 才控制连接逻辑。

更隐蔽的问题：把本该在 ON 里的条件挪到 WHERE，会让 LEFT JOIN 变相退化成 INNER JOIN。

• 筛选父级身份（如只查部门负责人），应写在 ON 后追加条件：AND m.is_dept_head = 1
• 若写在 WHERE m.is_dept_head = 1，则所有 m.is_dept_head 为 NULL 的记录（即无经理的员工）被整体剔除
• 层级深度大于 2 时（比如查“经理的经理”），WHERE 错位会导致中间层被意外截断

性能隐患：没给 manager_id 加索引，查询直接变慢十倍

自连接的驱动表通常是子级表（比如 employees），数据库要对每行去父级表找匹配，如果 manager_id 没索引，就是全表扫描嵌套，10 万行员工数据可能触发上亿次随机 IO。

另一个常被忽略的点：父级表的主键（如 id）必须有索引（通常默认有），但如果你用的是联合主键或自定义聚簇方式，得确认 id 真的能走索引。

• 执行 EXPLAIN 看 type 字段，避免出现 ALL 或 index（非唯一索引扫描）
• 复合场景如 “查某部门下三级以内的下属”，建议先用 CTE 或递归查询替代多层自连接，否则执行计划极易失控
• MySQL 8.0+、PostgreSQL 支持 WITH RECURSIVE，对深度不确定的树形结构更安全

层级数据最麻烦的不是写法，而是“哪一层该提前过滤、哪一层必须留到连接后算”。稍不注意，JOIN 就变成 Cartesian product 前奏。