create database if not exists joindemo default character set utf8mb4;
use joindemo;
 
create table stu (
    id   int primary key,
    name varchar(30) not null
);
 
insert into stu values
(1, 'jack'), (2, 'tom'), (3, 'kity'), (4, 'nono');
 
create table exam (
    id    int,
    grade int
);
 
insert into exam values
(1, 56), (2, 76), (11, 8);

四个学生,三条成绩,两个方向都故意留了缺口:kity、nono 没有成绩,id=11 的成绩没有学生。同一组数据,四种连接分别给出 2、4、3、5 行。

-- 内连接:两边都匹配才留
select s.name, e.grade from stu s join exam e on s.id = e.id;
-- jack 56 / tom 76,2 行
 
-- 左外:左边全留
select s.name, e.grade from stu s left join exam e on s.id = e.id;
-- jack 56 / tom 76 / kity NULL / nono NULL,4 行
 
-- 右外:右边全留
select s.id, s.name, e.grade from stu s right join exam e on s.id = e.id;
-- jack 56 / tom 76 / NULL 8,3 行
 
-- 全外:MySQL 没有现成语法,用 union 拼
select s.id as stu_id, s.name, e.id as exam_id, e.grade
from stu s left join exam e on s.id = e.id
union
select s.id, s.name, e.id, e.grade
from stu s right join exam e on s.id = e.id;
-- 前 4 行再加一行 NULL 学生配 8,5 行

差的那几行就是连接替你做的决定:内连接把没配上的一律丢掉,左外保住左表,右外保住右表,全外两边都保。

left join 保的是左表,不是“小表”

left join 的“左”指写在 join 关键字左边的那张表,和表的大小、索引、业务重要性都无关。想让学生全出现,学生表必须在左边;想让成绩全出现,成绩表必须在左边:

select s.name, e.grade from stu s left join exam e on s.id = e.id;
select e.id, e.grade, s.name from exam e left join stu s on s.id = e.id;

第一条 4 行,第二条 3 行。左右外连接是镜像的:把两张表对调,把 left 换成 right,结果一样。固定写 left join、把主角放左边,读的时候不用来回倒腾。

一对一、一对多会改变行数。给 exam 补一条 jack 的 90 分,内连接里 jack 就出现两次;这不是重复数据,是一条学生记录乘两条成绩。拿这种连接去数人数,数出来的是“学生-成绩对”的条数,不是人数。

同一个条件,写在 on 和写在 where 里

内连接里,on 和 where 的条件可以互换,结果不变;外连接里不行。on 管的是“两张表的这一行能不能配上”,where 管的是“配完之后哪些行留在结果里”。

-- A:条件在 where
select s.name, e.grade from stu s
left join exam e on s.id = e.id
where e.grade >= 60;
-- 1 行:tom 76
 
-- B:条件在 on
select s.name, e.grade from stu s
left join exam e on s.id = e.id and e.grade >= 60;
-- 4 行:jack NULL / tom 76 / kity NULL / nono NULL

A 里 jack 的成绩是 56,56 >= 60 为假,整行被 where 丢掉;kity、nono 的成绩是左连接补出来的 NULL,NULL >= 60 是 UNKNOWN,也被丢掉。左连接辛苦保下来的三行,被 where 当场撤销。

B 把条件写进 on,它只负责“能不能配上”。jack 的 56 配不上 grade >= 60,但作为左表的行仍然保留,成绩补 NULL;kity、nono 同理,所以 4 行都在。

条件引用左表的列时,区别同样存在:

select s.name, e.grade from stu s
left join exam e on s.id = e.id and s.name = 'jack';

4 行都在,tom、kity、nono 的成绩变成 NULL——s.name = 'jack' 只让其他人“配不上成绩”,不删除他们作为左表行的资格。同一条条件挪进 where,就只剩 jack 一行。

这就是外连接最常见的一类事故:写了 left join,却把右表的过滤条件放进 where,保留效果被撤销。想限制匹配,写 on;想过滤最终结果,写 where。

外连接里的 count(*) 和 count(列)

create table dept (
    deptno int primary key,
    dname  varchar(20) not null
);
 
insert into dept values
(10, 'ACCOUNTING'), (20, 'RESEARCH'), (30, 'SALES'), (40, 'OPERATIONS');
 
create table employee (
    empno  int primary key,
    ename  varchar(20) not null,
    mgr    int,
    sal    decimal(7,2),
    deptno int,
    constraint fk_emp_dept foreign key (deptno) references dept(deptno)
);
 
insert into employee values
(7369, 'SMITH', 7902,  800.00, 20),
(7566, 'JONES', 7839, 2975.00, 20),
(7698, 'BLAKE', 7839, 2850.00, 30),
(7782, 'CLARK', 7839, 2450.00, 10),
(7839, 'KING',  null, 5000.00, 10),
(7902, 'FORD',  7566, 3000.00, 20);
select d.deptno, d.dname,
       count(*)       as 行数,
       count(e.empno) as 人数
from dept d
left join employee e on d.deptno = e.deptno
group by d.deptno, d.dname;

40 号部门那一行,行数 是 1,人数 是 0。左连接给它造了一行“部门 + 全 NULL 员工”,count(*) 数行,老老实实算 1;count(列) 只数非 NULL 的值,所以是 0。要数人,永远用 count(被连接表的主键列)。

同一个位置上的 sum(e.sal) 会是 NULL,不是 0。一组里全是 NULL 时没有东西可加,聚合返回 NULL;要显示 0,用 coalesce(sum(e.sal), 0) 或 ifnull。

自连接与多段连接

自连接就是同一张物理表出现两次,必须给两个别名。mgr 指向同表的 empno:

-- 每个员工和他的上司,总裁没有上司
select e.ename as 员工, m.ename as 上司
from employee e
left join employee m on e.mgr = m.empno;
-- 6 行,KING 的上司是 NULL
 
-- 换成内连接
select e.ename, m.ename
from employee e
join employee m on e.mgr = m.empno;
-- 5 行,KING 被丢掉

自连接没有额外规则,它只是让同一张表以员工和上司两个身份出现,保留谁的问题原样存在。

多段连接里,保留是一层一层传下去的:

select d.dname, e.ename as 员工, m.ename as 上司
from dept d
left join employee e on e.deptno = d.deptno
left join employee m on e.mgr = m.empno
order by d.deptno, e.empno;

第一段以 DEPT 为驱动表,40 号部门能留下;第二段匹配不到上司,只把上司那列补成 NULL,不会删掉部门行。把第一段换成 employee e left join dept d,40 号部门立刻消失。谁在第一段当驱动表,决定了后面所有段落里谁有资格留下。

交叉连接:没有条件的连接

select s.name, e.grade from stu s cross join exam e;

12 行,4 × 3。在 MySQL 里 cross join、join、inner join 是同一组语法的同义词,cross join 后面带 on 也接受;省略 on 时才是纯笛卡尔积。其他数据库里 cross join 通常不允许带 on,移植时要注意。

笛卡尔积不是“错误写法”的代名词,它是一对多展开的基础:先按所有组合铺开,再由 on 或 where 从中挑出合法的那一对。真正的错误是两张表并列却忘了写配对条件,4 个学生配 3 条成绩,悄悄变成 12 行。

全外连接:MySQL 没实现,只能拼

标准 SQL 的 full outer join 两边落单都保留。MySQL 没有实现,写 full outer join 是语法错误。两边都不漏的写法是左外和右外各跑一次,再用 union 合并:

select s.id as stu_id, s.name, e.id as exam_id, e.grade
from stu s left join exam e on s.id = e.id
union
select s.id, s.name, e.id, e.grade
from stu s right join exam e on s.id = e.id;

这里必须用 union 而不是 union all:两个分支都会输出 jack、tom 这两条匹配行,不去重就重复了。union 的代价是一次去重排序或哈希,但正确性优先。

连接怎么写才不慢

MySQL 做连接主要是嵌套循环:从驱动表取一行,去被驱动表里找匹配。被驱动表的连接列没有索引时,每一行驱动都要扫一遍整张被驱动表,代价是两表行数相乘;有索引时,每次查找是一次索引定位。

优化的大头通常在被驱动表的连接列上建索引,而不是在驱动表上。写完之后 explain 看一眼 type 和 rows,比凭感觉调参靠得住。MySQL 8.0.18 之后,等值连接在没有可用索引时还能退化成哈希连接,把被驱动表装进哈希表,至少不再按乘积涨。

几个会让索引失效的细节:连接列上套函数,例如把 e.empno 包成 cast(e.empno as char);两边的类型或字符集不匹配,MySQL 需要转换,索引就用不上;把右表的过滤条件写进 where,既改了语义,也可能让优化器少掉一个可用的连接路径。

写连接之前先答三句:谁必须全出现,每一段是几对几,条件该放 on 还是 where。这三句答完,连接的方向、行数和保留规则基本就定了。