一、概述

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

二、表的別名

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

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

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

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

另外，還需要注意一點，一旦在FROM子句中為表指定了別名，則必須在剩余的子句中都是用表的別名，而不允許再使用原來的表名稱，否則，將出現【例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';

 總結：

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

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

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

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

三、內連接

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

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

• 其中:columns_list：字段列表
• table_name1 和 table_name2 ：兩個要實現內連接的表。
• join_condition：實現內連接的條件表達式
•  WHERE ：使用where子句進一步限制查詢范圍。

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

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

四、外連接

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

• 左外連接：關鍵字為LEFT OUTER JOIN 或 LEFT JOIN
• 右外連接：關鍵字為RIGHT OUTER JOIN 或 RIGHT JOIN
• 完全外連接：關鍵字為FULL OUTER JOIN 或 FULL JOIN

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

4.1 左外連接

左外連接的查詢結果中不僅包含了滿足連接條件的數據，還包含左表中不滿足連接條件的數據。

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

插入

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;

結果

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

4.2   右外連接

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

【例4.2.1】在SCOTT模式下，實現emp表和dept表之間通過deptno列進行右外連接。

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

運行結果如下

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

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

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

運行結果

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

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

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

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

4.3 完全外連接

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

【例4.3.1】 在SCOTT模式下，實現emp表和dept表之間通過deptno列進行完全外連接。

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 自然連接

自然連接和內連接功能相似，自然連接是指在檢索多個表時，Oracle會將第一個表中的列與第二個表中具有相同名稱的列進行自動連接。在自然連接中，用戶不需要明確指定進行連接的列，這個任務有Oracle自動完成，自然連接使用NATURAL JOIN 關鍵字。

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

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

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

4.5 自連接

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

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

【例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 交叉連接

交叉連接實際上就是不需要任務連接條件的連接，它使用CROSS JOIN關鍵字來實現，語法格式：

select colums_list
from talble_name1 cross join table_name2

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

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

select count(*)
from emp cross join dept

總結

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

最新評論