PostgreSQL的查询语句的连接方式与查询计划比较--简单语句

本文链接：https://blog.youkuaiyun.com/fly2nn/article/details/61924321

本文详细介绍了SQL中的不同连接类型，包括内连接、交叉连接、左连接、右连接和全外连接，并通过具体示例展示了每种连接在处理NULL值时的行为差异。此外，还对比了分析前后的查询计划，解释了不同连接类型下的执行策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--Step1：准备数据，创建表，插入数据，注意数据的设计，每个表是3种情况，目的是看字段为NULL时，各种连接的处理方式

test=# create table A (c1 int, c2 int);
CREATE TABLE
test=# create table B (c1 int, c2 int);
CREATE TABLE
test=#
test=# insert into A values(11,21);
INSERT 0 1
test=# insert into A values(NULL,22);
INSERT 0 1
test=# insert into A values(13,NULL);
INSERT 0 1
test=#
test=# insert into B values(11,21);
INSERT 0 1
test=# insert into B values(12,22);
INSERT 0 1
test=# insert into B values(NULL,23);
INSERT 0 1

--step2：各种查询，注意对比A、B表中不同数据在不同的连接方式下，其结果的不同之处
test=# select * from A join B on A.c1=B.c1;
c1 | c2 | c1 | c2
----+----+----+----
11 | 21 | 11 | 21
(1 row)

test=# select * from A cross join B ;
c1 | c2 | c1 | c2
----+----+----+----
11 | 21 | 11 | 21
11 | 21 | 12 | 22
11 | 21 |    | 23
    | 22 | 11 | 21
    | 22 | 12 | 22
    | 22 |    | 23
13 |    | 11 | 21
13 |    | 12 | 22
13 |    |    | 23
(9 rows)

test=# select * from A left join B on A.c1=B.c1;
c1 | c2 | c1 | c2
----+----+----+----
11 | 21 | 11 | 21
13 |    |    |
    | 22 |    |
(3 rows)

--说明：左连接，左表中为NULL得以保留

test=# select * from A right join B on A.c1=B.c1;
c1 | c2 | c1 | c2
----+----+----+----
11 | 21 | 11 | 21
    |    | 12 | 22
    |    |    | 23
(3 rows)
说明：右连接，右表中为NULL得以保留

test=# select * from A full join B on A.c1=B.c1;
c1 | c2 | c1 | c2
----+----+----+----
11 | 21 | 11 | 21
    |    | 12 | 22
    |    |    | 23
13 |    |    |
    | 22 |    |
(5 rows)
--说明：全外连接，左、右表中为NULL得以保留

--step3：比较查询计划的不同（粉色字体，用以方便与step5得出的查询计划比较不同之处）
test=# explain select * from A join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
Merge Join (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (a.c1 = b.c1)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         -> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

test=# explain select * from A cross join B ;
                           QUERY PLAN
-----------------------------------------------------------------
Nested Loop (cost=0.00..57313.15 rows=4579600 width=16)
   -> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
   -> Materialize (cost=0.00..42.10 rows=2140 width=8)
         -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(4 rows)

test=# explain select * from A left join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
Merge Left Join (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (a.c1 = b.c1)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         -> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

test=# explain select * from A right join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
Merge Left Join (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (b.c1 = a.c1)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         -> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

test=# explain select * from A full join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
Merge Full Join (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (a.c1 = b.c1)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         -> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
   -> Sort (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

--step4:对表做分析，目的是获得表属性的实际的数据（如表的真实行数），便于查询优化器获得真实数据，更好进行代价计算
test=# analyze;
ANALYZE

step5：查看analyze之后的查询计划，比较step3得到的查询计划，比较不同点（红色标识不同点，没有全部标出，可自行对照查看不同）
test=# explain select * from A join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
Hash Join (cost=1.07..2.12 rows=1 width=16)
   Hash Cond: (a.c1 = b.c1)
   -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8)
   -> Hash (cost=1.03..1.03 rows=3 width=8)
         -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8)
(5 rows)

test=# explain select * from A cross join B ;
                         QUERY PLAN
-------------------------------------------------------------
Nested Loop (cost=0.00..2.18 rows=9 width=16)
   -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8)
   -> Materialize (cost=0.00..1.04 rows=3 width=8)
         -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8)
(4 rows)

test=# explain select * from A left join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
Hash Left Join (cost=1.07..2.12 rows=3 width=16)
   Hash Cond: (a.c1 = b.c1)
   -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8)
   -> Hash (cost=1.03..1.03 rows=3 width=8)
         -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8)
(5 rows)

test=# explain select * from A right join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
Hash Left Join (cost=1.07..2.12 rows=3 width=16)
   Hash Cond: (b.c1 = a.c1)
   -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8)
   -> Hash (cost=1.03..1.03 rows=3 width=8)
         -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8)
(5 rows)

test=# explain select * from A full join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
Merge Full Join (cost=2.11..2.15 rows=3 width=16)
   Merge Cond: (a.c1 = b.c1)
   -> Sort (cost=1.05..1.06 rows=3 width=8)
         Sort Key: a.c1
         -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8)
   -> Sort (cost=1.05..1.06 rows=3 width=8)
         Sort Key: b.c1
         -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8)
(8 rows)

--step6：比较“explain analyze”和“explain”（step3）得出的查询计划的不同
test=# explain analyze select * from A join B on A.c1=B.c1;
                                              QUERY PLAN

--------------------------------------------------------------------------------
-----------------------
Hash Join (cost=1.07..2.12 rows=1 width=16) (actual time=0.094..0.109 rows=1 l
oops=1)
   Hash Cond: (a.c1 = b.c1)
   -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8) (actual time=0.012..0.018
rows=3 loops=1)
   -> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.029..0.029 rows=2 l
oops=1)
        Buckets: 1024 Batches: 1 Memory Usage: 1kB
         -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8) (actual time=0.005.
.0.013 rows=3 loops=1)
Total runtime: 0.220 ms
(7 rows)

--step7：比较join和普通where条件的不同
test=# select * from A, B where A.c1=B.c1;--与“select * from A join B on A.c1=B.c1;”的结果，没有什么不同
c1 | c2 | c1 | c2
----+----+----+----
11 | 21 | 11 | 21
(1 row)

test=# explain select * from A, B where A.c1=B.c1;--查询计划，也没有什么不同
                       QUERY PLAN
-------------------------------------------------------------
Hash Join (cost=1.07..2.12 rows=1 width=16)
   Hash Cond: (a.c1 = b.c1)
   -> Seq Scan on a (cost=0.00..1.03 rows=3 width=8)
   -> Hash (cost=1.03..1.03 rows=3 width=8)
         -> Seq Scan on b (cost=0.00..1.03 rows=3 width=8)
(5 rows)