mysql 基础知识 (6) – Join

看到网上有篇文章 用韦恩图来讲解了一下 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.userid 是 user.id 的外键,所以不会出现 userid 不存在的情况。

// 结果省略

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

及时获取更新,请关注公众号“爬虫技术学习(spider-learn)”

公众号“爬虫技术学习(spider-learn)”

About 逸飞

后端工程师

发表评论

电子邮件地址不会被公开。 必填项已用*标注