SQL JOIN 内连接 外连接 (自己总结)

SQL JOIN 内连接 外连接 (自己总结)

经典图示

去掉使用了where 条件的三种情况，主要就是内连接，左连接，右连接，全连接四种类型。

只返回两张表匹配的记录，这叫内连接（inner join）。
返回匹配的记录，以及表A 多余的记录，这叫左连接（left join）。
返回匹配的记录，以及表B 多余的记录，这叫右连接（right join）。
返回匹配的记录，以及表A 和表B 各自的多余记录，这叫全连接（full join）。

实验数据准备

create database if not exists testdb;

use testdb;

drop table if exists a, b;

create table a (
  id int not null,
  name varchar(10) not null,
  primary key(id)
);

create table b (
  id int not null,
  job varchar(10) not null,
  aid int not null,
  primary key(id)
);

insert into a values (1, 'XIAOMING');
insert into a values (2, 'XIAOHONG');
insert into a values (3, 'XIAOHUA');

insert into b values (1, 'Doctor', 1);
insert into b values (2, 'Teacher', 2);
insert into b values (3, 'Teacher', 4);

无连接

-- 在没有关联的情况下，查询两个表会得到迪卡乘积的结果。
-- 即表A 有3 条记录，表B 有3 条记录，那么查询出来就有3x3=9 条记录。
mysql> select * from a, b;
+----+----------+----+---------+-----+
| id | name     | id | job     | aid |
+----+----------+----+---------+-----+
|  3 | XIAOHUA  |  1 | Doctor  |   1 |
|  2 | XIAOHONG |  1 | Doctor  |   1 |
|  1 | XIAOMING |  1 | Doctor  |   1 |
|  3 | XIAOHUA  |  2 | Teacher |   2 |
|  2 | XIAOHONG |  2 | Teacher |   2 |
|  1 | XIAOMING |  2 | Teacher |   2 |
|  3 | XIAOHUA  |  3 | Teacher |   4 |
|  2 | XIAOHONG |  3 | Teacher |   4 |
|  1 | XIAOMING |  3 | Teacher |   4 |
+----+----------+----+---------+-----+

小技巧：mysql>提示符下输入system clear在Linux和macOS下可以清屏。😏

内连接

-- 由于查询结果是表A 和表B 的交集，内容同时属于表A 和表B，是AB 内部的一部分，所以叫内连接。
mysql> select * from a inner join b on a.id = b.aid;
+----+----------+----+---------+-----+
| id | name     | id | job     | aid |
+----+----------+----+---------+-----+
|  1 | XIAOMING |  1 | Doctor  |   1 |
|  2 | XIAOHONG |  2 | Teacher |   2 |
+----+----------+----+---------+-----+

-- 默认只写join 等同于inner join
mysql> select * from a join b on a.id = b.aid;
+----+----------+----+---------+-----+
| id | name     | id | job     | aid |
+----+----------+----+---------+-----+
|  1 | XIAOMING |  1 | Doctor  |   1 |
|  2 | XIAOHONG |  2 | Teacher |   2 |
+----+----------+----+---------+-----+

-- 用where 也可以得到和内连接一样的结果，但是处理步骤是不一样的。
mysql> select * from a, b where a.id = b.aid;
+----+----------+----+---------+-----+
| id | name     | id | job     | aid |
+----+----------+----+---------+-----+
|  1 | XIAOMING |  1 | Doctor  |   1 |
|  2 | XIAOHONG |  2 | Teacher |   2 |
+----+----------+----+---------+-----+

on 和where 的区别

WHERE
  1. 生成迪卡乘积的临时虚拟表，内容等同于 select * from a, b;
  2. 根据where 条件过滤临时虚拟表，获得最终查询结果。
INNER JOIN ON
  1. 直接根据on 后面的条件生成最终查询结果。

外连接

关于外连接，我的理解是查询的数据包含了两表间的交集和交集以外的内容。
按照出现在FROM后面的顺序，先左后右，所以SELECT * FROM A, B则A是左表，B是右表。
当然，以上SELECT查询中A、B两表并未做任何连接，只是用来解释说明左右表的位置而已。

左连接

-- 左连接就是以左表为基准，左表的记录将会全部显示，如右表没有对应的记录则显示`NULL`。
mysql> select * from a left outer join b on a.id = b.aid;
+----+----------+------+---------+------+
| id | name     | id   | job     | aid  |
+----+----------+------+---------+------+
|  1 | XIAOMING |    1 | Doctor  |    1 |
|  2 | XIAOHONG |    2 | Teacher |    2 |
|  3 | XIAOHUA  | NULL | NULL    | NULL |
+----+----------+------+---------+------+

-- 默认只写left join 等同于 left outer join
mysql> select * from a left join b on a.id = b.aid;
+----+----------+------+---------+------+
| id | name     | id   | job     | aid  |
+----+----------+------+---------+------+
|  1 | XIAOMING |    1 | Doctor  |    1 |
|  2 | XIAOHONG |    2 | Teacher |    2 |
|  3 | XIAOHUA  | NULL | NULL    | NULL |
+----+----------+------+---------+------+

右连接

-- 右连接就是以右表为基准，右表的记录将会全部显示，如左表没有对应的记录则显示`NULL`。
mysql> select * from a right outer join b on a.id = b.aid;
+------+----------+----+---------+-----+
| id   | name     | id | job     | aid |
+------+----------+----+---------+-----+
|    1 | XIAOMING |  1 | Doctor  |   1 |
|    2 | XIAOHONG |  2 | Teacher |   2 |
| NULL | NULL     |  3 | Teacher |   4 |
+------+----------+----+---------+-----+

-- 默认只写right join 等同于right outer join
mysql> select * from a right join b on a.id = b.aid;
+------+----------+----+---------+-----+
| id   | name     | id | job     | aid |
+------+----------+----+---------+-----+
|    1 | XIAOMING |  1 | Doctor  |   1 |
|    2 | XIAOHONG |  2 | Teacher |   2 |
| NULL | NULL     |  3 | Teacher |   4 |
+------+----------+----+---------+-----+

全连接

-- MySQL8.0 仍不支持全连接，以下在SQL SERVER 2017 中执行通过，估计MSSQL 2000 以上就可以
-- 全连接会显示左右表的所有记录，互相不匹配的字段用[NULL] 表示，以下结果中显示为空。
select * from a full outer join b on a.id = b.aid;
+-----------+----------+-----------+----------+-----------+
|id         |name      |id         |job       |aid        |
|-----------|----------|-----------|----------|-----------|
|1          |XIAOMING  |1          |Doctor    |1          |
|2          |XIAOHONG  |2          |Teacher   |2          |
|3          |XIAOHUA   |           |          |           |
|           |          |3          |Teacher   |4          |

-- 默认只写full join 等同于full outer join
select * from a full join b on a.id = b.aid;
+-----------+----------+-----------+----------+-----------+
|id         |name      |id         |job       |aid        |
|-----------|----------|-----------|----------|-----------|
|1          |XIAOMING  |1          |Doctor    |1          |
|2          |XIAOHONG  |2          |Teacher   |2          |
|3          |XIAOHUA   |           |          |           |
|           |          |3          |Teacher   |4          |

参考文档

数据库表连接的简单解释
SQL 连接(JOIN)
sql join 的on 和where 区别