多表查询（连接查询）

多表查询指从多张表中查询数据。直接查多张表会得到笛卡尔积：两个集合的所有组合情况（如 A 表 2 行 × B 表 4 行 = 8 行）。多表查询需要消除无效的笛卡尔积，也就是加上连接条件。

多表查询分类

分类	说明
内连接	查询两张表交集部分的数据
外连接	左外连接：左表所有数据 + 两表交集；右外连接：右表所有数据 + 两表交集
自连接	当前表与自身的连接查询，自连接必须使用表别名
子查询	见子查询

示例数据

CREATE TABLE dept(
    id    INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    name  VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';

INSERT INTO dept (id, name) VALUES
(1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');

CREATE TABLE emp(
    id         INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    name       VARCHAR(50) NOT NULL COMMENT '姓名',
    age        INT COMMENT '年龄',
    job        VARCHAR(20) COMMENT '职位',
    salary     INT COMMENT '薪资',
    entrydate  DATE COMMENT '入职时间',
    managerid  INT COMMENT '直属领导ID',
    dept_id    INT COMMENT '部门ID'
) COMMENT '员工表';

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5),
(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
(7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
(8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
(9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
(10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
(11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
(12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
(13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
(14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
(15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
(16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
(17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);

-- 联表查看
SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;

一、内连接

查询的是两张表交集的部分。

-- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

-- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

-- 查询每一个员工的姓名，及关联的部门的名称
SELECT emp.name, dept.name FROM emp, dept WHERE emp.dept_id = dept.id;
SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.id;      -- 用表别名简化
SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

隐式与显式写法结果相同、性能相同，差异在于可读性与可维护性，详见隐式内连接 vs 显式内连接。

二、外连接

-- 左外连接：查询表1（左表）的所有数据，包含表1和表2交集部分的数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

-- 右外连接：查询表2（右表）的所有数据，包含表1和表2交集部分的数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

-- 查询 emp 表的所有数据，和对应的部门信息
SELECT e.*, d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

-- 查询 dept 表的所有数据，和对应的员工信息
SELECT d.*, e.* FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;
SELECT d.*, e.* FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;   -- 右外连接可以改写成左外连接

左外连接会把左表中不满足连接条件的行也查出来（右表字段为 NULL），右外连接反之。

三、自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询。

-- 查询员工及其所属领导的名字
SELECT a.name, b.name FROM emp a, emp b WHERE a.managerid = b.id;
SELECT a.name '员工', b.name '领导' FROM emp a INNER JOIN emp b ON a.managerid = b.id;

-- 查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来
SELECT a.name '员工', b.name '领导' FROM emp a LEFT JOIN emp b ON a.managerid = b.id;

四、联合查询 union / union all

把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

多张表的列数必须保持一致，字段类型也需要保持一致；
union all 会把全部数据直接合并在一起，union 会对合并之后的数据去重。

-- 将薪资低于 5000 的员工，和年龄大于 50 岁的员工全部查询出来
SELECT * FROM emp WHERE salary < 5000
UNION
SELECT * FROM emp WHERE age > 50;

五、速查

-- 内连接
SELECT ... FROM 表A, 表B WHERE 条件 ...;                    -- 隐式
SELECT ... FROM 表A [INNER] JOIN 表B ON 条件 ...;            -- 显式

-- 外连接
SELECT ... FROM 表A LEFT  [OUTER] JOIN 表B ON 条件 ...;      -- 左外：左表全要
SELECT ... FROM 表A RIGHT [OUTER] JOIN 表B ON 条件 ...;      -- 右外：右表全要

-- 自连接（必须起别名）
SELECT ... FROM 表A 别名1 JOIN 表A 别名2 ON 条件 ...;

-- 联合查询
SELECT ... FROM 表A UNION [ALL] SELECT ... FROM 表B ...;