今天开发人员跑来咨询使用 left join 进行表关联的事情，这个查询 SQL 简单为以下: 查询SQL1

1
2
3
4
5

select a.column_1,  
 a.column_2,  
 b.column_3  
from ( 结果集1 ) a  
left join b on a.id=b.id;

其中”结果集1”为部分表关联查询的中间结果，具体代码这里省略，开发人员的疑问是：已知结果集1的输出为 125 条， 而整个查询SQL1的结果却有 126 条，也就是说，left join 后比结果集1要多一条数据，而开发人员觉得使用 left join 后整个查询结果集应该与左边的结果集保持一致。平常对于 left/right join 关注得比较少，为了解释这个问题，先在测试环境下模拟一遍，看看是否真是如此？

测试环境准备

数据库版本

1
2
3
4
5

skytf=> select version();  
 version  
-----------------------------------------------------------------------------------------------------------  
PostgreSQL 9.0.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit  
(1 row)

创建两张表并插入少量数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

skytf=> create table test_a (id serial,name varchar(32));NOTICE: CREATE TABLE will create implicit sequence "test_a_id_seq" for serial column "test_a.id"  
CREATE TABLE

skytf=> insert into test_a (name) values ('a'),('b'),('c'),('d'),('e'),('f'),('g');  
INSERT 0 7

skytf=> select * From test_a;  
id | name  
----+------  
 1 | a  
 2 | b  
 3 | c  
 4 | d  
 5 | e  
 6 | f  
 7 | g  
(7 rows)

skytf=> create table test_b (id serial,name varchar(32));  
NOTICE: CREATE TABLE will create implicit sequence "test_b_id_seq" for serial column "test_b.id"  
CREATE TABLE  
  
skytf=> insert into test_b (name) values ('a'),('b');  
INSERT 0 2

skytf=> select * from test_b;  
id | name  
----+------  
 1 | a  
 2 | b  
(2 rows)

备注：为了测试需要，创建了表 test_a 和 test_b 并插入少量数据。注意：test_a 表有 7 条数据，test_b 表有 2 条数据。

LEFT JOIN 测试

1
2
3
4
5
6
7
8
9
10
11

skytf=> select a.id,a.name,b.id,b.name from test_a a left join test_b b on a.name=b.name;  
id | name | id | name  
----+------+----+------  
 1 | a | 1 | a  
 2 | b | 2 | b  
 3 | c | |  
 4 | d | |  
 5 | e | |  
 6 | f | |  
 7 | g | |  
(7 rows)

备注：这里结果集为 7 条数据，确实与表 test_a 结果集保持一致。

给表 test_b 插入一条记录，接着测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

skytf=> insert into test_b (name) values ('b');  
INSERT 0 1

skytf=> select * from test_b;  
id | name  
----+------  
 1 | a  
 2 | b  
 3 | b  
(3 rows)

skytf=> select a.id,a.name,b.id,b.name from test_a a left join test_b b on a.name=b.name;  
id | name | id | name  
----+------+----+------  
 1 | a | 1 | a  
 2 | b | 3 | b  
 2 | b | 2 | b  
 3 | c | |  
 4 | d | |  
 5 | e | |  
 6 | f | |  
 7 | g | |  
(8 rows)

备注：给表 test_b 插入记录 “3 b” 后，再次 left join，发现结果集为 8 条，确实多了一条，果然和开发人员遇到的问题一样，多的这一条其实为 test_b 的 “name” 字段的重复记录。

开始我也觉得奇怪，接着查下文档，看看手册上的说明：

手册上的解释

Cross join
T1 CROSS JOIN T2
INNER JOIN
For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the
join condition with R1.
LEFT OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the
join condition with any row in T2, a joined row is added with null values in columns of T2.
Thus, the joined table always has at least one row for each row in T1.

备注：重点看”LEFT OUTER JOIN” 的解释：首先，会执行一个 inner join，然后，对于 T1 中不满足与 T2 关联条件的每一行会添加 null 值到 T2 的列，所以连接的表至少为 T1 中的每一行。

文档的解释已经很清楚了，如果还不是很清楚，看看下面的 INNER JOIN 输出就会更明白些。

INNER JOIN 测试

1
2
3
4
5
6
7

skytf=> select a.id,a.name,b.id,b.name from test_a a inner join test_b b on a.name=b.name;  
id | name | id | name  
----+------+----+------  
 1 | a | 1 | a  
 2 | b | 3 | b  
 2 | b | 2 | b  
(3 rows)

备注：至此问题已经很明朗了。

参考

• http://www.postgresql.org/docs/9.0/static/queries-table-expressions.html#QUERIES-FROM