Oracle表連接

概述：SQL/Oracle使用表連接從多個(gè)表中查詢數(shù)據(jù)

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

說(shuō)明：當(dāng)被連接的多個(gè)表中存在同名字段時(shí)，必須在該字段前加上“table.”作為前綴

如果沒(méi)有限定where連接條件，否則就會(huì)出現(xiàn)笛卡爾集的不現(xiàn)實(shí)或沒(méi)有實(shí)用意義的結(jié)果

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

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

類型：Oracle8i之前的表連接(支持SQL92標(biāo)準(zhǔn))：等值連接(Equijoin)、非等值連接(Non—Equijoin)、自連接(Self join)

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

Oracle9i新引入的連接形式(支持SQL99規(guī)范)：交叉連接(Cross join)、自然連接(Natural join)、使用Using或On子句建立連接

內(nèi)連接(Inner join)、外連接(Outer join)：左外連接、右外連接、全外連接

補(bǔ)充：多表連接中可使用AND操作符增加查詢條件，使用表別名可以簡(jiǎn)化查詢，使用表名(表別名)前綴可提高查詢效率

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

樣本：hr帳戶也是Oracle自帶的樣本帳戶，里面有很多樣本表，其數(shù)據(jù)量及復(fù)雜程度都要比scott強(qiáng)一些

hr帳戶缺省是鎖定的，可以在OEM中將其解鎖并設(shè)密碼。其中的employees表是scott中的emp表的增強(qiáng)版

另外還有departments和locations表的數(shù)據(jù)看起來(lái)都比較真實(shí)。這三個(gè)表通常用于復(fù)雜的多表連接查詢

等值連接(Equijoin)

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

非等值連接(Non—Equijoin)

問(wèn)題：如何查得每個(gè)員工的工資等級(jí)

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

外連接(Outer join)

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

傳統(tǒng)的外連接分為左外連接和右外連接兩種。實(shí)際開(kāi)發(fā)中，外連接的使用頻率要比等值連接少一些

語(yǔ)法：select 字段列表 from table1,table2 where table1.column1(+)=table2.column2;

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

說(shuō)明：table1.column1(+)=table2.column2;--顯示所有符合條件的記錄，同時(shí)table2中不符合連接條件的記錄也會(huì)顯示出來(lái)

table1.column1=table2.column2(+);--顯示所有符合條件的記錄，同時(shí)table1中不符合連接條件的記錄也會(huì)顯示出來(lái)

補(bǔ)充：在SQL99規(guī)范中，內(nèi)連接(Inner Join)只返回滿足連接條件的數(shù)據(jù)，而外連接還返回不滿足連接條件的行

分類：左外聯(lián)接(Left Outer Join)：即兩個(gè)表在連接過(guò)程中除返回滿足連接條件的行以外，還返回左表中不滿足條件的行

右外聯(lián)接(Right Outer Join)：即兩個(gè)表在連接過(guò)程中除返回滿足連接條件的行以外，還返回右表中不滿足條件的行

滿外聯(lián)接(Full Outer Join)：即兩個(gè)表在連接過(guò)程中除返回滿足連接條件的行以外，還返回兩個(gè)表中不滿足條件的行

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

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

自連接(Self join)

概述：自連接誒本質(zhì)就是把一個(gè)表當(dāng)作兩個(gè)表來(lái)使用，只是定義不同的別名而已

平時(shí)很少使用自連接，但有時(shí)確實(shí)只有采用自連接的方式才能解決某些問(wèn)題

問(wèn)題：如何查得每個(gè)員工及其上司的工號(hào)和姓名

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

SQL99連接語(yǔ)法

概述：SQL1999規(guī)范中規(guī)定的連接查詢語(yǔ)法。兩個(gè)以上的表進(jìn)行連接時(shí)應(yīng)依次分別指定相臨的兩個(gè)表之間的連接條件

語(yǔ)法：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)]...;

說(shuō)明：如果感覺(jué)新語(yǔ)法比較混亂，在沒(méi)有強(qiáng)制性要求的時(shí)候，完全可以使用舊語(yǔ)法，二者不存在效率上的差異

交叉連接(Cross join)

概述：Cross join產(chǎn)生了一個(gè)笛卡爾集，其效果等同于在兩個(gè)表進(jìn)行連接時(shí)未使用WHERE子句限定連接條件

舉例：select empno, ename, dname from emp a cross join dept b;--交叉連接實(shí)際上沒(méi)有太大的意義

自然連接(Natural join)

概述：Natural join基于兩個(gè)表中的全部同名列建立連接。從兩個(gè)表中選出同名列的值均對(duì)應(yīng)相等的所有行

如果兩個(gè)表中同名列的數(shù)據(jù)類型不同，則出錯(cuò)。而且不允許在參照列上使用表名或別名作為前綴

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

Using子句

概述：如果不希望參照被連接表的所有同名列進(jìn)行等值連接，自然連接將無(wú)法滿足要求

可以在連接時(shí)使用USING子句來(lái)設(shè)置用于等值連接的列(參照列)名

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

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

On子句

概述：如果要參照非同名的列進(jìn)行等值連接，或想設(shè)置任意的連接條件，可以使用ON子句

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

子查詢(Sub Query)

概述：子查詢?cè)谥鞑樵兦皥?zhí)行一次，主查詢使用子查詢的結(jié)果。比如查詢所有比張三工資高的員工信息

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

語(yǔ)法：select 字段列表 form table where 表達(dá)式 operator(select 字段列表 from table);

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

注意：基于未知值的查詢應(yīng)考慮使用子查詢。子查詢必須包含在括號(hào)內(nèi)

建議將子查詢放在比較運(yùn)算符的右側(cè)，以增強(qiáng)可讀性。除非進(jìn)行Top—N分析，否則不要在子查詢中使用ORDER BY子句

對(duì)單行子查詢可以使用單行記錄比較運(yùn)算符。而對(duì)多行子查詢則只能使用多行記錄比較運(yùn)算符

空值：如果子查詢未返回任何行，則主查詢也不會(huì)返回任何結(jié)果

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

多值：如果子查詢返回多行結(jié)果，則為多行子查詢，此時(shí)不允許對(duì)其使用單行記錄比較運(yùn)算符

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

TopN查詢(TopN分析)

概述：即獲得按照某種規(guī)則排序之后的前n條的記錄。Oracle中通常采用子查詢的方式實(shí)現(xiàn)TOPN查詢

其實(shí)子查詢可以認(rèn)為是查到了一個(gè)臨時(shí)表，或沒(méi)有名字的臨時(shí)視圖

語(yǔ)法：select 字段列表 from (select 字段列表 from table order by 排序字段) where rownum<=n;

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

偽列rownum

概述：SELECT查詢結(jié)果中會(huì)隱含的增加一個(gè)字段rownum，即偽列。rownum用起來(lái)很靈活，但也很容易出錯(cuò)

rownum偽列并不是數(shù)據(jù)表中或者子查詢的虛擬表中真實(shí)存在的列，它只是查詢結(jié)果中的一個(gè)偽列

它標(biāo)記的是符合查詢條件的結(jié)果的編號(hào)，第一條記錄的rownum值為1，第二條記錄的rownum值為2

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

例一：select * from emp where rownum>=5;--它執(zhí)行后的結(jié)果是沒(méi)有返回值

執(zhí)行時(shí)先取出結(jié)果集中的，或者說(shuō)是數(shù)據(jù)表中的第一條記錄，并標(biāo)記第一條記錄的編號(hào)為1

判斷后得知1小于5，不符合條件。隨后便過(guò)濾掉這條記錄了，接著判斷下一條記錄是不是符合條件

于是就又取出下一條記錄，下一條記錄的rownum還是從1開(kāi)始。而rownum永遠(yuǎn)是從1開(kāi)始的，結(jié)果可想而知

接著的下一條記錄的編號(hào)還是1。即照此情形下去，記錄的編號(hào)永遠(yuǎn)不會(huì)符合大于等于5的條件

也就是說(shuō)在這條SQL語(yǔ)句的環(huán)境下，rownum永遠(yuǎn)也不會(huì)大于等于5。所以就不能指望用rownum進(jìn)行區(qū)間排序

也就是說(shuō)rownum>=5 and rownum <=10是永遠(yuǎn)也不會(huì)成立的。這就是所謂的TopN分析

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

該句執(zhí)行后并不會(huì)得到預(yù)期的結(jié)果。雖然也會(huì)得到5條記錄，但并不是工資降序排列后的前5個(gè)值

它返回的是emp表中的前5行記錄，只不過(guò)顯示的時(shí)候是按照工資進(jìn)行降序排列之后的效果

執(zhí)行時(shí)會(huì)先對(duì)where條件進(jìn)行過(guò)濾，過(guò)濾后得到了原表中的前5條記錄。然后再對(duì)表的前5條記錄排序并輸出

很顯然這并不是我們想要得到的。我們希望的是先排序，排好了順序之后再獲取前面的5行信息

但若寫成select * from emp order by sal desc where rownum<=5;的話，是不符合select語(yǔ)法的，會(huì)出錯(cuò)

所以只能通過(guò)子查詢的方式在一條語(yǔ)句中結(jié)合rownum偽列來(lái)實(shí)現(xiàn)TopN查詢

分頁(yè)：在JavaWeb編程中，經(jīng)常會(huì)遇到分頁(yè)顯示的問(wèn)題。有時(shí)需要在某一頁(yè)顯示一個(gè)區(qū)間的記錄

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

這時(shí)可以讓子查詢中的偽列變成一個(gè)真實(shí)存在的列，或者說(shuō)讓它變成能夠進(jìn)行比較運(yùn)算的真實(shí)的列

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

這里如果將a.*寫成*的話，就會(huì)出現(xiàn)缺失表達(dá)式的錯(cuò)誤。而子查詢不是真實(shí)的表，所以只能靠它的別名

它的運(yùn)行結(jié)果是顯示原emp中的所有記錄，而且還多出了一列ROWNUM的記錄，列值是從1到14的連續(xù)數(shù)字

此時(shí)的rownum還是虛的，仍然不能執(zhí)行where rownum>=5 and rownum<=10的區(qū)間排序

因?yàn)閞ownum實(shí)際上是等于本次查詢14行記錄中的每一行記錄的偽列號(hào)

從第一行開(kāi)始永遠(yuǎn)等于1，如果不符合大于等于5的條件的話，第一行記錄就會(huì)被過(guò)濾掉了

而下一行記錄的偽列號(hào)還是從1開(kāi)始的，便又會(huì)出現(xiàn)“例一”中的結(jié)果，所以此時(shí)仍不能進(jìn)行區(qū)間排序

述二：這時(shí)可以給rownum起一個(gè)別名，如myno。然后再把剛才的整條語(yǔ)句作為一個(gè)子查詢

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

整個(gè)括號(hào)括起來(lái)的又充當(dāng)了一個(gè)子查詢。這個(gè)子查詢會(huì)得到n+1條記錄，其中第一條記錄是myno字段

這時(shí)的myno就是一個(gè)實(shí)際存在的結(jié)果了。如果把子查詢當(dāng)作一個(gè)真實(shí)的表，這個(gè)表中應(yīng)該有n+1個(gè)字段

都是真實(shí)存在的字段，一個(gè)字段叫myno，其它的是原來(lái)emp中的所有字段

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

這時(shí)再執(zhí)行整條語(yǔ)句，得到的就是預(yù)期中的結(jié)果，即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查詢實(shí)現(xiàn)分頁(yè)顯示效果的SQL語(yǔ)句，也可以把當(dāng)前的語(yǔ)法格式當(dāng)作一個(gè)模板來(lái)記住

子查詢可以當(dāng)作是一個(gè)表，假想這個(gè)表是物理存在的，里面有n+1個(gè)真實(shí)存在的字段，其中一個(gè)字段叫myno

如果這時(shí)把條件改成where rownum>=5 and rownum<=10，那么執(zhí)行結(jié)果是：未選定行或沒(méi)有選定任何內(nèi)容

因?yàn)檫@個(gè)rownum指的根本不是子查詢中的返回的結(jié)果rownum，而是這一次主查詢中又得到的一個(gè)偽列

更多信息請(qǐng)查看IT技術(shù)專欄