连接查询

连接是关系模型的核心操作。这一篇讲清楚：各类 JOIN 的语义差异、ON 与 WHERE 为什么不能混用、以及半连接 / 反连接这两个高频模式。

一、连接类型全景

以 A JOIN B 为例（A 为左表，B 为右表）：

类型语义MySQL 支持
CROSS JOIN笛卡尔积，m × n 行✅
INNER JOIN只保留两侧都匹配的行✅
LEFT [OUTER] JOIN左表全保留，右表无匹配时补 NULL✅
RIGHT [OUTER] JOIN右表全保留，左表无匹配时补 NULL✅
FULL OUTER JOIN两侧都全保留❌ 需用 UNION 模拟
SELF JOIN表与自身连接，靠别名区分✅（不是独立语法）
NATURAL JOIN按同名列自动连接✅ 但强烈不推荐

MySQL 里 JOIN = INNER JOIN = CROSS JOIN

在 MySQL 中这三个关键字是同义词，区别只在于是否写了 ON。这是 MySQL 对标准的扩展，其他数据库中 CROSS JOIN 不能带 ON。为了可读性，请始终显式写 INNER JOIN ... ON ...。

同时避免使用 NATURAL JOIN 和 USING(col) 的隐式行为：一旦有人给表加了一个同名列（比如 created_at），连接条件会悄悄改变，是典型的定时炸弹。

不要再写 SQL-89 的逗号连接：

-- ❌ 旧式写法，连接条件和过滤条件混在一起，漏写一个条件就是笛卡尔积
SELECT * FROM employee e, department d WHERE e.department_id = d.id;
-- ✅ SQL-92 显式连接
SELECT * FROM employee e INNER JOIN department d ON e.department_id = d.id;

二、ON 与 WHERE 的本质区别 ⭐⭐⭐

回忆逻辑执行顺序：ON 在生成连接结果时求值，WHERE 在连接结果已经生成之后再过滤。

  • 对 INNER JOIN：两者等价（优化器都会下推），位置只影响可读性。
  • 对 OUTER JOIN：完全不等价。
-- ① 条件写在 ON：先按 (部门匹配 AND 部门名 = 'Sales') 连接，
--    不匹配的员工仍然保留，只是 d.* 全为 NULL。左表所有行都在。
SELECT e.name, d.name
FROM employee e
LEFT JOIN department d ON e.department_id = d.id AND d.name = 'Sales';

-- ② 条件写在 WHERE：先做左连接，再过滤 d.name = 'Sales'，
--    补 NULL 的那些行因为 NULL = 'Sales' 为 UNKNOWN 被丢弃，
--    结果等价于 INNER JOIN。
SELECT e.name, d.name
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
WHERE d.name = 'Sales';

记忆口诀

对右表的筛选条件写在 ON，对左表的筛选条件写在 WHERE。 一旦在 WHERE 里对右表的列做了 IS NULL 之外的判断，LEFT JOIN 就退化成了 INNER JOIN。

唯一的例外正是反连接模式：WHERE right.key IS NULL —— 它专门用来保留"没匹配上"的那部分行。

三、外连接与 FULL OUTER JOIN 模拟

MySQL 没有 FULL OUTER JOIN，标准解法是左右两次外连接取并集：

SELECT e.id, e.name, d.name AS dept
FROM employee e LEFT JOIN department d ON e.department_id = d.id
UNION            -- 必须用 UNION（去重），不能用 UNION ALL
SELECT e.id, e.name, d.name
FROM employee e RIGHT JOIN department d ON e.department_id = d.id;

UNION 会把两边都匹配上的重复行去掉，正好得到全外连接语义。缺点是要做全量去重排序，数据量大时代价不低。

四、自连接

同一张表出现两次，用别名区分。典型场景是层级结构和行间比较。

-- 查每位员工及其上级
SELECT e.name AS employee, m.name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;   -- LEFT 保证没有上级的老板也被查出

-- 查比自己上级挣得多的员工（LeetCode 181）
SELECT e.name AS Employee
FROM employee e
INNER JOIN employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

多层级（无限深度）的场景请用递归 CTE，自连接只能处理固定层数。

五、半连接与反连接 ⭐⭐

这两个概念在执行计划里出现频率极高。

半连接（Semi Join）：只判断"右表里是否存在匹配"，不复制右表的列，不会放大行数。

-- 三种等价写法
SELECT * FROM employee e WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = e.id);
SELECT * FROM employee e WHERE e.id IN (SELECT user_id FROM orders);
SELECT DISTINCT e.* FROM employee e JOIN orders o ON o.user_id = e.id;  -- 必须 DISTINCT

JOIN 会放大行数

第三种写法如果漏写 DISTINCT，一个员工有 5 笔订单就会出现 5 次。能用 EXISTS / IN 表达的"存在性判断"，不要用 JOIN + DISTINCT：前者匹配到第一条就短路返回，后者要先做完连接再去重。MySQL 8.0 的优化器可以把 IN/EXISTS 子查询自动转换为半连接（EXPLAIN 里的 FirstMatch、LooseScan、Duplicate Weedout 等策略）。

反连接（Anti Join）：找"右表里不存在匹配"的行。

-- ✅ 推荐：不受 NULL 影响，语义最清晰
SELECT * FROM employee e WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = e.id);

-- ✅ 可用：LEFT JOIN + IS NULL
SELECT e.* FROM employee e
LEFT JOIN orders o ON o.user_id = e.id
WHERE o.id IS NULL;              -- 注意要判断右表的非空列（如主键）

-- ⚠️ 慎用：子查询列可空时结果恒为空集
SELECT * FROM employee e WHERE e.id NOT IN (SELECT user_id FROM orders);

NOT IN 的 NULL 陷阱见基础篇三值逻辑。

六、多表连接的顺序问题

SELECT ...
FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c ON c.b_id = b.id      -- c 依赖 b，中间一断整条链都是 NULL
INNER JOIN d ON d.a_id = a.id;    -- ⚠️ 这里的 INNER 会把前面 LEFT 的效果吃掉

两条规则：

  1. LEFT JOIN 链条中间插入 INNER JOIN，会把整条链退化为内连接（因为内连接要求该表必须匹配）。需要保留左表全量时，后续也应继续用 LEFT JOIN。
  2. INNER JOIN 之间的书写顺序不影响结果，优化器会基于代价重排；OUTER JOIN 不满足交换律和结合律，顺序会影响结果，优化器只能在有限范围内调整。

至于连接的物理算法（Nested-Loop Join / Block Nested-Loop / Hash Join），见执行计划与调优。

实战

组合两个表

👉 Leetcode 链接-175在新窗口打开

Person 表有 personId、firstName、lastName；Address 表有 addressId、personId、city、state。 编写查询报告 Person 表中每个人的姓、名、城市和州，如果 personId 的地址不在 Address 表中，则报告为 null。

考点

"没有地址也要出现" → 必须 LEFT JOIN，INNER JOIN 会漏掉这些人。

select
    p.firstName, p.lastName, a.city, a.state
from
    Person p
left join
    Address a
on
    p.personId = a.personId

超过经理收入的员工

👉 Leetcode 链接-181在新窗口打开

Employee 表：id、name、salary、managerId。找出收入比经理高的员工。

考点

自连接。这里用 INNER JOIN：没有经理的员工（managerId IS NULL）本来就不该出现在结果里。

select
    e.name as Employee
from
    Employee e
join
    Employee m
on
    e.managerId = m.id
where
    e.salary > m.salary

部门工资最高的员工 ⭐⭐

👉 Leetcode 链接-184在新窗口打开

Employee(id, name, salary, departmentId)、Department(id, name)。查找每个部门中工资最高的员工（并列都要输出）。

考点

"分组内取极值"。两种主流解法，结果一致但执行方式不同。

解法一：IN + 分组子查询

select
    d.name as Department,
    e.name as Employee,
    e.salary as Salary
from
    Employee e
join
    Department d on e.departmentId = d.id
where
    (e.departmentId, e.salary) in (
        select departmentId, max(salary)
        from Employee
        group by departmentId
    )

这里用到了行构造器（row constructor）(a, b) IN (...)，一次比较多列，避免了先取 max 再回连的写法漏掉"跨部门同薪"的边界情况。

解法二：窗口函数（推荐）

select Department, Employee, Salary
from (
    select
        d.name as Department,
        e.name as Employee,
        e.salary as Salary,
        dense_rank() over (partition by e.departmentId order by e.salary desc) as rk
    from Employee e
    join Department d on e.departmentId = d.id
) t
where rk = 1

只扫一遍表即可，且把它改成"前三高"只需要把 rk = 1 改成 rk <= 3（即 LeetCode 185在新窗口打开）。窗口函数详见窗口函数篇。

上级经理已离职的公司员工

👉 Leetcode 链接-1978在新窗口打开

Employees(employee_id, name, manager_id, salary)。找出薪水低于 30000 且上级经理已离职（manager_id 不在表中且不为 NULL）的员工，按 employee_id 升序返回。

考点

反连接。这里 manager_id 可为空，正是 NOT IN 的雷区——必须先排除 NULL，或者直接用 NOT EXISTS。

select
    employee_id
from
    Employees e
where
    salary < 30000
    and manager_id is not null
    and not exists (
        select 1 from Employees m where m.employee_id = e.manager_id
    )
order by
    employee_id

每台机器的进程平均运行时间

👉 Leetcode 链接-1661在新窗口打开

Activity(machine_id, process_id, activity_type, timestamp)，activity_type 取 start / end。求每台机器所有进程的平均运行时长，保留 3 位小数。

考点

把"同一实体的两行"配对成一行——自连接的经典用法。

select
    a.machine_id,
    round(avg(b.timestamp - a.timestamp), 3) as processing_time
from
    Activity a
join
    Activity b
on
    a.machine_id = b.machine_id
    and a.process_id = b.process_id
    and a.activity_type = 'start'
    and b.activity_type = 'end'
group by
    a.machine_id

也可以用条件聚合一次扫描完成，通常更快：

select
    machine_id,
    round(
        avg(case when activity_type = 'end' then timestamp else -timestamp end) * 2,
        3
    ) as processing_time
from Activity
group by machine_id

每台机器每个进程恰好有一对 start/end，所以 end - start 的和除以进程数，等价于 (∑end - ∑start) / (n/2)，即上式中平均值乘 2。

下一步 👉 聚合与分组

上次更新:
贡献者: Joe