Oracle檢索資料——多表關聯查詢

在實際應用中查詢資料可能會涉及多個資料表，每個表不是獨立存在的，而是若幹個表之間的資訊存在一定的關系，當查詢一個表的資訊時，很可能需要查詢關聯資料表的資訊，這就是多表關聯查詢。

1、表别名

　　在進行多表查詢時，如果多個表之間存在同名的列，則必須使用表名來限定列的引用。SQL 語言提供了設定表别名的機制，使用簡短的表别名就可以替代原有較長的表名稱，可以大大縮短語句的長度。

　　SQL > select e.empno as 員工編号，e.ename as 員工名稱， d.dname as 部門 from emp e,dept d where e.deptno = d.deptno and e.job ="Manager";

　表示通過部門号（deptno）來管理emp表和dept表，并查詢這兩個表中相關字段的資訊。

　注意：一旦在from 子句中為表指定了列名，則必須在剩餘的子句中都使用表别名。　　

2、内連接配接

　　内連接配接是常用的多表關聯查詢，使用關鍵字inner join來實作，其中，inner 關鍵字可以省略，使用 join 即代表内聯接。使用内聯接查詢多個表時，必須在 from 子句之後定義一個 on 子句，用來指定兩個表實作内聯接的“連接配接 條件”。

　　使用内聯接進行多表查詢時，傳回的查詢結果是隻包含查詢條件和連接配接條件的行，消除了與另一個表中任何行不比對的行。

　　注意：在内聯接的查詢結果中，所有記錄行都是滿足連接配接條件的。

　　文法：

　　　　select column_list

　　　　from table_name1 [ innter ] join table_name2

　　　　on join_condition;

• 　columns_list : 字段清單
• 　table_name1 和 table_name2 :兩個要實作内連接配接的表。
• join_condition: 實作内連接配接的條件表達式。

SQL > select e.empno as 員工編号， e.ename as 員工名稱，d.dname as 部門 from emp e inner join dept d on e.deptno = d.deptno;

表示通過 deptno 字段來連接配接emp 表和 dept表，并查詢兩個表中相關的字段。

3、外連接配接

　　多表之間進行外連接配接時，除了傳回所有比對的行外，還會傳回一部分或全部不比對的行，這主要取決于外連接配接的種類，主要由以下3種：

　 (1) 左外連接配接：關鍵字為left Outer join 或 left join

　　左外聯接的結果集包括 LEFT OUTER子句中指定的左表的所有行，而不僅僅是聯接列所比對的行。如果左表的某行在右表中沒有比對行，則在相關聯的結果集行中右表的所有選擇清單列均為空值。

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

　　表示從員工表（emp）表中查詢出來所有的記錄并查詢部門表(dept)中兩個deptno值相等的記錄，如果沒有相比對的行，均為空值。

（2）右外連接配接：關鍵字為 right outer join 或 right join

　　右向外聯接是左向外聯接的反向聯接。将傳回右表的所有行。如果右表的某行在左表中沒有比對行，則将為左表傳回空值。

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

　　表示從部門表（dept）中查詢出來所有的記錄，并查詢員工表（emp）表中與之deptno 對應的記錄，如果沒有比對的行，均為空值。

（3） 完全外連接配接：關鍵字為 full outer join 或 full join

　　完整外部聯接傳回左表和右表中的所有行。當某行在另一個表中沒有比對行時，則另一個表的選擇清單列包含空值。如果表之間有比對行，則整個結果集行包含基表的資料值。

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

　　表示查詢兩個表中所有的deptno的記錄，如果對方的表中沒有比對的行，置為空值。

4、自然連接配接

　　自然連接配接是指在查詢多個表時，Oracle 會将第一個表中的列與第二表中具有相同名稱的列進行自動連接配接。自然連接配接中，使用者不需要指定進行連續的列，這個任務由 Oracle 系統西段完成，自然連接配接使用“Natural join”關鍵字。

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

　　表示在emp表中查詢工資（sal）大于2000的記錄，并實作emp表與dept 表的自然連接配接。

　注意：自然連接配接強制要求表之間必須具有相同的列名稱，但是在實際開發中很少用到。在使用自然連接配接時，不能為列指定限定詞（即表名或表的别名），否則Oracle 會報錯。

5、自連接配接

　　在開發中，使用者可能會擁有“自引用式”的外鍵，是指表中的一個列可以是該表主鍵的一個外鍵。

6、交叉連接配接

　　交叉連接配接實際上就是不需要任何連接配接條件的連接配接，它使用 cross join 關鍵字來實作。

　　文法： select colums_list from table_name1 cross join table_name2;

• colums_list ： 字段清單
• table_name： 兩個實作交叉連接配接的表名。

　　交叉連接配接的執行結果是一個笛卡爾積，這種查詢非常備援，但可以通過where 子句來過濾出有用的記錄。

　圖解：

-------------------------------------------------------------------------------------------------------------------

　　　表A Id name 表B ID job p_id

　　　 1 張三 1 25 1

2 李四 2 36 2

3 王五 3 36 4

a.id與b.p_id存在關系

-------------------------------------------------------------------------------------------------------------------（1）内連接配接

SQL > select a.*,b.* from a inner join b where a.id=b.p_id;

---------------------------------------------------------------------------------

　　　 a.Id a. name b.id b.job b.p_id

　　　 1 張三 1 25 1

2 李四 2 36 2

----------------------------------------------------------------------------------

（2）左外連接配接

SQL > select a.*，b.* from a left join b where a.id=b.p_id;

---------------------------------------------------------------------------------

　　 a.Id a. name b.id b.job b.p_id

　　　1 張三 1 25 1

2 李四 2 36 2

　　　3 王五 null null null

----------------------------------------------------------------------------------

（3）右外連接配接

SQL > select a.*，b.* from a right join b where a.id=b.p_id;

---------------------------------------------------------------------------------

　　　a.Id a. name b.id b.job b.p_id

　　　1 張三 1 25 1

2 李四 2 36 2

　　　null null 3 36 4

----------------------------------------------------------------------------------

（4）完全連接配接

SQL > select a.*,b.* from a full join b on a.id=b.p_id

---------------------------------------------------------------------------------

　　a.Id a. name b.id b.job b.p_id

　　 1 張三 1 25 1

2 李四 2 36 2

　　null null 3 36 4

　　3 王五 null null null

----------------------------------------------------------------------------------