mysql 基础知识(6) - Join


Author: yifei / Created: April 11, 2018, 10:16 a.m. / Modified: April 11, 2018, 11:40 a.m. / Edit

看到网上有篇文章用韦恩图来讲解了一下 SQL 的 join 操作,但是感觉举的例子似乎不太实际,遂自己写了一篇,图是从那篇文章里面盗的(逃

假设我们有下面两张表,上边的是表 user,下边的是 package,表示每个用户对应的包裹

id name
1 Luke
2 Leia
3 Anakin
4 Padem
id content user_id
1 droid 3
2 lightsaber 2
3 blaster 1
4 R2D2 5

创建这两个表的语句分别是:

create table user (id integer, name string);
create table package (id integer, content string, user_id integer);
insert into user (id, name) values (1, 'Luke');
insert into user (id, name) values (2, 'Leia');
insert into user (id, name) values (3, 'Anakin');
insert into user (id, name) values (4, 'Padme');
insert into package (id, content, user_id) values (1, 'droid', 3);
insert into package (id, content, user_id) values (2, 'lightsaber', 2);
insert into package (id, content, user_id) values (3, 'blaster', 1);
insert into package (id, content, user_id) values (4, 'R2D2', 5);

Veen diagram(韦恩图)是一种表示集合的图形语言。SQL 的 join 本质上也是从集合论里面来的,可以从集合论的角度来学习和记忆 Join 的语法。

Inner Join

如果我们要选出每个有包裹的人,以及对应的包裹,可以使用 inner join。内连接(inner join)计算的是两个表的交集,也就是 A ∩ B

select
user.id, user.name, package.id, package.content 
from
user inner join package
on user.id == package.user_id;

结果一共有3列,每个表中的第四列都因为在对方表中没有而没有出现在结果里。

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid

Full Outer Join

如果我们想要选出所有的任何包裹的对应关系,哪怕是对应得人或者包裹不存在的话,可以使用 full outer join。全连接计算的是两个表的并集,也就是 A ∪ B

select
user.id, user.name, package.id, package.content
from
user full outer join package
on user.id == package.user_id;

结果一共有6列,注意其中缺字段的地方被补上了 null。另外 SQLite 不支持 full outer join。感觉这个 Join 似乎用的不是太多,因为实际情况中,往往 package.user_id 是 user.id 的外键,所以不会出现 user_id 不存在的情况。

// 结果省略

Left Outer Join

如果我们要取出每个人的包裹情况,没有包裹的也写上 null,那么这用情况下应该使用 left outer join。

select
user.id, user.name, package.id, package.content
from
user left outer join package
on user.id == package.user_id;

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid
4           Padme       NULL        NULL

Cross Join

要获得A表和B表左右可能的交叉组合的话,可以使用 cross join,也就是笛卡尔乘积。

select
user.id, user.name, package.id, package.content
from
user cross join package;

结果如下

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        1           droid
1           Luke        2           lightsaber
1           Luke        3           blaster
1           Luke        4           R2D2
2           Leia        1           droid
2           Leia        2           lightsaber
2           Leia        3           blaster
2           Leia        4           R2D2
3           Anakin      1           droid
3           Anakin      2           lightsaber
3           Anakin      3           blaster
3           Anakin      4           R2D2
4           Padme       1           droid
4           Padme       2           lightsaber
4           Padme       3           blaster
4           Padme       4           R2D2

有任何问题可以发邮件到 kongyifei (at) gmail.com 讨论