Oracle資料庫入門之多表串連與子查詢

　　Oracle表串連

　　概述：SQL/Oracle使用表串連從多個表中查詢資料

　　格式：select 欄位列表 from table1,table2 where table1.column1=table2.column2;

　　說明：當被串連的多個表中存在同名欄位時，必須在該欄位前加上“table.”作為首碼

　　如果沒有限定where串連條件，否則就會出現笛卡爾集的不現實或沒有實用意義的結果

　　舉例：select empno, ename, sal, emp.deptno, dname, loc from emp, dept;--這時得到的就是笛卡爾集。此時得到70條記錄

　　select empno, ename, sal, emp.deptno, dname, loc from emp, dept where emp.deptno=dept.deptno;--得到14條記錄

　　類型：Oracle8i之前的表串連(支援SQL92標準)：等值串連(Equijoin)、非等值串連(Non—Equijoin)、自串連(Self join)

　　外串連(Outer join)：左外串連、右外串連

　　Oracle9i新引入的串連形式(支援SQL99規範)：交叉串連(Cross join)、自然串連(Natural join)、使用Using或On子句建立串連

　　內串連(Inner join)、外串連(Outer join)：左外串連、右外串連、全外串連

　　補充：多表串連中可使用AND操作符增加查詢條件，使用表別名可以簡化查詢，使用表名(表別名)首碼可提高查詢效率

　　而串連n個表，則至少需要n-1個串連條件。如select a.ename, a.deptno, b.dname from emp a, dept b where a.deptno=b.deptno;

　　樣本：hr帳戶也是Oracle內建的樣本帳戶，裡面有很多樣本表，其資料量及複雜程度都要比scott強一些

　　hr帳戶預設是鎖定的，可以在OEM中將其解鎖並設密碼。其中的employees表是scott中的emp表的增強版

　　另外還有departments和locations表的資料看起來都比較真實。這三個表通常用於複雜的多表串連查詢

　　等值串連(Equijoin)

　　舉例：select empno, ename, emp.deptno, dname from emp, dept where emp.deptno=dept.deptno;

　　非等值串連(Non—Equijoin)

　　問題：如何查得每個員工的工資等級

　　舉例：select empno, ename, sal, grade 工資等級 from emp,salgrade where sal between losal and hisal;

　　外串連(Outer join)

　　概述：外串連運算子為(+)。使用外串連可以看到參與串連的某一方不滿足串連條件的記錄

　　傳統的外串連分為左外串連和右外串連兩種。實際開發中，外串連的使用頻率要比等值串連少一些

　　文法：select 欄位列表 from table1,table2 where table1.column1(+)=table2.column2;

　　select 欄位列表 from table1,table2 where table1.column1=table2.column2(+);

　　說明：table1.column1(+)=table2.column2;--顯示所有合格記錄，同時table2中不符合串連條件的記錄也會顯示出來

　　table1.column1=table2.column2(+);--顯示所有合格記錄，同時table1中不符合串連條件的記錄也會顯示出來

　　補充：在SQL99規範中，內串連(Inner Join)只返回滿足串連條件的資料，而外串連還返回不滿足串連條件的行

　　分類：左外聯結(Left Outer Join)：即兩個表在串連過程中除返回滿足串連條件的行以外，還返回左表中不滿足條件的行

　　右外聯結(Right Outer Join)：即兩個表在串連過程中除返回滿足串連條件的行以外，還返回右表中不滿足條件的行

　　滿外聯結(Full Outer Join)：即兩個表在串連過程中除返回滿足串連條件的行以外，還返回兩個表中不滿足條件的行

　　舉例：select employee_id, last_name, salary, department_id, department_name

　　form employees left join departments using(department_id);--返回107行

　　自串連(Self join)

　　概述：自串連誒本質就是把一個表當作兩個表來使用，只是定義不同的別名而已

　　平時很少使用自串連，但有時確實只有採用自串連的方式才能解決某些問題

　　問題：如何查得每個員工及其上司的工號和姓名

　　舉例：select a.empno, a.ename, a.mgr, b.ename from emp a, emp b where a.mgr=b.empno;

　　SQL99串連文法

　　概述：SQL1999規範中規定的串連查詢文法。兩個以上的表進行串連時應依次分別指定相臨的兩個表之間的串連條件

　　文法：select 欄位列表

　　from table1

　　[cross join table2]|

　　[natural join table2]|

　　[join table2 using(欄位名)]|

　　[join table2 on(table1.column_name=table2.column_name)]|

　　[(left|right|full outer)join table2 on(table1.column_name=table2.column_name)];

　　[cross join table3]|

　　[natural join table3]|

　　[join table3 using(欄位名)]|

　　[join table3 on(table2.column_name=table3.column_name)]|

　　[(left|right|full outer)join table3 on(table2.column_name=table3.column_name)]...;

　　說明：如果感覺新文法比較混亂，在沒有強制性要求的時候，完全可以使用舊文法，二者不存在效率上的差異

　　交叉串連(Cross join)

　　概述：Cross join產生了一個笛卡爾集，其效果等同於在兩個表進行串連時未使用WHERE子句限定串連條件

　　舉例：select empno, ename, dname from emp a cross join dept b;--交叉串連實際上沒有太大的意義

　　自然串連(Natural join)

　　概述：Natural join基於兩個表中的全部同名列建立串連。從兩個表中選出同名列的值均對應相等的所有行

　　如果兩個表中同名列的資料類型不同，則出錯。而且不允許在參照列上使用表名或別名作為首碼

　　舉例：select demno, ename, sal, deptno, dname from emp natural jon dept;

　　Using子句

　　概述：如果不希望參照被串連表的所有同名列進行等值串連，自然串連將無法滿足要求

　　可以在串連時使用USING子句來設定用於等值串連的列(參照列)名

　　同樣不允許在參照列上使用表名或別名作為首碼

　　舉例：select empno, ename, sal, deptno, dname form emp join dept using(deptno);

　　On子句

　　概述：如果要參照非同名的列進行等值串連，或想設定任意的串連條件，可以使用ON子句

　　舉例：select empno, ename, sal, emp.deptno, dname from emp join dept on(emp.deptno=dept.deptno);

　　子查詢(Sub Query)

　　概述：子查詢在主查詢前執行一次，主查詢使用子查詢的結果。比如查詢所有比張三工資高的員工資訊

　　子查詢分為單行子查詢(返回一行結果)和多行子查詢(返回多行結果)兩大類

　　文法：select 欄位列表 form table where 運算式 operator(select 欄位列表 from table);

　　比如select * from emp where sal>(select sal from emp where empno=7654);

　　注意：基於未知值的查詢應考慮使用子查詢。子查詢必須包含在括弧內

　　建議將子查詢放在比較子的右側，以增強可讀性。除非進行Top—N分析，否則不要在子查詢中使用ORDER BY子句

　　對單行子查詢可以使用單行記錄比較子。而對多行子查詢則只能使用多行記錄比較子

　　空值：如果子查詢未返回任何行，則主查詢也不會返回任何結果

　　比如select * from where sal>(select sal from emp where empno=8888);--不會返回任何結果

　　多值：如果子查詢返回多行結果，則為多行子查詢，此時不允許對其使用單行記錄比較子

　　比如select * from emp where sal>(select avg(sal) from group by deptno);--非法

　　TopN查詢(TopN分析)

　　概述：即獲得按照某種規則排序之後的前n條的記錄。Oracle中通常採用子查詢的方式實現TOPN查詢

　　其實子查詢可以認為是查到了一個暫存資料表，或沒有名字的臨時視圖

　　文法：select 欄位列表 from (select 欄位列表 from table order by 排序欄位) where rownum<=n;

　　舉例：select * from (select * from emp order by sal desc) where rownum <=5;

　　偽列rownum

　　概述：SELECT查詢結果中會隱含的增加一個欄位rownum，即偽列。rownum用起來很靈活，但也很容易出錯

　　rownum偽列並不是資料表中或者子查詢的虛擬表中真實存在的列，它只是查詢結果中的一個偽列

　　它標記的是符合查詢條件的結果的編號，第一條記錄的rownum值為1，第二條記錄的rownum值為2

　　可以理解為，符合查詢條件的第一行記錄編號為1，符合查詢條件的第二行記錄編號為2

　　例一：select * from emp where rownum>=5;--它執行後的結果是沒有傳回值

　　執行時先取出結果集中的，或者說是資料表中的第一條記錄，並標記第一條記錄的編號為1

　　判斷後得知1小於5，不符合條件。隨後便過濾掉這條記錄了，接著判斷下一條記錄是不是符合條件

　　於是就又取出下一條記錄，下一條記錄的rownum還是從1開始。而rownum永遠是從1開始的，結果可想而知

　　接著的下一條記錄的編號還是1。即照此情形下去，記錄的編號永遠不會符合大於等於5的條件

　　也就是說在這條SQL語句的環境下，rownum永遠也不會大於等於5。所以就不能指望用rownum進行區間排序

　　也就是說rownum>=5 and rownum <=10是永遠也不會成立的。這就是所謂的TopN分析

　　例二：select * from emp where rownum<=5 order by sal desc;

　　該句執行後並不會得到預期的結果。雖然也會得到5條記錄，但並不是工資降序排列後的前5個值

　　它返回的是emp表中的前5行記錄，只不過顯示的時候是按照工資進行降序排列之後的效果

　　執行時會先對where條件進行過濾，過濾後得到了原表中的前5條記錄。然後再對錶的前5條記錄排序並輸出

　　很顯然這並不是我們想要得到的。我們希望的是先排序，排好了順序之後再擷取前面的5行資訊

　　但若寫成select * from emp order by sal desc where rownum<=5;的話，是不符合select文法的，會出錯

　　所以只能通過子查詢的方式在一條語句中結合rownum偽列來實現TopN查詢

　　分頁：在JavaWeb編程中，經常會遇到分頁顯示的問題。有時需要在某一頁顯示一個區間的記錄

　　比如顯示第21條到第30條記錄。在這種情況下，單純的TopN查詢顯然不能滿足要求

　　這時可以讓子查詢中的偽列變成一個真實存在的列，或者說讓它變成能夠進行比較運算的真實的列

　　述一：select rownum, a.* from (select * from emp order by sal desc) a;

　　這裡如果將a.*寫成*的話，就會出現缺失運算式的錯誤。而子查詢不是真實的表，所以只能靠它的別名

　　它的運行結果是顯示原emp中的所有記錄，而且還多出了一列ROWNUM的記錄，列值是從1到14的連續數字

　　此時的rownum還是虛的，仍然不能執行where rownum>=5 and rownum<=10的區間排序

　　因為rownum實際上是等於本次查詢14行記錄中的每一行記錄的偽列號

　　從第一行開始永遠等於1，如果不符合大於等於5的條件的話，第一行記錄就會被過濾掉了

　　而下一行記錄的偽列號還是從1開始的，便又會出現“例一”中的結果，所以此時仍不能進行區間排序

　　述二：這時可以給rownum起一個別名，如myno。然後再把剛才的整條語句作為一個子查詢

　　即select * from (select rownum myno, a.* from (select * from emp order by sal desc) a);

　　整個括弧括起來的又充當了一個子查詢。這個子查詢會得到n+1條記錄，其中第一條記錄是myno欄位

　　這時的myno就是一個實際存在的結果了。如果把子查詢當作一個真實的表，這個表中應該有n+1個欄位

　　都是真實存在的欄位，一個欄位叫myno，其它的是原來emp中的所有欄位

　　然後在語句中將查詢條件where myno>=5 and myno<=10寫在該語句的後面。整句就如“模板”中句子

　　這時再執行整條語句，得到的就是預期中的結果，即emp中工資排名在第5到第10名之間的員工資訊

　　模板：select * from (select rownum myno, a.* from (select * from emp order by sal desc) a) where myno>=5 and myno<=10;

　　這就是在Oracle中利用TopN查詢實現分頁顯示效果的SQL語句，也可以把當前的文法格式當作一個模板來記住

　　子查詢可以當作是一個表，假想這個表是物理存在的，裡面有n+1個真實存在的欄位，其中一個欄位叫myno

　　如果這時把條件改成where rownum>=5 and rownum<=10，那麼執行結果是：未選定行或沒有選定任何內容

　　因為這個rownum指的根本不是子查詢中的返回的結果rownum，而是這一次主查詢中又得到的一個偽列