PostgreSQL表连接详解（inner join、outer join、子查询）_postgresql inner join

1、子查询

在介绍pg中表连接之前，我们先来看一下子查询。在某些方面，子查询和表连接类似，都是多个表协作一起进行使用。

需要注意的是，pg中的子查询使用有2点限制：
1、select子查询只能返回一列：

bill@bill=>select (select * from (values (1,2),(2,3)) as t(c1,c2)) , relname, relkind from pg_class;
ERROR:  subquery must return only one column
LINE 1: select (select * from (values (1,2),(2,3)) as t(c1,c2)) , re...
1
2
3

2、select子查询只能返回一条记录：

bill@bill=>select (select * from (values (1),(2)) as t(c1)) , relname, relkind from pg_class;
ERROR:  more than one row returned by a subquery used as an expression
1
2

当然，子查询的使用也是十分灵活。
我们可以在select子句中使用子查询：

bill@bill=>select (select * from (values (1),(2)) as t(c1) limit 1) , relname, relkind from pg_class;
 c1 |                    relname                    | relkind
----+-----------------------------------------------+---------
  1 | loadavg                                       | f
  1 | meminfo                                       | f
  1 | v_freeze                                      | v
  1 | pglog                                         | f
  1 | pg_type                                       | r
  1 | t1                                            | r
  1 | pg_toast_16476                                | t
1
2
3
4
5
6
7
8
9
10

也可以在select源中使用子查询：

bill@bill=>select t.relname from (select * from pg_class limit 1) t , pg_class where t.relname=pg_class.relname;
 relname
---------
 loadavg
(1 row)
1
2
3
4
5

除此之外，我们还可以用在with, update from语句中。

2、PostgreSQL表连接

表连接通常使用在将多个表之间的公共列进行组合。这其实是一种逻辑概念，而像我们常说的NEST LOOP JOIN、 HASH JOIN等是表连接的物理实现方式（PostgreSQL表连接 nestloop/hash/merge join详解）。

接下来我们来看看pg中不同表连接的使用及差异吧。
首先构造两张简单的表：

bill@bill=>select * from tbl_1;
 id | info
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

bill@bill=>select * from tbl_2;
 id | info
----+------
  1 | aaa
  2 | bbb
  4 | ddd
(3 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.1、inner join

INNER JOIN 表示返回两个表或记录集连接字段的匹配记录。

bill@bill=>select a.*,b.* from
tbl_1 a inner join tbl_2 b
on a.id = b.id;
 id | info | id | info
----+------+----+------
  1 | a    |  1 | aaa
  2 | b    |  2 | bbb
(2 rows)
1
2
3
4
5
6
7
8

2.2、full outer join

full outer join是外连接的一种，它表示包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。不符合条件的，以空值代替。

bill@bill=>SELECT
bill-#     a.*,
bill-#     b.*
bill-# FROM
bill-#     tbl_1 a
bill-# FULL OUTER JOIN tbl_2 b ON a.id = b.id;
 id | info | id | info
----+------+----+------
  1 | a    |  1 | aaa
  2 | b    |  2 | bbb
  3 | c    |    |
    |      |  4 | ddd
(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

2.3、left join

左外连接又叫左连接 ：意思是包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐。换句话说就是，列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。

bill@bill=>SELECT
bill-#     a.*,
bill-#     b.*
bill-# FROM
bill-#     tbl_1 a
bill-# LEFT JOIN tbl_2 b ON a.id = b.id;
 id | info | id | info
----+------+----+------
  1 | a    |  1 | aaa
  2 | b    |  2 | bbb
  3 | c    |    |
(3 rows)
1
2
3
4
5
6
7
8
9
10
11
12

2.4、right join

右外连接又叫右连接： 意思是包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐，换句话说，与左连接一样，列出右边表全部的，及左边表符合条件的，不符合条件的用空值替代。

bill@bill=>SELECT
bill-#     a.*,
bill-#     b.*
bill-# FROM
bill-#     tbl_1 a
bill-# RIGHT JOIN tbl_2 b ON a.id = b.id;
 id | info | id | info
----+------+----+------
  1 | a    |  1 | aaa
  2 | b    |  2 | bbb
    |      |  4 | ddd
(3 rows)
1
2
3
4
5
6
7
8
9
10
11
12

需要注意的是：无论是左连接还是右连接都是外连接的一种，因此对于例如t1 left join t2，无论是否满足连接条件，t1都会返回全部记录。

而我们可能会见到这样两种写法：
–scan filter

select t1.*,t2.* from t1 left join t2 on (t1.x=t2.x) where t1.x=x; 
1

–join filter

select t1.*,t2.* from t1 left join t2 on (t1.x=t2.x and t1.x=x);
1

乍一看好像两者一样，其实不然。scan filter是对join之后的结果集进行筛选，而join filter中t1的约束条件无效（因为都会返回全部的t1记录）。

例如：
–scan filter

bill@bill=>select * from tbl_1 left join tbl_2 on(tbl_1.id=tbl_2.id) where tbl_1.id =2;
 id | info | id | info
----+------+----+------
  2 | b    |  2 | bbb
(1 row)
1
2
3
4
5

–join filter

bill@bill=>select * from tbl_1 left join tbl_2 on(tbl_1.id=tbl_2.id and tbl_1.id=2);
 id | info | id | info
----+------+----+------
  1 | a    |    |
  2 | b    |  2 | bbb
  3 | c    |    |
(3 rows)
1
2
3
4
5
6
7

2.5、cross join

CROSS JOIN就是笛卡尔乘积连接，不需要任何关联条件，实现M*N的结果集。这种连接方式在实际中也很少使用。

bill@bill=>select * from tbl_1 cross join tbl_2;
 id | info | id | info
----+------+----+------
  1 | a    |  1 | aaa
  1 | a    |  2 | bbb
  1 | a    |  4 | ddd
  2 | b    |  1 | aaa
  2 | b    |  2 | bbb
  2 | b    |  4 | ddd
  3 | c    |  1 | aaa
  3 | c    |  2 | bbb
  3 | c    |  4 | ddd
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

2.6、NATURAL JOIN

NATURAL JOIN自然连接，在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。有点类似于inner join。

语法如下：
SELECT *
FROM Table1
NATURAL [INNER, LEFT, RIGHT] JOIN Table2;

bill@bill=>select * from tbl_1 natural left join tbl_2;
 id | info
----+------
  1 | a
  2 | b
  3 | c
(3 rows)
1
2
3
4
5
6
7

2.7、self join

自联接是将表联接到自身的查询。自联接对于比较同一表中一行列中的值很有用。不过一般情况下，自连接的效率比多表之间的普通连接效率要低。因此用于自连接的列推荐使用在该列上创建索引的方式来代替。

3、如何强制表连接顺序

在Oracle中我们通过通过hint来改变那些不合理的表连接方式，那么在pg中该如何强制表连接方式呢？

我们可以通过设置参数join_collapse_limit来指定表的连接顺序。

该参数默认和from_collapse_limit相同， 这样适合大多数使用。

其含义是：如果得出的列表中不超过这么多项，那么规划器将把显式JOIN（除了FULL JOIN）结构重写到 FROM项列表中。较小的值可减少规划时间，但是可能会生成差些的查询计划。

我们可以把它设置为 1 来避免任何显式JOIN的重排序。因此查询中指定的显式连接顺序就是关系被连接的实际顺序。

需要注意的是，必须得写成显示关联才能强制JOIN顺序。

例如：
下面这种写法没有办法强制join顺序。

bill@bill=>set join_collapse_limit=1;
SET

select t1.info, t5.info from tbl_join_1 t1,  
tbl_join_2 t2,  
tbl_join_3 t3,  
tbl_join_4 t4,  
tbl_join_5 t5,  
tbl_join_6 t6,  
tbl_join_7 t7,  
tbl_join_8 t8,  
tbl_join_9 t9  
where   
t1.id=t2.id and  
t2.id=t3.id and  
t3.id=t4.id and  
t4.id=t5.id and  
t5.id=t6.id and  
t6.id=t7.id and  
t7.id=t8.id and  
t8.id=t9.id and  
t9.id=10000;  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

需要写成这样：

select t1.info, t5.info from   
tbl_join_1 t1 join tbl_join_2 t2 on (t1.id=t2.id)  
join tbl_join_3 t3 on (t2.id=t3.id)  
join tbl_join_4 t4 on (t3.id=t4.id)  
join tbl_join_5 t5 on (t4.id=t5.id)  
join tbl_join_6 t6 on (t5.id=t6.id)  
join tbl_join_7 t7 on (t6.id=t7.id)  
join tbl_join_8 t8 on (t7.id=t8.id)  
join tbl_join_9 t9 on (t8.id=t9.id)  
where t9.id=10000;  
1
2
3
4
5
6
7
8
9
10

在某些情况下我们可以选择暂时把这个变量设置为 1，然后显式地指定想要的连接顺序。一般不建议这么去做。