13 表的内外连接

表的内连和外连

多表查询时，数据怎么凑在一起看，就是连接。

  • 内连接（INNER JOIN） → 只保留匹配上的
  • 外连接（OUTER JOIN） → 保留一边，不管另一边有没有匹配

1. 内连接（INNER JOIN）

只显示 两个表都有匹配的行（交集），没匹配的行丢掉。示例场景：查“下了单的用户”，没下单的不要。

基本语法：

1
2
3
4
SELECT a.列, b.列
FROM 表1 a
INNER JOIN 表2 b
  ON a.关联列 = b.关联列;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 只有有部门的员工显示，OPERATIONS 部门不出现
mysql> select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

# 显示SMITH的名字和部门名称
mysql> select e.ename, d.dname  # 只要这两列
    -> from emp e               # 员工表
    -> inner join dept d        # 关联部门表
    -> on e.deptno=d.deptno     # 连接条件：部门号得一样
    -> and e.ename='SMITH';     # 过滤条件：只要 SMITH
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+

2. 外连接（OUTER JOIN）

  • 左连接 (LEFT JOIN) ： 左边表数据全保留，右边对不上就填 NULL（左为主，右为辅）。场景示例：查“所有用户及其订单”，没下单的用户也要显示。
  • 右连接 (RIGHT JOIN) ： 右边表数据全保留，但是几乎没人用，建议/一般是： 直接把表顺序换一下，写成左连接，脑子不累。

1. 左外连接（LEFT JOIN / LEFT OUTER JOIN）

保留 左表所有行，右表匹配不上就显示 NULL。基本语法：

1
2
3
4
SELECT a.列, b.列
FROM 表1 a
LEFT JOIN 表2 b
  ON a.关联列 = b.关联列;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 所有部门都显示，没人的部门 ename 为 NULL
mysql> select d.dname, e.ename from dept d left join emp e on d.deptno=e.deptno;
+------------+--------+
| dname      | ename  |
+------------+--------+
| ACCOUNTING | MILLER |
| ACCOUNTING | KING   |
| ACCOUNTING | CLARK  |
| RESEARCH   | FORD   |
| RESEARCH   | ADAMS  |
| RESEARCH   | SCOTT  |
| RESEARCH   | JONES  |
| RESEARCH   | SMITH  |
| SALES      | JAMES  |
| SALES      | TURNER |
| SALES      | BLAKE  |
| SALES      | MARTIN |
| SALES      | WARD   |
| SALES      | ALLEN  |
| OPERATIONS | NULL   |
+------------+--------+

2. 右外连接（RIGHT JOIN / RIGHT OUTER JOIN，了解）

保留 右表所有行，左表匹配不上显示 NULL。基本语法：

1
2
3
4
SELECT a.列, b.列
FROM 表1 a
RIGHT JOIN 表2 b
  ON a.关联列 = b.关联列;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 效果同上左连接，只是表顺序换了
mysql> select d.dname, e.ename from emp e right join dept d on d.deptno=e.deptno;
+------------+--------+
| dname      | ename  |
+------------+--------+
| ACCOUNTING | MILLER |
| ACCOUNTING | KING   |
| ACCOUNTING | CLARK  |
| RESEARCH   | FORD   |
| RESEARCH   | ADAMS  |
| RESEARCH   | SCOTT  |
| RESEARCH   | JONES  |
| RESEARCH   | SMITH  |
| SALES      | JAMES  |
| SALES      | TURNER |
| SALES      | BLAKE  |
| SALES      | MARTIN |
| SALES      | WARD   |
| SALES      | ALLEN  |
| OPERATIONS | NULL   |
+------------+--------+

3. 小结

类型谁保留谁可能 NULL
INNER JOIN匹配的行都匹配不上就丢掉
LEFT JOIN左表全保右表匹配不上 → NULL
RIGHT JOIN右表全保左表匹配不上 → NULL