一个连接表是根据特定的连接规则从两个其它表(真实表或生成表)中派生的表。我们支持内连接、外连接、交叉连接。
连接类型
-
交叉连接
-
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 T2
INNER 和 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)
摘自《PostgreSQL 8.2.3 中文文档》7.2.1.1节。