Inner Join与Left Join
内连接、外连接、半连接区别是什么？
先创建一个示例表及数据：
代码语言：javascript复制postgres=# create table t10(id1 int,id2 int);
CREATE TABLE
postgres=# create table t11(id1 int,id2 int);
CREATE TABLEpostgres=# select * from t11;
id1 | id2
----- -----
2 | 4
5 | 6
3 | 5
(3 行记录)
postgres=# select * from t10;
id1 | id2
----- -----
1 | 2
2 | 3
3 | 4
(3 行记录)示例很简单，下面做下演示。
隐式连接与显式连接
先看隐式连接：
代码语言：javascript复制postgres=# select * from t10,t11 where t10.id1=t11.id1;
id1 | id2 | id1 | id2
----- ----- ----- -----
2 | 3 | 2 | 4
3 | 4 | 3 | 5
(2 行记录)这种情况下，连接实际发生在WHERE子句中。在FROM子句中，只需要列出JOIN的表。另一种显式连接：
代码语言：javascript复制postgres=# select * from t10 join t11 on t10.id1=t11.id1;
id1 | id2 | id1 | id2
----- ----- ----- -----
2 | 3 | 2 | 4
3 | 4 | 3 | 5
(2 行记录)两者的语法其实是一致的，看下他们的执行计划就可以知道，他们的执行计划完全一样：
代码语言：javascript复制postgres=# explain select * from t10,t11 where t10.id1=t11.id1;
QUERY PLAN
-------------------------------------------------------------------
Merge Join (cost=317.01..711.38 rows=25538 width=16)
Merge Cond: (t10.id1 = t11.id1)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: t10.id1
-> Seq Scan on t10 (cost=0.00..32.60 rows=2260 width=8)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: t11.id1
-> Seq Scan on t11 (cost=0.00..32.60 rows=2260 width=8)
(8 行记录)
postgres=# explain select * from t10 join t11 on t10.id1=t11.id1;
QUERY PLAN
-------------------------------------------------------------------
Merge Join (cost=317.01..711.38 rows=25538 width=16)
Merge Cond: (t10.id1 = t11.id1)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: t10.id1
-> Seq Scan on t10 (cost=0.00..32.60 rows=2260 width=8)
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Sort Key: t11.id1
-> Seq Scan on t11 (cost=0.00..32.60 rows=2260 width=8)
(8 行记录)对于MOST查询来说，优化器基本上以相同的方式处理这两个查询。如果您碰巧运行一个典型的应用程序，则没有区别。但是有一些细微的差异需要提一下。我们看个变量：
代码语言：javascript复制postgres=# show join_collapse_limit;
join_collapse_limit
---------------------
8
(1 行记录)这个参数的意义：每当生成的 items 列表不超过本参数值时，planner 就会将 explicit join 结构（FULL JOINs 除外）重写为 FROM items 列表。较小的值会减少计划时间，但可能会产生较差的查询计划。默认情况下，此参数值设置为与 from_collapse_limit 参数值相同，这适用于大多数用途。将其设置为 1 可防止 explicit join 的任何重新排序。因此，查询中指定的显式连接顺序将是连接关系的实际顺序。因为查询规划器并不总是选择最佳的连接顺序，所以高级用户可以选择将此参数值临时设置为 1，然后显式指定所需的连接顺序。设置本参数值为 geqo_threshold 参数值或者更大可能会触发 GEQO Planner 的使用，进而导致非优化的执行计划。
大多数情况下，显式和隐式JOIN是风格问题，而不是性能问题。但，在真正复杂的查询时确实存在差异。
INNER JOIN与OUTER JOIN
内连接在JOIN表中查找公共元组，外连接始终获取一侧的所有数据并在另一侧找相应的匹配项：
代码语言：javascript复制postgres=# select * from t10 left join t11 on t10.id1=t11.id1;
id1 | id2 | id1 | id2
----- ----- ----- -----
1 | 2 | |
2 | 3 | 2 | 4
3 | 4 | 3 | 5
(3 行记录)这里是t10作为left表，并在右侧找匹配的行。与left join相反的是右连接：
代码语言：javascript复制postgres=# select * from t10 right join t11 on t10.id1=t11.id1;
id1 | id2 | id1 | id2
----- ----- ----- -----
2 | 3 | 2 | 4
3 | 4 | 3 | 5
| | 5 | 6
(3 行记录)全连接
还有一种连接--全连接。他的作用是从双方获取所有数据并找匹配项。没有匹配的在对应一侧填充NULL：
代码语言：javascript复制postgres=# select * from t10 full join t11 on t10.id1=t11.id1;
id1 | id2 | id1 | id2
----- ----- ----- -----
1 | 2 | |
2 | 3 | 2 | 4
3 | 4 | 3 | 5
| | 5 | 6
(4 行记录)虽然编码相对容易，但是由于对外连接的作用存在误解，人们常会犯错误。
外连接常见错误
比如在连接条件中加了个AND子句：
代码语言：javascript复制postgres=# select * from t10 left join t11 on t10.id1=t11.id1 and t11.id1=2;
id1 | id2 | id1 | id2
----- ----- ----- -----
1 | 2 | |
2 | 3 | 2 | 4
3 | 4 | |
(3 行记录)大多数人实际期望结果集比以前小，但事实并非如此。ON条件仅改变了匹配的行，一个LEFT JOIN仍会在左侧产生所有行。附件条件只是将一些条目更高为NULL，他会不减少数据量。
代码语言：javascript复制postgres=# select * from t10 left join t11 on t10.id1=t11.id1 ;
id1 | id2 | id1 | id2
----- ----- ----- -----
1 | 2 | |
2 | 3 | 2 | 4
3 | 4 | 3 | 5
(3 行记录)涉及聚集时，很容易隐藏潜在的语义问题：
代码语言：javascript复制postgres=# select count(*),count(t11.id1) from t10 left join t11 on t10.id1=t11.id1 and t11.id1=2;
count | count
------- -------
3 | 1
(1 行记录)IN:Semi Join
什么是半连接？思考下IN语句：
代码语言：javascript复制postgres=# select * from t10 where id1 in(select id1 from t11);
id1 | id2
----- -----
2 | 3
3 | 4
(2 行记录)IN语句是一个隐式的DISTINCT过滤器，用于删除重复的条目。因此连接与半连接的区别在于处理重复项的方式。
执行计划清楚的显示这些重复项已被删除。这种情况下，使用HashAggregate完成。（许多情况下，还会在GROUP BY语句中看到）：
代码语言：javascript复制postgres=# explain select * from t10 where id1 in(select id1 from t11);
QUERY PLAN
-------------------------------------------------------------------------
Hash Join (cost=42.75..93.85 rows=1130 width=8)
Hash Cond: (t10.id1 = t11.id1)
-> Seq Scan on t10 (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=40.25..40.25 rows=200 width=4)
-> HashAggregate (cost=38.25..40.25 rows=200 width=4)
Group Key: t11.id1
-> Seq Scan on t11 (cost=0.00..32.60 rows=2260 width=4)
(7 行记录)IN语句的反义词是NOT IN：
代码语言：javascript复制postgres=# select * from t10 where id1 not in(select id1 from t11);
id1 | id2
----- -----
1 | 2
(1 行记录)这种情况下，从结果集中删除其他行。
