Question

SQL 中的各种联接的详细用法用途

Answer 1

连接表
一个连接表是根据特定的连接规则从两个其它表(真实表或生成表)中派生的表。我们支持内连接、外连接、交叉连接。

连接类型

交叉连接
T1 CROSS JOIN T2对每个来自 T1 和 T2 的行进行组合，生成的表将包含这样的行：所有 T1 里面的字段后面跟着所有 T2 里面的字段。如果两表分别有 N 和 M 行，连接成的表将有 N*M 行。

FROM T1 CROSS JOIN T2 等效于 FROM T1, T2 。它还等效于 FROM T1 INNER JOIN T2 ON TRUE (见下文)。

条件连接
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2INNER 和 OUTER 对所有连接类型都是可选的。INNER 为缺省。LEFT, RIGHT, FULL 隐含外连接。

连接条件在 ON 或 USING 子句里声明，或者用关键字 NATURAL 隐含地声明。连接条件判断来自两个源表中的那些行是"匹配"的，这些我们将在下面详细解释。

ON 子句是最常见的连接条件的类型：它接收一个和 WHERE 子句相同的布尔表达式。如果两个分别来自 T1 和 T2 的行在 ON 表达式上运算的结果为真，那么它们就算是匹配的行。

USING 是个一个连接条件的缩写语法：它接收一个用逗号分隔的字段名列表，这些字段必须是连接表共有的并且其值必须相同。最后，JOIN USING 会将每一对相等的输入字段输出为一个字段，其后跟着所有其它字段。因此，USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了 ON ，那么在结果里 a, b, c 字段都会有两个，而用 USING 的时候就只会有一个。

最后，NATURAL 是 USING 的缩写形式：它自动形成一个由两个表中同名的字段组成的 USING 列表(同名字段只出现一次)。

条件连接可能的类型是：

INNER JOIN
内连接。对于 T1 中的每一行 R1 ，如果能在 T2 中找到一个或多个满足连接条件的行，那么这些满足条件的每一行都在连接表中生成一行。

LEFT OUTER JOIN
左外连接。首先执行一次内连接。然后为每一个 T1 中无法在 T2 中找到匹配的行生成一行，该行中对应 T2 的列用 NULL 补齐。因此，生成的连接表里无条件地包含来自 T1 里的每一行至少一个副本。

RIGHT OUTER JOIN
右外连接。首先执行一次内连接。然后为每一个 T2 中无法在 T1 中找到匹配的行生成一行，该行中对应 T1 的列用 NULL 补齐。因此，生成的连接表里无条件地包含来自 T2 里的每一行至少一个副本。

FULL OUTER JOIN
全连接。首先执行一次内连接。然后为每一个 T1 与 T2 中找不到匹配的行生成一行，该行中无法匹配的列用 NULL 补齐。因此，生成的连接表里无条件地包含 T1 和 T2 里的每一行至少一个副本。

如果 T1 和 T2 之一或全部是可以连接的表，那么所有类型的连接都可以串连或嵌套在一起。你可以在 JOIN 子句周围使用圆括弧来控制连接顺序，如果没有圆括弧，那么 JOIN 子句从左向右嵌套。

为了解释这些问题，假设我们有一个表 t1

num | name
-----+------
1 | a
2 | b
3 | c和 t2

num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz然后我们用不同的连接方式可以获得各种结果：

=> SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
2 | b |
3 | c | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
| | 5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
| | 5 | zzz
(4 rows)用 ON 声明的连接条件也可以包含与连接不直接相关的条件。这种功能可能对某些查询很有用，但是需要我们仔细想清楚。比如：

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | |
(3 rows)

Answer 2

这张图片展示了各种联接的用途了，各种连接产生的交集或并集是不同的

Answer 3

select * from tableA inner join tableB on TableA.A=TableB.b group by id