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