Oracle in與exists語句

標籤：大於   tab   release 2   varchar2   order   情況   ott   子查詢   span   

官方文檔：
Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration

EXISTS Condition

An EXISTS condition tests for existence of rows in a subquery.

EXISTS條件測試子查詢中是否存在行。

exists （sql 返回結果集為真）
not exists (sql 不返回結果集為真）

實驗：

create table scott.a (id number(10),name varchar(32));
insert into scott.a
select 1,'A1' from dual
union all
select 2,'A2' from dual
union all
select 3,'A3' from dual;
commit;

create table scott.b(id number(10),aid number(10),name varchar2(32));

insert into scott.b
    select 1,1,'B1' from dual
    union all
    select 2,2,'B2' from dual
    union all
    select 3,2,'B3' from dual;
    commit;

[email protected]>select * from scott.a;

        ID NAME
---------- --------------------------------
         1 A1
         2 A2
         3 A3

[email protected]>select * from scott.b;

        ID        AID NAME
---------- ---------- --------------------------------
         1          1 B1
         2          2 B2
         3          2 B3

表A與表B是1:B關係，怎麼看呢？

只需要對兩表關聯列進行匯總統計就能知道兩表是什麼關係：把關聯列進行匯總再 order by 一下

[email protected]>select id,count(*) from scott.a group by id order by count(*) desc;

        ID   COUNT(*)
---------- ----------
         1          1
         3          1
         2          1

[email protected]>select aid,count(*) from scott.b group by aid order by count(*) desc;

       AID   COUNT(*)
---------- ----------
         2          2
         1          1

B表的count(*)的值大於1，屬於N：1關係，看count(*)的值，都是1  就是1:1關係，count(*)有大於1的就是1：N關係

emp:dept N:1 返回N的關係，不會返回1的關係

查看等價改寫：

[email protected]>select id,name from scott.a where id in (select aid from scott.b);

        ID NAME
---------- --------------------------------
         1 A1
         2 A2

關聯列是a.id = b.aid

以上查詢使用了in語句,in()只執行一次,它查出B表中的所有id欄位並緩衝起來.之後,檢查A表的id是否與B表中的id相等,如果相等則將A表的記錄加入結果集中,直到遍曆完A表的所有記錄.

可以看出,當B表資料較大時不適合使用in(),因為它會B表資料全部遍曆一次.

等價改寫：

select id,name from scott.a exists (select 1 from scott.b where a.id=b.aid);

以上查詢使用了exists語句,exists()會執行A.length次,它並不緩衝exists()結果集,因為exists()結果集的內容並不重要,重要的是結果集中是否有記錄,如果有則返回true,沒有則返回false.

結論:exists()適合B表比A表資料大的情況

當A表資料與B表資料一樣大時,in與exists效率差不多,可任選一個使用.

Oracle in與exists語句