背景
有人在Dave的群里問了個問題，大概是這樣的：
有一段sql，如何理解其中的rownum<=2
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum <= 2;
因為“rownum<=2”這個條件，并不是在where后，而是作為left join的條件，那么a.id肯定是輸出了全部值，但b.id會是什么情況？
實際查詢結果是，兩列全部值都輸出了，沒有空值，仿佛“rownum <= 2”這個條件并不存在。
分析
先從執行計劃上入手
create table test_tb(id number);
insert into test_tb
select rownum from dual connect by rownum <= 10;
commit;
EXPLAIN PLAN FOR
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum <= 2;
select * from table(dbms_xplan.display);
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3673628547
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 4 (0)| 00
| 1 | COUNT | | | | |
|* 2 | HASH JOIN OUTER | | 10 | 260 | 4 (0)| 00
| 3 | TABLE ACCESS FULL | TEST_TB | 10 | 130 | 2 (0)| 00
| 4 | VIEW | VW_DCL_2E38C6CE | 2 | 26 | 2 (0)| 00
| 5 | TABLE ACCESS FULL| TEST_TB | 10 | 130 | 2 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="ITEM_1"(+))
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic statistics used: dynamic sampling (level=2)
21 rows selected
產生了一個2行的view，但解釋不了為什么右表的數據全部查出來了
然后我做了個嘗試，把條件改成rownum<=0 ，結果竟然和rownum<=2一樣。
改成rownum<0,結果也沒變；只有當條件為rownum=0時，右側數據沒了.
問題來了：
我們知道，rownum永遠都是大于等于1的，rownum<0和rownum=0 都明顯是false，理論上應該等價，但此處卻出現了差異！
原有認知被打破了!
再嘗試rownum=1
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum=1;
兩列數據都出來了；如果改成rownum=2，那么左邊數據是全的，右邊沒有數據。
所以，總結現象:
| 條件 | 左側是否有數據 | 右側是否有數據 |
|---|---|---|
| rownum<1 | 有 | 有 |
| rownum<0 | 有 | 有 |
| rownum>0 | 有 | 有 |
| rownum=1 | 有 | 有 |
| rownum=0 | 有 | 無 |
| rownum=2 | 有 | 無 |
這個規律是，首先rownum大于等于一個“大于1的值”，肯定沒數據，可以排除；
然后rownum等于0，肯定也沒數據，也可以排除；
剩下的就是分兩類
- 等于1和大于0是一類，是可以有數據的；
- 小于1和小于0是一類，不應該有數據，但實際上查出了數據；
矛盾點就在于最后一點。
對于等于1和大于0，可以猜測右側數據存在一個隱藏字段，每一行的值都為1。
于是查看一下 “rownum=0”時的執行計劃
SQL> explain plan for select a.id, b.id
2 from test_tb a
3 left join test_tb b
4 on a.id = b.id
5 and rownum=0;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 404971544
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 2 (0)| 0
| 1 | COUNT | | | | |
|* 2 | HASH JOIN OUTER | | 10 | 160 | 2 (0)| 0
| 3 | TABLE ACCESS FULL | TEST_TB | 10 | 30 | 2 (0)| 0
| 4 | VIEW | VW_DCL_2E38C6CE | 1 | 13 | 5 (100)| 0
|* 5 | FILTER | | | | |
| 6 | TABLE ACCESS FULL| TEST_TB | 10 | 30 | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="ITEM_1"(+))
5 - filter(0=1)
19 rows selected
和rownum不是等于0的時候，多了一個 “filter(0=1)”
也就是說，“rownum=0” 被oracle 自動優化成了 “1=0” ,這是作為一個過濾條件，并且此處rownum始終是等于1的，印證了我之前的猜想。
為了排除版本特性的問題，我測試了11g/12c/19c/21c，表現均一致。
嘗試等價改寫
先不考慮小于0和小于1的情況，那么sql可以這么改
--改寫前
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum <0;
--改寫后
select a.id, b.id
from test_tb a
left join
(select rn,b.* from (select rownum rn from dual),test_tb b ) b
on a.id = b.id
and rn <0;
但是這個肯定不滿足小于0 和小于1的情況，于是再改一下
select a.id, b.id
from test_tb a
left join (select nvl(rn, -1E125) rn, b.*
from test_tb b
left join (select rownum rn from dual where rownum < 0)
on 1 = 1) b
on a.id = b.id
and rn < 0;
同時改“rownum < 0”、“rn < 0” 兩處條件即可一一對應幾乎所有場景，除了 “rownum=-1E125”的場景。當然再加個判斷就可以完全對應了，不過意義不大。其實重點在于，處理這個邏輯時，期待引入一個無法用number類型表示的“最小的負數”。
以下附完整測試驗證代碼，標準組和對照組的輸出完全一致
--測試數據準備
create table test_tb(id number);
insert into test_tb
select rownum from dual connect by rownum <= 10;
commit;
-- 小于0 標準組
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum <0;
-- 小于0 對照組
select a.id, b.id
from test_tb a
left join (select nvl(rn, -1E125) rn, b.*
from test_tb b
left join (select rownum rn from dual where rownum < 0)
on 1 = 1) b
on a.id = b.id
and rn < 0;
-- 等于0 標準組
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum =0;
-- 等于0 對照組
select a.id, b.id
from test_tb a
left join (select nvl(rn, -1E125) rn, b.*
from test_tb b
left join (select rownum rn from dual where rownum = 0)
on 1 = 1) b
on a.id = b.id
and rn = 0;
-- 等于1 標準組
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum <0;
-- 等于1 對照組
select a.id, b.id
from test_tb a
left join (select nvl(rn, -1E125) rn, b.*
from test_tb b
left join (select rownum rn from dual where rownum =1)
on 1 = 1) b
on a.id = b.id
and rn =1;
-- 小于1 標準組
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum <1;
-- 小于1 對照組
select a.id, b.id
from test_tb a
left join (select nvl(rn, -1E125) rn, b.*
from test_tb b
left join (select rownum rn from dual where rownum < 1)
on 1 = 1) b
on a.id = b.id
and rn < 1;
-- 大于1 標準組
select a.id, b.id
from test_tb a
left join test_tb b
on a.id = b.id
and rownum >1;
-- 大于1 對照組
select a.id, b.id
from test_tb a
left join (select nvl(rn, -1E125) rn, b.*
from test_tb b
left join (select rownum rn from dual where rownum > 1)
on 1 = 1) b
on a.id = b.id
and rn > 1;
后記
rownum，在不同數據庫上實現的情況不一樣，或者說，就連join語法的執行邏輯也可能存在差異，因為我測了下其他幾個支持rownum的數據庫：
- 在openGauss 3.0.0中， rownum<=2,輸出的結果是左邊全部數據,右邊數據2行;
- 在達夢8中，輸出結果和openGauss 3.0.0一致；
- 在EDB14.4中，輸出結果也和openGauss 3.0.0一致
- 在kinbase8中，rownum不能用于join…
也就是說，在大部分數據庫中，都把rownum的限制條件簡單認為是對右表限制條件,當然這更容易理解。
但是查詢結果和oracle不一致，我無法判斷這是ORACLE的特性還是BUG，歡迎大家來討論或拍磚。
- 本文作者： DarkAthena
- 本文鏈接： https://www.darkathena.top/archives/left-join-rownum-equ0-less0-diff
- 版權聲明： 本博客所有文章除特別聲明外，均采用CC BY-NC-SA 3.0 許可協議。轉載請注明出處！
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
