看到网上 [有篇文章][1] 用韦恩图来讲解了一下 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

如果需要使用过滤条件，那么可以使用 where 语句或者 on 中的语句，不过在 inner join 中，这两者几乎是等价的。

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

Where 和 on 的区别

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

我们还可以使用 left join 来过滤不包含某些对应关系的行。

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 不存在的情况。

// 结果省略

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

Subquery

子查询是可以和 join 等价的一种查询方式。在以往（2010 年左右），子查询被认为是比较低效的查询方式，但是随着 SQL 引擎的进步，子查询应该在性能上问题不大了。

子查询其实很简单，就是一个查询的结果是另一个查询的数据基础。和函数调用有一些相似。

第一种常见用法，把子查询的结果用在 IN 中。

select * from students where id in (select sudent_id from student_awards);

第二种使用子查询来作为结果列的数值。

select id, (select count(*) from student_awards where students.id = student_awards.student_id) from students;

参考

1. https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
2. https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause
3. https://stackoverflow.com/questions/25890534/mysql-select-rows-where-left-join-is-null
4. https://stackoverflow.com/questions/2577174/join-vs-sub-query