一、概述

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

二、表的別名

在多表關(guān)聯(lián)查詢時，如果多個表之間存在同名的列，則必須使用表名來限定列的引用。例如，在SCOTT模式中，DEPT表和EMP表都有DEPTNO列，當(dāng)用戶使用該列關(guān)聯(lián)查詢兩個表時，就需要通過指定表名來區(qū)分這兩個列的歸屬。但是，隨著查詢變得越來越復(fù)雜，語句就會因為每次限定列必須輸入表名而變得冗長。對于這種情況，SQL提供了設(shè)定表別名的機(jī)制，使用簡短的表別名可以代替原有較長的表名稱，這樣就大大縮減語句的長度。

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

select e.empno 員工編號,e.ename 員工姓名,d.dname 部門 from dept d,emp e where d.deptno = e.deptno and e.job = 'MANAGER';

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

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

【例2.2】 

select e.empno 員工編號,e.ename 員工姓名,d.dname 部門 from dept d,emp e where d.deptno = e.deptno and emp.job = 'MANAGER';

 總結(jié)：

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

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

        3、表的別名只在所定義的查詢語句中有效。

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

三、內(nèi)連接

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

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

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

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

select e.empno 員工編號,e.ename 員工姓名,d.dname 部門 
from emp e inner join dept d 
on e.deptno = d.deptno
where d.dname = 'SALES';

四、外連接

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

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

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

4.1 左外連接

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

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

插入

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

查詢

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

結(jié)果

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

4.2   右外連接

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

【例4.2.1】在SCOTT模式下，實現(xiàn)emp表和dept表之間通過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

運行結(jié)果如下

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

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

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

運行結(jié)果

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

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

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

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

4.3 完全外連接

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

【例4.3.1】 在SCOTT模式下，實現(xiàn)emp表和dept表之間通過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)連接功能相似，自然連接是指在檢索多個表時，Oracle會將第一個表中的列與第二個表中具有相同名稱的列進(jìn)行自動連接。在自然連接中，用戶不需要明確指定進(jìn)行連接的列，這個任務(wù)有Oracle自動完成，自然連接使用NATURAL JOIN 關(guān)鍵字。

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

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

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

4.5 自連接

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

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

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

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

4.6 交叉連接

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

select colums_list
from talble_name1 cross join table_name2

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

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

select count(*)
from emp cross join dept

總結(jié)

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

最新評論