oracle中 exists 與 in效率及其用法

oracle中 exists 與 in效率及其用法

用not exists 代替not in

select * from tsp_product p where not exists(select '' from tsp_orderitem i where p.id=i.product_id)
select * from tsp_product p where id not in(select product_id from tsp_orderitem i where p.id=i.product_id)
用exists 代替in
select * from tsp_product p where p.id  in(select product_id from tsp_orderitem )
select * from tsp_product p where  exists(select 'x' from tsp_orderitem i where p.id =i.product_id )

下面來分析為什麼用用not exists 代替not in

有兩個簡單例子，以說明 “exists”和“in”的效率問題

1) select * from t1 where exists(select 1 from t2 where t1.a=t2.a) ;

    t1資料量小而t2資料量非常大時，t1<<t2 時，1) 的查詢效率高。

2) select * from t1 where t1.a in (select t2.a from t2) ;

     t1資料量非常大而t2資料量小時，t1>>t2 時，2) 的查詢效率高。

 

union
把兩張表的資料合起來，如有重複行，只取一行
union all
把兩張表的資料合起來，不過濾重複行
minus
返回在第一個查詢結果中與第二個查詢結果不相同的那部分行記錄。　

oracle有這樣的一些約定
1.select語句必須返回相同的列數，如果列數不同的話，可以選擇串代替列。
2.select語句中相應的列必須有相同的資料類型，長度可以不同

rollup

統計：select sum(s.totalamount),to_char(createdate,'yyyy-mm') from tsp_orders s group by rollup(to_char(createdate,'yyyy-mm')

connect by
select level,a. * from tsp_area a start with parent_id is null connect by prior id = parent_id

說明：建立類似樹報表。
 prior 強制報表的順序變為從根到葉（如果prior是父輩）或從葉到根（如果prior是後代）
 雖然where子句可以人樹排除上體，但無法排除他們的子孫子(或者祖先,如果prior在等號的右邊)

詳細看看它他的區別

exists 用法：

請注意 1）句中的有顏色字型的部分 ，理解其含義；

其中 “select 1 from t2 where t1.a=t2.a” 相當於一個關聯表查詢，相當於

“select 1 from t1,t2     where t1.a=t2.a”

但是，如果你噹噹執行 1） 句括弧裡的語句，是會報語法錯誤的，這也是使用exists需要注意的地方。

“exists（xxx）”就表示括弧裡的語句能不能查出記錄，它要查的記錄是否存在。

因此“select 1”這裡的 “1”其實是無關緊要的，換成“*”也沒問題，它只在乎括弧裡的資料能不能尋找出來，是否存在這樣的記錄，如果存在，這 1） 句的where 條件成立。

in 的用法：

繼續引用上面的例子

“2) select * from t1 where t1.a in (select t2.a from t2) ”

這裡的“in”後面括弧裡的語句搜尋出來的欄位的內容一定要相對應，一般來說，t1和t2這兩個表的a欄位表達的意義應該是一樣的，否則這樣查沒什麼意義。

打個比方：t1，t2表都有一個欄位，表示工單號，但是t1表示工單號的欄位名叫“ticketid”，t2則為“id”，但是其表達的意義是一樣的，而且資料格式也是一樣的。這時，用 2）的寫法就可以這樣：

“select * from t1 where t1.ticketid in (select t2.id from t2) ”

select name from employee where name not in (select name from student);

select name from employee where not exists (select name from student);