An RDBMS left-connected SQL execution Plan resolution

1. The test data is as follows:

Sql> select * from T1;
A | B | C
---+----+---
1 | 10 | 1
2 | 20 | 2
3 | 30 | 3
4 | 40 | 4
5 | 50 | 5
6 | 60 | 6
(6 rows)

Sql> select * from T2;
A | B | D
---+----+---
1 | 10 | 1
2 | 20 | 2
3 | 30 | 3
(3 rows)

2. Parse the example SQL as follows:

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

3. Oracle database View execution results and execution plan:

Sql> SELECT *
from (
Select * from T1 where C >= 2
) T1 a LEFT join (
Select * from T2 where B < 30
) t2 on t1.a = t2.a
and t2.d > 1
where t1.b <
;

A b C a b D
---------- ---------- ---------- ---------- ---------- ----------
2 20 2 2 20 2
3 30 3
4 40 4

Execution Plan
----------------------------------------------------------
Plan Hash value:1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   3 |    234 | 7 (15) | 00:00:01 |
|* 1 |      HASH JOIN OUTER |    |   3 |    234 | 7 (15) | 00:00:01 |
|* 2 | TABLE ACCESS full|    T1 |   3 |    117 | 3 (0) | 00:00:01 |
|* 3 | TABLE ACCESS full|    T2 |    1 |    39 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

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

1-access ("T1". A "=" T2 "." A "(+))
2-filter ("T1". B "<50 and" C ">=2)
3-filter ("T2". 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
     926  bytes sent via sql*net to Client
     523  bytes received via sql*net from client
        2  sql*net roundtrips to/from client
       0  sorts (memory)
       0  Sorts (disk)
       3  rows Processed

4, Pgsql database view execution results and execution plan:

postgres=# SELECT *
postgres-# from (
Postgres (# Select * from T1 where C >= 2
Postgres (#) T1 LEFT join (
Postgres (# Select * from T2 where B < 30
Postgres (#) t2 on t1.a = t2.a
postgres-# and t2.d > 1
postgres-# where t1.b < 50
postgres-#;
A | B | C | A | B | D
---+----+---+---+----+---
2 | 20 | 2 | 2 | 20 | 2
3 | 30 |   3 |    | |
4 | 40 |   4 |    | |
(3 rows)

postgres=# explain analyze select *
postgres-# from (
Postgres (# Select * from T1 where C >= 2
Postgres (#) T1 LEFT join (
Postgres (# Select * from T2 where B < 30
Postgres (#) t2 on t1.a = t2.a
postgres-# and t2.d > 1
postgres-# where t1.b < 50
postgres-#;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Hash left Join (cost=37.04..85.88 rows=197 width=24) (actual time=0.020..0.027 rows=3 Loops=1)
Hash Cond: ("outer". A = "inner". a)
Seq Scan on T1 (cost=0.00..36.55 rows=197 width=12) (actual time=0.005..0.008 rows=3 Loops=1)
Filter: ((c >= 2) and (b < 50))
-Hash (cost=36.55..36.55 rows=197 width=12) (actual time=0.006..0.006 Rows=1 Loops=1)
Seq Scan on T2 (cost=0.00..36.55 rows=197 width=12) (actual time=0.002..0.003 Rows=1 Loops=1)
Filter: ((b <) and (d > 1))
Total runtime:0.052 ms
(8 rows)

5. MySQL database view execution results and execution plan:

Mysql> SELECT *
-From (
SELECT * from t1 where C >= 2
) T1 LEFT JOIN (
SELECT * FROM T2 where B < 30
) t2 on t1.a = t2.a
and t2.d > 1
Where t1.b < 50
;
+---+----+---+------+------+------+
| A | B | C | A | B | D |
+---+----+---+------+------+------+
| 2 | 20 |    2 |   2 |    20 | 2 |
| 3 | 30 | 3 | NULL | NULL | NULL |
| 4 | 40 | 4 | NULL | NULL | NULL |
+---+----+---+------+------+------+
3 rows in Set (0.05 sec)

Mysql> Explain SELECT *
-From (
SELECT * from t1 where C >= 2
) T1 LEFT JOIN (
SELECT * FROM T2 where B < 30
) t2 on t1.a = t2.a
and t2.d > 1
Where t1.b < 50
;
+----+-------------+------------+------+---------------+-------------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+------------+------+---------------+-------------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL |    NULL | 6 | Using where |
| 1 | PRIMARY | <derived3> | Ref | <auto_key0> | <auto_key0> | 8 |    t1.a | 1 | Using where |
| 3 | DERIVED | T2 | All | NULL | NULL | NULL |    NULL | 3 | Using where |
| 2 | DERIVED | T1 | All | NULL | NULL | NULL |    NULL | 6 | Using where |
+----+-------------+------------+------+---------------+-------------+---------+------+------+-------------+
4 rows in Set (0.00 sec)

6, for the above SQL execution Plan analysis:

1) Full table scan left table T1, and according to T1 table sub-query condition "C" >=2 and where filter condition "T1". B "<50 combined filtering, i.e. filter (" T1 "." B "<50 and" C ">=2), the results of the provisional memento for TMP1;
2) Full table scan right table T2, and according to T2 table sub-query condition "B" (+) <30 and on clause "T2". D "(+) >1 combined filtering, i.e. filter (" T2 "." D "(+) >1 and" B "(+) <30), the calculated result is memento to TMP2;
3) The left table T1 and the right table T2 after processing the temporary table TMP1 and TMP2 through Access ("T1". A "=" T2 "." 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.

7, some more complex SQL as follows, interested in self-study:

1) test data

CREATE TABLE TMP1 as
Select A,b,c,a as e from T1;

CREATE TABLE TMP2 as
Select A,b,d,a as E from T2;

2) Example 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
;

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
;

An RDBMS left-connected SQL execution Plan resolution