一、概述

在實(shí)際應(yīng)用系統(tǒng)開(kāi)發(fā)中會(huì)設(shè)計(jì)多個(gè)數(shù)據(jù)表，每個(gè)表的信息不是獨(dú)立存在的，而是若干個(gè)表之間的信息存在一定的關(guān)系，當(dāng)用戶(hù)查詢(xún)某一個(gè)表的信息時(shí)，很可能需要查詢(xún)關(guān)聯(lián)數(shù)據(jù)表的信息，這就是多表關(guān)聯(lián)查詢(xún)。SELECT語(yǔ)句自身是支持多表關(guān)聯(lián)查詢(xún)的，多表關(guān)聯(lián)查詢(xún)要比單表查詢(xún)復(fù)雜的多。在進(jìn)行多表關(guān)聯(lián)查詢(xún)時(shí)，可能會(huì)涉及表別名、內(nèi)連接、外連接、自然連接和交叉連接等概念，下面將對(duì)這些內(nèi)容進(jìn)行講解。

二、表的別名

在多表關(guān)聯(lián)查詢(xún)時(shí)，如果多個(gè)表之間存在同名的列，則必須使用表名來(lái)限定列的引用。例如，在SCOTT模式中，DEPT表和EMP表都有DEPTNO列，當(dāng)用戶(hù)使用該列關(guān)聯(lián)查詢(xún)兩個(gè)表時(shí)，就需要通過(guò)指定表名來(lái)區(qū)分這兩個(gè)列的歸屬。但是，隨著查詢(xún)變得越來(lái)越復(fù)雜，語(yǔ)句就會(huì)因?yàn)槊看蜗薅斜仨気斎氡砻兊萌唛L(zhǎng)。對(duì)于這種情況，SQL提供了設(shè)定表別名的機(jī)制，使用簡(jiǎn)短的表別名可以代替原有較長(zhǎng)的表名稱(chēng)，這樣就大大縮減語(yǔ)句的長(zhǎng)度。

【例2.1】在SCOTT模式下，通過(guò)DEPTNO（部門(mén)號(hào)）列來(lái)關(guān)聯(lián)EMP表和DEPT表，并檢索這兩個(gè)表中相關(guān)字段的信息，代碼及運(yùn)行結(jié)果如下：

select e.empno 員工編號(hào),e.ename 員工姓名,d.dname 部門(mén) from dept d,emp e where d.deptno = e.deptno and e.job = 'MANAGER';

在上面的SELECT語(yǔ)句中，F(xiàn)ROM子句最先執(zhí)行，然后才是WHERE子句和SELECT子句，這樣在FROM子句中指定表的別名后，當(dāng)需要限定引用列時(shí)，其他所有子句都可以使用表的別名。

另外，還需要注意一點(diǎn)，一旦在FROM子句中為表指定了別名，則必須在剩余的子句中都是用表的別名，而不允許再使用原來(lái)的表名稱(chēng)，否則，將出現(xiàn)【例2.2】錯(cuò)誤提示。

【例2.2】 

select e.empno 員工編號(hào),e.ename 員工姓名,d.dname 部門(mén) from dept d,emp e where d.deptno = e.deptno and emp.job = 'MANAGER';

 總結(jié)：

        1、表的別名在FROM子句中定義，別名放在表名之后，之間用空格隔開(kāi)。

        2、別名一經(jīng)定義，在整個(gè)查詢(xún)語(yǔ)句中就只能使用表的別名而不能在使用表名。

        3、表的別名只在所定義的查詢(xún)語(yǔ)句中有效。

        4、應(yīng)該選擇有意義的別名，表的別名最長(zhǎng)為30個(gè)字符，但越短越好。

三、內(nèi)連接

內(nèi)連接是一種常用的多表關(guān)聯(lián)查詢(xún)方式，一般使用關(guān)鍵字INNER JOIN來(lái)實(shí)現(xiàn)。其中INNER關(guān)鍵字可以省略，當(dāng)只使用JOIN關(guān)鍵字時(shí)，語(yǔ)句默認(rèn)表示內(nèi)連接操作。在使用內(nèi)連接查詢(xún)多個(gè)表時(shí)，必須在FROM子句之后定義一個(gè)ON子句，ON子句指定內(nèi)連接操作列與連接條件匹配的數(shù)據(jù)行，使用比較運(yùn)算符比較被連接列的值。簡(jiǎn)單來(lái)說(shuō)，內(nèi)連接就是使用JOIN指定用于連接的兩個(gè)表，使用ON指定表的連接條件。若進(jìn)一步限制查詢(xún)范圍，則可以直接在后面添加WHERE子句。內(nèi)連接的語(yǔ)法格式如下：

SELECT columns_list
FROM table_name1 [INNER] JOIN table_name2
ON join_condition
[WHERE]

• 其中:columns_list：字段列表
• table_name1 和 table_name2 ：兩個(gè)要實(shí)現(xiàn)內(nèi)連接的表。
• join_condition：實(shí)現(xiàn)內(nèi)連接的條件表達(dá)式
•  WHERE ：使用where子句進(jìn)一步限制查詢(xún)范圍。

【例3.1】在scott模式下，通過(guò)deptno字段來(lái)內(nèi)連接emp表和dept表，查詢(xún)dept表中部門(mén)名稱(chēng)為 SALES,并檢索這兩個(gè)表中相關(guān)的字段信息。

select e.empno 員工編號(hào),e.ename 員工姓名,d.dname 部門(mén) 
from emp e inner join dept d 
on e.deptno = d.deptno
where d.dname = 'SALES';

四、外連接

使用外連接進(jìn)行多表查詢(xún)時(shí)，返回的查詢(xún)結(jié)果中只包含符合查詢(xún)條件和連接條件的數(shù)據(jù)。內(nèi)連接消除了與另一個(gè)表中的任何數(shù)據(jù)不匹配的行，而外連接擴(kuò)展了內(nèi)連接的結(jié)果集，除了返回所有匹配的數(shù)據(jù)外，還會(huì)返回一部分或全部不匹配的行，這主要取決于外連接的種類(lèi)。外連接通過(guò)有以下三種。

• 左外連接：關(guān)鍵字為L(zhǎng)EFT OUTER JOIN 或 LEFT JOIN
• 右外連接：關(guān)鍵字為RIGHT OUTER JOIN 或 RIGHT JOIN
• 完全外連接：關(guān)鍵字為FULL OUTER JOIN 或 FULL JOIN

與內(nèi)連接不同的是，外連接不只列出與連接條件匹配的數(shù)據(jù)，還能夠列出左表（左外連接時(shí)）、右表（右外連接時(shí)）、兩個(gè)表（全部外連接時(shí)）中所有符合搜索條件的數(shù)據(jù)。

4.1 左外連接

左外連接的查詢(xún)結(jié)果中不僅包含了滿(mǎn)足連接條件的數(shù)據(jù)，還包含左表中不滿(mǎn)足連接條件的數(shù)據(jù)。

【例4.1.1】首先在emp表中插入一條新數(shù)據(jù)（注意沒(méi)有為deptno和dname列插入值，即它們的值是null），然后實(shí)現(xiàn)emp表和dept表之間通過(guò)deptno列進(jìn)行左外連接。

插入

insert into emp (empno,ename,job) values(9527,'EAST','SALESMAN');

查詢(xún)

select e.empno,e.ename,e.job,d.deptno,d.dname 
from emp e left join dept d
on e.deptno = d.deptno;

結(jié)果

從上面的查詢(xún)結(jié)果中可以看到，雖然新插入的數(shù)據(jù)deptno值為null，但該行記錄讓然出現(xiàn)在查詢(xún)結(jié)果中，說(shuō)明左外連接的查詢(xún)結(jié)果會(huì)包含左表中不滿(mǎn)足“連接條件”的數(shù)據(jù)。 

4.2   右外連接

同樣道理，右外連接的查詢(xún)結(jié)果中不僅包含了滿(mǎn)足連接條件的數(shù)據(jù)行，而且還包含右表中不滿(mǎn)足連接條件的數(shù)據(jù)行。

【例4.2.1】在SCOTT模式下，實(shí)現(xiàn)emp表和dept表之間通過(guò)deptno列進(jìn)行右外連接。

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e
right join dept d 
on e.deptno = d.deptno

運(yùn)行結(jié)果如下

從上面的查詢(xún)結(jié)果中可以看到，雖然部門(mén)編號(hào)為40的數(shù)據(jù)行在emp表中還沒(méi)有員工記錄，但它卻出現(xiàn)在查詢(xún)結(jié)果中，這說(shuō)明右外連接的查詢(xún)結(jié)果會(huì)包含由表中不滿(mǎn)足“連接條件”的數(shù)據(jù)行。

在外連接中也可以使用外連接的連接運(yùn)算符，外連接的連接運(yùn)算符為“(+)”，該連接運(yùn)算符可以放在等號(hào)的左邊，也可以放在等號(hào)的右邊，但是一定要放在缺少信息的那一邊，比如放在e.deptno所在的一方。

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e,dept d
where e.deptno (+)= d.deptno

運(yùn)行結(jié)果

使用(+)操作符時(shí)應(yīng)注意：

1、當(dāng)使用(+)操作符執(zhí)行外連接時(shí)，如果在WHERE子句中包含多個(gè)條件，則必須在所有條件中都包含(+)操作符。

2、(+)操作符只適用于列，而不能用在表達(dá)式上。

3、(+)操作符不能與ON和IN操作符一起使用。

4.3 完全外連接

在執(zhí)行完全外連接時(shí)，Oracle會(huì)執(zhí)行一個(gè)完成的左外連接和右外連接查詢(xún)，然后將查詢(xún)結(jié)果合并，并消除重復(fù)的數(shù)據(jù)。

【例4.3.1】 在SCOTT模式下，實(shí)現(xiàn)emp表和dept表之間通過(guò)deptno列進(jìn)行完全外連接。

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e
full join dept d 
on e.deptno = d.deptno

4.4 自然連接

自然連接和內(nèi)連接功能相似，自然連接是指在檢索多個(gè)表時(shí)，Oracle會(huì)將第一個(gè)表中的列與第二個(gè)表中具有相同名稱(chēng)的列進(jìn)行自動(dòng)連接。在自然連接中，用戶(hù)不需要明確指定進(jìn)行連接的列，這個(gè)任務(wù)有Oracle自動(dòng)完成，自然連接使用NATURAL JOIN 關(guān)鍵字。

【4.4.1】 在emp表中檢索工資(sal字段)大于2000的記錄，并實(shí)現(xiàn)emp表與dept表的自然連接。

select empno,ename,job,dname
from emp natural join dept
where sal > 2000

由于自然連接要求表之間必須具有相同的列名稱(chēng)，這樣容易在設(shè)計(jì)表時(shí)出現(xiàn)不可預(yù)知的錯(cuò)誤，所以在實(shí)際應(yīng)用中很少用到自然連接。另外需要注意的是，在使用自然連接時(shí)，不能為列指定限定詞（即表名或表的別名），否則Oracle會(huì)彈出“ORA-25155:NATURAL連接中使用的列不能有限定詞”的錯(cuò)誤提示.

4.5 自連接

在實(shí)際應(yīng)用中，用戶(hù)可能會(huì)擁有“自引用式”的外鍵。“自引用式”外鍵是指表中的一列可以使該表住建的一個(gè)外鍵。

自連接主要用在自參考表上顯示上下級(jí)關(guān)系或者層次關(guān)系。自參照表是指在同一張表的不同列之間具有參照關(guān)系活著主從關(guān)系的表。例如，emp表包含empno（雇員號(hào)）和mgr(管理員號(hào))列，兩者之間就具有參照關(guān)系。這樣用戶(hù)就可以通過(guò)mgr列與empno列的關(guān)系，實(shí)現(xiàn)查詢(xún)某個(gè)管理者所管理的下屬員工信息。

【例4.5.1】在SCOTT模式下，查詢(xún)所有管理者所管理的下屬員工信息。

select e2.ename 上層管理者,e1.ename 下屬員工
from emp e1 
left join emp e2 
on e1.mgr = e2.empno
order by e1.mgr

4.6 交叉連接

交叉連接實(shí)際上就是不需要任務(wù)連接條件的連接，它使用CROSS JOIN關(guān)鍵字來(lái)實(shí)現(xiàn)，語(yǔ)法格式：

select colums_list
from talble_name1 cross join table_name2

交叉連接的執(zhí)行結(jié)果是一個(gè)笛卡爾積，這種查詢(xún)結(jié)果是非常冗余的，但是可以通過(guò)WHERE子句來(lái)過(guò)濾出有用的記錄信息。

【4.6.1】在SCOTT模式下，通過(guò)交叉連接dept表和emp表，計(jì)算出查詢(xún)結(jié)果的行數(shù)。

select count(*)
from emp cross join dept

總結(jié)

到此這篇關(guān)于oracle基礎(chǔ)教程之多表關(guān)聯(lián)查詢(xún)的文章就介紹到這了,更多相關(guān)oracle多表關(guān)聯(lián)查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論