Two left-connected SQL Execution Plan resolution (Oracle and Pgsql contrast):

The previous parse link is as follows:
Https://www.cnblogs.com/wcwen1990/p/9325968.html

1. SQL Example 1:

Sql> SELECT *
From (
SELECT * from TMP1 where C >= 1
) T1 LEFT JOIN (
SELECT * from TMP2 where B < 30
) t2 on t1.a = t2.a
and t2.d > 1 and t1.e >= 2
Where t1.b < 50
;

A b C e A b D E
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2 20 2 2 2 20 2 2
4 40 4 4
3 30 3 3
1 10 1 1

Execution Plan
----------------------------------------------------------
Plan Hash value:2592321047

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   4 |    416 | 7 (15) | 00:00:01 |
|* 1 |      HASH JOIN OUTER |    |   4 |    416 | 7 (15) | 00:00:01 |
|* 2 | TABLE ACCESS full|    TMP1 |   4 |    208 | 3 (0) | 00:00:01 |
|* 3 | TABLE ACCESS full|    TMP2 |    1 |    52 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("TMP1". A "=" TMP2 "." A "(+))
Filter ("TMP1". " E ">=case When (" TMP2 ".") A "(+) is not NULL) and then 2
ELSE 2 END)
2-filter ("TMP1". B "<50 and" C ">=1)
3-filter ("TMP2". D "(+) >1 and" B "(+) <30)

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
7 Consistent gets
0 physical Reads
0 Redo Size
1082 Bytes sent via sql*net to client
524 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

postgres=# explain analyze select *
postgres-# from (
Postgres (# Select * from TMP1 where C >= 1
Postgres (#) T1 LEFT join (
Postgres (# Select * from TMP2 where B < 30
Postgres (#) t2 on t1.a = t2.a
postgres-# and t2.d > 1 and t1.e >= 2
postgres-# where t1.b < 50
postgres-#;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Hash left Join (cost=34.90..80.00 rows=181 width=32) (actual time=0.021..0.035 rows=4 Loops=1)
Hash Cond: ("outer". A = "inner". a)
Join Filter: ("outer". E >= 2)
Seq Scan on TMP1 (cost=0.00..34.45 rows=181 width=16) (actual time=0.006..0.011 rows=4 Loops=1)
Filter: ((c >= 1) and (b < 50))
-Hash (cost=34.45..34.45 rows=181 width=16) (actual time=0.007..0.007 Rows=1 Loops=1)
Seq Scan on TMP2 (cost=0.00..34.45 rows=181 width=16) (actual time=0.002..0.003 Rows=1 Loops=1)
Filter: ((b <) and (d > 1))
Total runtime:0.063 ms
(9 rows)

Analysis of the SQL execution plan:

1) Full table scan left table TMP1, and according to TMP1 table sub-query condition "C" >=1 and where filter condition "T1". B "<50 combined filtering, i.e. filter (" TMP1 "." B "<50 and" C ">=1), the results of the provisional memento for TMP1;
2) Full table scan right table TMP2, and according to TMP2 table sub-query condition "B" (+) <30 and on clause "T2". D "(+) >1 combined filtering, i.e. filter (" TMP2 "." D "(+) >1 and" B "(+) <30), the calculated result is memento to TMP2;
3) The left table TMP1 and the right table TMP2 after processing the temporary table TMP1 and TMP2 through Access ("TMP1". A "=" TMP2 "." A "(+)") connection conditions for the hash left JOIN operation, the full return of a temporary table result set, the right table mismatch row is null, return the result of temporary memento to Tmp3;
4) Returns the result set.

2. SQL Example 2:

Sql> SELECT *
From (
SELECT * from TMP1 where C >= 1
) T1 LEFT JOIN (
SELECT * from TMP2 where B < 30
) t2 on t1.a = t2.a
and t2.d > 1 and t1.e >= 2
where t1.b < t2.e <= 3
;

A b C e A b D E
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2 20 2 2 2 20 2 2

Execution Plan
----------------------------------------------------------
Plan Hash value:1630095649

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   1 |    104 | 7 (15) | 00:00:01 |
|* 1 |      HASH JOIN |    |   1 |    104 | 7 (15) | 00:00:01 |
|* 2 | TABLE ACCESS full|    TMP2 |    1 |    52 | 3 (0) | 00:00:01 |
|* 3 | TABLE ACCESS full|    TMP1 |   3 |    156 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("TMP1". A "=" TMP2 "." A ")
2-filter ("TMP2". E "<=3 and" TMP2 "." D ">1 and" B "<30)
3-filter ("TMP1". B "<50 and" TMP1 "." E ">=2 and" C ">=1)

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
9 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
981 Bytes sent via sql*net to client
524 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>

postgres=# SELECT *
postgres-# from (
Postgres (# Select * from TMP1 where C >= 1
Postgres (#) T1 LEFT join (
Postgres (# Select * from TMP2 where B < 30
Postgres (#) t2 on t1.a = t2.a
postgres-# and t2.d > 1 and t1.e >= 2
postgres-# where t1.b < t2.e <= 3
postgres-#;
A | B | C | e | A | B | D | E
---+----+---+---+---+----+---+---
2 | 20 | 2 | 2 | 2 | 20 | 2 | 2
(1 row)

postgres=# explain analyze select *
postgres-# from (
Postgres (# Select * from TMP1 where C >= 1
Postgres (#) T1 LEFT join (
Postgres (# Select * from TMP2 where B < 30
Postgres (#) t2 on t1.a = t2.a
postgres-# and t2.d > 1 and t1.e >= 2
postgres-# where t1.b < t2.e <= 3
postgres-#;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Hash Join (cost=38.68..78.43 rows=18 width=32) (actual time=0.033..0.041 Rows=1 Loops=1)
Hash Cond: ("outer". A = "inner". a)
Seq Scan on TMP1 (cost=0.00..38.53 rows=60 width=16) (actual time=0.007..0.011 rows=3 Loops=1)
Filter: ((c >= 1) and (e >= 2) and (b < 50))
-Hash (cost=38.53..38.53 rows=60 width=16) (actual time=0.008..0.008 Rows=1 Loops=1)
Seq Scan on TMP2 (cost=0.00..38.53 rows=60 width=16) (actual time=0.003..0.005 Rows=1 Loops=1)
Filter: ((b <) and (d > 1) and (e <= 3))
Total runtime:0.070 ms
(8 rows)

postgres=#

Analysis of the SQL execution plan:

1) Full table scan left table TMP2, and according to TMP2 table sub-query condition "B" <30 and where filter condition "TMP2". E "<=3 and ON clause filter condition" TMP2 "." D ">1 combined filtering, i.e. filter (" TMP2 "." E "<=3 and" TMP2 "." D ">1 and" B "<30), the results of the provisional memento for TMP1;
2) Full table scan right table TMP1, and according to TMP1 table sub-query condition "C" >=1 and WHERE clause filter condition "TMP1". B "<50 and ON clause" TMP1 "." E ">=2 combined filtering, i.e. filter (" TMP1 "." B "<50 and" TMP1 "." E ">=2 and" C ">=1), the results of the provisional memento for TMP2;
3) temporary tables TMP1 and TMP2 through Access ("TMP1". " A "=" TMP2 "." A ") connection conditions for the hash join operation (where the left join notation has been converted to the inner link), return the match result temporary memento to Tmp3;
4) Returns the result set.

Two left-connected SQL Execution Plan resolution (Oracle and Pgsql contrast):