提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  Java

当前栏目

Inner Join与Left Join

2023-02-18 16:43:15 时间

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 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 行记录)

这种情况下，从结果集中删除其他行。

相关文章