Inner Join与Left Join
内连接、外连接、半连接区别是什么？
先创建一个示例表及数据：
postgres=# create table t10(id1 int,id2 int); CREATE TABLE postgres=# create table t11(id1 int,id2 int); CREATE TABLE
postgres=# 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 行记录)
示例很简单，下面做下演示。
隐式连接与显式连接
先看隐式连接：
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的表。另一种显式连接：
postgres=# select * from t10 join t11 on t10.id1=t11.id1; id1 | id2 | id1 | id2 -----+-----+-----+----- 2 | 3 | 2 | 4 3 | 4 | 3 | 5 (2 行记录)
两者的语法其实是一致的，看下他们的执行计划就可以知道，他们的执行计划完全一样：
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查询来说，优化器基本上以相同的方式处理这两个查询。如果您碰巧运行一个典型的应用程序，则没有区别。但是有一些细微的差异需要提一下。我们看个变量：
postgres=# show j 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表中查找公共元组，外连接始终获取一侧的所有数据并在另一侧找相应的匹配项：
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相反的是右连接：
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：
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子句：
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，他会不减少数据量。
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 行记录)
涉及聚集时，很容易隐藏潜在的语义问题：
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语句：
postgres=# select * from t10 where id1 in(select id1 from t11); id1 | id2 -----+----- 2 | 3 3 | 4 (2 行记录)
IN语句是一个隐式的DISTINCT过滤器，用于删除重复的条目。因此连接与半连接的区别在于处理重复项的方式。
执行计划清楚的显示这些重复项已被删除。这种情况下，使用HashAggregate完成。（许多情况下，还会在GROUP BY语句中看到）：
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：
postgres=# select * from t10 where id1 not in(select id1 from t11); id1 | id2 -----+----- 1 | 2 (1 行记录)
这种情况下，从结果集中删除其他行。
