> 表連線方式方法介紹(Nested Loop/Hash Join/Merge Join/Join) 資料庫版本(oracle11.2.0.4 and PostgreSQL 13.1)  -- Firsouler 2021/03/04

- [環境構造](#環境構造)
- [Nested Loop](#nested-loop)
  - [Oracle 執行舉例](#oracle-執行舉例)
  - [Postgresql 執行舉例](#postgresql-執行舉例)
- [Hash Join](#hash-join)
  - [Oracle舉例](#oracle舉例)
  - [Postgresql舉例](#postgresql舉例)
- [Merge Join](#merge-join)
  - [Oracle舉例](#oracle舉例-1)
  - [Postgresql舉例](#postgresql舉例-1)
- [三種連線方法的優劣](#三種連線方法的優劣)
- [Join連線型別](#join連線型別)
  - [inner Join](#inner-join)
  - [Full outer Join](#full-outer-join)
  - [left/right join](#leftright-join)
- [union 介紹](#union-介紹)
- [自連線](#自連線)
  - [分頁查詢](#分頁查詢)
  - [刪除重複行](#刪除重複行)
  - [關聯子查詢比較行與行 eg](#關聯子查詢比較行與行-eg)
- [參考文件](#參考文件)

#### 環境構造
``` sql
-- oracle11.2.0.4
--城市、國家
drop table country purge;
CREATE TABLE country (
country_id int primary key,
country_name VARCHAR(50) NOT NULL
);

drop table city purge;
CREATE TABLE city (
city_id int primary key,
city_name VARCHAR(50) NOT NULL,
country_id int NOT NULL
);

begin
for i in 1 .. 10 loop
insert into country values(i,'country'||i);
end loop;
commit;
end;

begin
for i in 1 .. 10000 loop
insert into city values(i,'city'||i,ceil(i/1000));
end loop;
commit;
end;

execute dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname => 'CITY' ,estimate_percent=> 100 ,cascade => true);
execute dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname => 'COUNTRY' ,estimate_percent=> 100 ,cascade => true);

-- PostgreSQL 13.1
drop table country;
CREATE TABLE country (
country_id integer primary key,
country_name text NOT NULL
);

drop table city purge;
CREATE TABLE city (
city_id integer primary key,
city_name text NOT NULL,
country_id integer NOT NULL
);

insert into country values (generate_series(1,10),'country'||generate_series(1,10));

insert into city values(generate_series(1,10000),'city'||generate_series(1,10000),ceil(random()*(10-1)+1));

analyze city;
analyze country;
```
#### Nested Loop

![Nested Loop](https://gitee.com/firsouler/my_study_notes/raw/master/images/sql_table_join/nested_loop.png)

如上圖所示，為Nested Loop方式介紹。 其演算法：**驅動表返回一行資料，透過連線列傳值給被驅動表，驅動表返回多少行，被驅動表就要被掃描多少次。** 被驅動表索引方式索引唯一掃描或者範圍掃描。在被驅動表資料子集較少的情況下，巢狀迴圈是比較好的選擇，也就是適合結果集比較小的查詢，通常超過10000行被認為大行，會變成低效。 舉例如下：

##### Oracle 執行舉例
``` sql 
SQL> explain plan for select city_name,country_name from city,country where city.country_id=country.country_id and city.city_id=99;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2738185913

---------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		    |	  1 |	 93 |	  2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |		    |	  1 |	 93 |	  2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CITY	    |	  1 |	 53 |	  1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN	     | SYS_C0018242 |	  1 |	    |	  1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| COUNTRY	    |	  1 |	 40 |	  1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN	     | SYS_C0018239 |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CITY"."CITY_ID"=99)
   5 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

18 rows selected.
```

語句對比

``` sql
--下面方式 oracle會選擇 hash  全表掃描
explain plan for select city_name,country_name from city,country where city.country_id=country.country_id and city.city_id>=10;

--強制使用nested loop 方式
explain plan for select /*+ leading(city) use_nl(country) */ city_name,country_name from city,country
 where city.country_id=country.country_id and city.city_id>=10;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 103883790

---------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		    |  9991 |	907K| 10005   (1)| 00:02:01 |
|   1 |  NESTED LOOPS		     |		    |  9991 |	907K| 10005   (1)| 00:02:01 |
|   2 |   NESTED LOOPS		     |		    |  9991 |	907K| 10005   (1)| 00:02:01 |
|*  3 |    TABLE ACCESS FULL	     | CITY	    |  9991 |	517K|	 11   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN	     | SYS_C0018239 |	  1 |	    |	  0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| COUNTRY	    |	  1 |	 40 |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("CITY"."CITY_ID">=10)
   4 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

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

22 rows selected.
--由上所示，被驅動別執行了 9991次，也就是（10000-9），資源消耗明顯上升
```
##### Postgresql 執行舉例
``` sql
mydb=# explain analyze select city_name,country_name from city,country where city.country_id=country.country_id and city.city_id=99;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.44..16.49 rows=1 width=40) (actual time=0.031..0.036 rows=1 loops=1)
   ->  Index Scan using city_pkey on city  (cost=0.29..8.30 rows=1 width=12) (actual time=0.015..0.017 rows=1 loops=1)
         Index Cond: (city_id = 99)
   ->  Index Scan using country_pkey on country  (cost=0.15..8.17 rows=1 width=36) (actual time=0.006..0.006 rows=1 loops=1)
         Index Cond: (country_id = city.country_id)
 Planning Time: 0.621 ms
 Execution Time: 0.098 ms
(7 rows)

--返回多行，強制使用nested loop,比預設選擇hash join 多30ms
mydb=# set enable_hashjoin=off;
SET
mydb=# set enable_mergejoin=off;
SET
mydb=# explain analyze select city_name,country_name from city,country where city.country_id=country.country_id and city.city_id>=10;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.15..1934.29 rows=9991 width=40) (actual time=0.050..43.354 rows=9991 loops=1)
   ->  Seq Scan on city  (cost=0.00..188.00 rows=9991 width=12) (actual time=0.025..4.870 rows=9991 loops=1)
         Filter: (city_id >= 10)
         Rows Removed by Filter: 9
   ->  Index Scan using country_pkey on country  (cost=0.15..0.17 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=9991)
         Index Cond: (country_id = city.country_id)
 Planning Time: 0.393 ms
 Execution Time: 45.366 ms
(8 rows)
```
**PG Nested loop說明**

Postgresql使用巢狀迴圈執行查詢，那麼它可以透過迭代表1中的所有條目、迭代表2中的所有條目，然後在表1和表2中的行對滿足篩選條件時發出一行。巢狀迴圈是Postgresql唯一可以用來處理任何連線的連線演算法。例如下面語句，Oracle會選擇Merge Join,Postgresql則選擇 Nested Loop,詳細可參考 Merge Join部分。

```  sql
--即使資料量較大情況，以下語句pg還會選擇Nested Loop
explain  select a.city_name,b.city_id from city a,city2 b where a.country_id<b.country_id hash="" join="" area="" table="" segment="" loop="" oracle="" sql="" country=""> explain plan for select city_name,country_name from city,country where city.country_id=country.country_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 114462077

------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     | 10000 |	 781K|	  14   (0)| 00:00:01 |
|*  1 |  HASH JOIN	   |	     | 10000 |	 781K|	  14   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| COUNTRY |	  10 |	 400 |	   3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| CITY    | 10000 |	 390K|	  11   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

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

19 rows selected.

```
##### Postgresql舉例
``` sql
mydb=# explain analyze select city_name,country_name from city,country where city.country_id=country.country_id;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=38.58..227.91 rows=10000 width=40) (actual time=0.185..12.086 rows=10000 loops=1)
   Hash Cond: (city.country_id = country.country_id)
   ->  Seq Scan on city  (cost=0.00..163.00 rows=10000 width=12) (actual time=0.026..3.735 rows=10000 loops=1)
   ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.064..0.067 rows=10 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 17kB
         ->  Seq Scan on country  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.025..0.031 rows=10 loops=1)
 Planning Time: 1.953 ms
 Execution Time: 13.983 ms
(8 rows)
```
**為了可以使用小表建立hash table，最佳化器會轉換,如 right join 被改為Left Join**

``` sql
mydb=# explain analyze select * from country right join city on city.country_id=country.country_id;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=38.58..227.91 rows=10000 width=52) (actual time=0.087..11.883 rows=10000 loops=1)
   Hash Cond: (city.country_id = country.country_id)
   ->  Seq Scan on city  (cost=0.00..163.00 rows=10000 width=16) (actual time=0.020..3.035 rows=10000 loops=1)
   ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.044..0.047 rows=10 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 17kB
         ->  Seq Scan on country  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.024..0.028 rows=10 loops=1)
 Planning Time: 0.413 ms
 Execution Time: 13.588 ms
(8 rows)
```
#### Merge Join
兩張表在做連線時用排序操作和合並操作來得到結果集的連線方式。排序主要處理非等值管理。演算法：先對兩張表根據連線列各自進行排序，巢狀迴圈是從被驅動表的索引中匹配資料，排序合併連線是從記憶體(PGA中的work area)中匹配資料，嚴格來說沒有驅動表，可以認為較少表作為驅動表。HASH JOIN只需要把驅動表放入PGA中，但是排序連線合並需要將兩表結果集均放入PGA中

![Merge Loop](https://gitee.com/firsouler/my_study_notes/raw/master/images/sql_table_join/merge_join.jpg)

上圖為Merge大概流程，如果統計資訊等都準確，資料庫會自動選擇最好執行計劃。

##### Oracle舉例
``` sql
col PLAN_TABLE_OUTPUT for a100
set lines 200 pages 999
explain plan for select a.city_name,b.country_name from city a,country b where a.country_id<b.country_id sql=""> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1026867539

------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	     | 45000 |	1054K|	     |	  62   (2)| 00:00:01 |
|   1 |  MERGE JOIN		      | 	     | 45000 |	1054K|	     |	  62   (2)| 00:00:01 |
|   2 |   SORT JOIN		      | 	     |	  10 |	 120 |	     |	   2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| COUNTRY      |	  10 |	 120 |	     |	   2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN	      | SYS_C0018239 |	  10 |	     |	     |	   1   (0)| 00:00:01 |
|*  5 |   SORT JOIN		      | 	     | 10000 |	 117K|	 408K|	  60   (2)| 00:00:01 |
|   6 |    TABLE ACCESS FULL	      | CITY	     | 10000 |	 117K|	     |	  11   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(INTERNAL_FUNCTION("A"."COUNTRY_ID")<internal_function filter="" rows="" selected.="" postgresql="" merge="" join="" loop="" sql="" mydb="#" analyze="" city="" country="" set="" enable_nestloop="off;" explain="" select="" a.city_name="" from="" a="" b="" where="" a.country_id="" query="" plan="" nested="" width="18)" time="0.104..419.608" loops="1)" filter:="" b.country_id="" removed="" by="">  Seq Scan on city a  (cost=0.00..1636.00 rows=100000 width=13) (actual time=0.066..20.058 rows=100000 loops=1)
   ->  Materialize  (cost=0.00..1.15 rows=10 width=13) (actual time=0.000..0.001 rows=10 loops=100000)
         ->  Seq Scan on country b  (cost=0.00..1.10 rows=10 width=13) (actual time=0.014..0.019 rows=10 loops=1)
 Planning Time: 0.649 ms
 Execution Time: 457.499 ms
(8 rows)
```

> hash join 只有當在記憶體中執行是最快連線方式，當pg認為hash連線所需超出記憶體時，會選擇Merge join，Merge Join可以更有效使用磁碟。Postgresql中，Merge Join僅適用於條件為相等的連線。其工作原理，比較每個輸出的第一行（最小值），如果相等，結果中新增一行，並從input刪除已比較的兩行；如果欄位不相同，刪除id最小的，並繼續與下一個最小的對比。

Postgres可以非常有效地在磁碟上執行合併，方法是將資料的一小部分儲存在記憶體中，對該資料執行合併，將合併結果寫入磁碟，然後從磁碟將更多的輸入讀取到記憶體中。這種方法一次在磁碟上讀寫大量的資料，比Hash 隨機讀高效。

``` sql
mydb=# explain  select a.city_name,b.city_id from city a,city2 b where a.country_id=b.country_id and a.city_id<30000;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Hash Join  (cost=3274.21..3764393.24 rows=333242482 width=13)
   Hash Cond: (a.country_id = b.country_id)
   ->  Index Scan using city_pkey on city a  (cost=0.29..1052.87 rows=30033 width=13)
         Index Cond: (city_id < 30000)
   ->  Hash  (cost=1634.63..1634.63 rows=99863 width=8)
         ->  Seq Scan on city2 b  (cost=0.00..1634.63 rows=99863 width=8)
(6 rows)

mydb=# set enable_hashjoin=off;
SET
mydb=# explain  select a.city_name,b.city_id from city a,city2 b where a.country_id=b.country_id and a.city_id<30000;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Merge Join  (cost=13213.55..5012000.94 rows=333242482 width=13)
   Merge Cond: (a.country_id = b.country_id)
   ->  Sort  (cost=3286.46..3361.55 rows=30033 width=13)
         Sort Key: a.country_id
         ->  Index Scan using city_pkey on city a  (cost=0.29..1052.87 rows=30033 width=13)
               Index Cond: (city_id < 30000)
   ->  Sort  (cost=9927.09..10176.74 rows=99863 width=8)
         Sort Key: b.country_id
         ->  Seq Scan on city2 b  (cost=0.00..1634.63 rows=99863 width=8)
(9 rows)
```
**Merge Join方法主要是記憶體不夠用時，效果更好**

當資料量特別大時，會選擇Merge Join，如果沒有索引，會選擇"Materialize",將結果快取記憶體或檔案中，避免多次全表掃描。如下所示：

``` sql
mydb=# create  unlogged table tbl1(id int, info text) with (autovacuum_enabled=off);
CREATE TABLE
mydb=#  create  unlogged table tbl2(id int, info text) with (autovacuum_enabled=off);
CREATE TABLE
mydb=# insert into tbl1 select generate_series(1,10000000);  
INSERT 0 10000000
mydb=# insert into tbl2 select generate_series(1,10000000);  
INSERT 0 10000000
mydb=#  explain select * from tbl1,tbl2 where tbl1.id=tbl2.id;  
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Merge Join  (cost=2075531.84..2370522825.11 rows=157893676470 width=72)
   Merge Cond: (tbl1.id = tbl2.id)
   ->  Sort  (cost=1037765.92..1051814.66 rows=5619496 width=36)
         Sort Key: tbl1.id
         ->  Seq Scan on tbl1  (cost=0.00..100442.96 rows=5619496 width=36)
   ->  Materialize  (cost=1037765.92..1065863.40 rows=5619496 width=36)
         ->  Sort  (cost=1037765.92..1051814.66 rows=5619496 width=36)
               Sort Key: tbl2.id
               ->  Seq Scan on tbl2  (cost=0.00..100442.96 rows=5619496 width=36)
(9 rows)

mydb=#  explain select * from tbl1,tbl2 where tbl1.id<tbl2.id query="" plan="" nested="" loop="" rows="10526245098005" width="72)" join="" filter:="" tbl2.id="">  Seq Scan on tbl1  (cost=0.00..100442.96 rows=5619496 width=36)
   ->  Materialize  (cost=0.00..172443.44 rows=5619496 width=36)
         ->  Seq Scan on tbl2  (cost=0.00..100442.96 rows=5619496 width=36)
(5 rows)

mydb=# set enable_mergejoin=off;
SET
mydb=#  explain select * from tbl1,tbl2 where tbl1.id=tbl2.id;  
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Hash Join  (cost=214589.66..5526742276.56 rows=157893676470 width=72)
   Hash Cond: (tbl1.id = tbl2.id)
   ->  Seq Scan on tbl1  (cost=0.00..100442.96 rows=5619496 width=36)
   ->  Hash  (cost=100442.96..100442.96 rows=5619496 width=36)
         ->  Seq Scan on tbl2  (cost=0.00..100442.96 rows=5619496 width=36)
(5 rows)

mydb=#  explain select * from tbl1,tbl2 where tbl1.id=tbl2.id and tbl1.id<2000;  
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Merge Join  (cost=1449858.75..790941515.17 rows=52631216124 width=72)
   Merge Cond: (tbl1.id = tbl2.id)
   ->  Sort  (cost=412092.83..416775.74 rows=1873165 width=36)
         Sort Key: tbl1.id
         ->  Seq Scan on tbl1  (cost=0.00..114491.70 rows=1873165 width=36)
               Filter: (id < 2000)
   ->  Materialize  (cost=1037765.92..1065863.40 rows=5619496 width=36)
         ->  Sort  (cost=1037765.92..1051814.66 rows=5619496 width=36)
               Sort Key: tbl2.id
               ->  Seq Scan on tbl2  (cost=0.00..100442.96 rows=5619496 width=36)
(10 rows)
```

#### 三種連線方法的優劣

還有一種連線叫笛卡爾積(Cross Join/Cartesian Product),兩表關聯沒有任何條件。結果集為兩張錶行數的乘積，此種方式基本不會用到，這次不做討論。

類別 | Nested Loop  | Hash Join | Merge Join
---  | ---  |   --- |  ---
使用條件| 任何條件 | 等值連線 | 非等值，'<>'除外
使用資源| CPU， disk IO | 記憶體、臨時空間| 記憶體、臨時空間
特點 | 高選擇索引或進行限制性搜尋時效率高，能快速返回第一次搜尋結果 | 表記錄多，缺乏有效索引， 效率高| 非等值連線，效率高
缺點 | 資料量大效率低，尤其無索引或限制條件不夠時| 需要大量記憶體，可能會用磁碟| 表都需要排序，結果最後返回，PG批次依次記憶體排序

#### Join連線型別

![Merge Loop](https://gitee.com/firsouler/my_study_notes/raw/master/images/sql_table_join/sql_join.png)

##### inner Join

``` sql
--結果只包含滿足連線條件的記錄
select a.id,a.col1,b.col2 from a,b where a.id=b.id;
--or
select a.id,a.col1,b.col2 from a inner join b on a.id=b.id;
--or
select id,a.col1,b.col2 from a inner join b using(id);
```

##### Full outer Join
``` sql
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name;
	ID NAME 	  ID NAME
---------- ------ ---------- ------
	 1 aaa		   1 aaa
			       2 bbb
	 3 ccc

--如果沒有匹配記錄，null作為值，可以去掉相同結果集
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name 
where tablea.id is null or tableb.id is null;
	ID      NAME  ID       NAME
---------- ------ ------ ------
			        2     bbb
	 3       ccc
```
##### left/right join
``` sql
--如上圖所示，產生A表所有結果，B沒有的用null作為值,除相同值外，right與left正好相反
SELECT * FROM TableA left JOIN TableB ON TableA.name = TableB.name;
	ID NAME 	  ID NAME
---------- ------ ---------- ------
	 1 aaa		   1 aaa
	 3 ccc
--可以去掉相同結果集
SELECT * FROM TableA left JOIN TableB ON TableA.name = TableB.name 
where  tableb.id is null;
	ID NAME 	  ID NAME
---------- ------ ---------- ------
	 3 ccc
```

#### union 介紹
UNION 運算子用於合併兩個或多個 SELECT 語句的結果集。

請注意，UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的資料型別。同時，每條 SELECT 語句中的列的順序必須相同。UNION 只選取記錄，有去重功能，而UNION ALL會列出所有記錄。

``` sql
SELECT name FROM TableA UNION SELECT name FROM TableB;
NAME
------------------------------
aaa
bbb

SELECT name FROM TableA UNION all SELECT name FROM TableB;

NAME
------------------------------
aaa
aaa
bbb
```

**有些情況下可以使用union 代替or ，提供查詢效率**
```  sql
select * from t1 where owner='SCOTT' or object_id in (select object_id from t2);
--上述語句會使用FILTER，可以調整sql,或者t2.object_id建立索引
select * from t1 where owner='SCOTT' 
union 
select * from t1 where object_id in (select object_id from t2);

--列轉行舉例
select employee,child_1 as child from personnel
union all
select employee,child_2 as child from personnel;
```

#### 自連線

##### 分頁查詢
``` sql
--四層分頁更適合頁數比較大的分頁查詢，返回結果集小的當做驅動表，掃描被驅動表。
with tmp as
(SELECT * FROM
    ( SELECT rid, ROWNUM as RN
       FROM
           (SELECT rowid as rid
             FROM t1
             where owner='SYS'
             order by object_id desc
            ) WHERE ROWNUM <= 500
   )  WHERE RN > 490
) 
select  /*+ use_nl(a) leading(b) */ owner,object_name,object_id,rn
from t1 a,tmp b
where a.rowid=b.rid;
--三層select是常用的
select * from (select owner,object_name,object_id,rownum as rn
 from(select owner,object_name,object_id from t1 where owner='SYS' order by object_id desc) 
 where rownum<=500) 
 where rn>490;
```
##### 刪除重複行

``` sql
--極致函式
delete from products p1
where rowid < (
   select max(p2.rowid) from products p2
   where p1.name = p2.name
   and p1.price =p2.price
);
--使用非等值連線
delete from products p1
where exists (
   select * from products p2
   where p1.name = p2.name
   and p1.price = p2.price
   and p1.rowid < p2.rowid
);
```

##### 關聯子查詢比較行與行 eg
``` sql
--求與上一年營業額一樣的年份
select year,sale from sales s1
where sale = (
   select sale from sales s2 
   where s2.year = s1.year-1
) order by year;

--or
select s1.year,s1.sale from sales s1,sales s2
where s2.sale =s1.sale
and s2.year = s1.year-1
order by year;

--求同期對比情況
select s1.year,s1.sale,
case when s1.sale = s2.sale then '持平'
     when s1.sale > s2.sale then '增長'
     when s1.sale < s2.sale then '減少'
else '-' end as var
from sales s1,sales s2
where s2.year = s1.year -1
order by year;     
```

#### 參考文件
- Join introduce: https://www.cnblogs.com/xqzt/p/4469673.html
- Join introduce: https://logicalread.com/
- pg join method: http://www.jasongj.com/2015/03/07/Join1/
- pg join method: https://developer.aliyun.com/article/70992
- pg join method: https://www.interdb.jp/pg/pgsql03.html
- pg Merge Joins: https://malisper.me/postgres-merge-joins/
- 表連線方法參考 ：書籍 《SQL 最佳化核心思想》
- SQL連線示例參考：書籍 《SQL進階教程》</tbl2.id></internal_function></b.country_id></b.country_id>

【SQL】SQL表連線方法方式介紹（Oracle/Postgresql）

相關文章