【東軟實訓】SQL多表鏈接

如果一個查詢同時涉及兩個以上的表，則稱之為鏈接查詢，鏈接查詢是關系數據庫中最主要的查詢，主要包括等值鏈接查詢、非等值鏈接查詢、自身鏈接查詢、外鏈接查詢和復合條件鏈接查詢。?
這篇博文我們來對多表鏈接進行學習。

Outline

• 鏈接的基本概念
• Oracle自有的鏈接方法
  • 等值鏈接
  • 非等值鏈接
  • 自身鏈接
• SQL標準語法連接方法
  
  • 交叉連接（笛卡爾連接）
  • 自然連接
  • Using與On語句
  • 左外連接、右外連接、全連接

Notes

## 鏈接的基本概念

• 鏈接是在多個表之間通過一定的鏈接條件，使表之間發生關聯，進而能從多個表之間獲取數據。
• 語法為

  SELECT    table1.column, table2.column
  FROM    table1, table2
  WHERE    table1.column1 = table2.column2;

• 在 WHERE子句中書寫鏈接條件。N個表相連時，至少需要N－1個鏈接條件。
• 如果在多個表中出現相同的列名，則需要使用表名作為來自該表的列名的前綴。

【鏈接的分類】

　　一個用戶查詢請求涉及到多個表的時候，鏈接兩個表的條件為=時，就是等值鏈接查詢；其他的運算符鏈接的就是非等值查詢。?注意：鏈接條件中的各鏈接字段類型必須是可比的，但不必是相同的，整型和實型是可比的，但是字符型和整型就不可比。

　　內連接，也被稱為自然連接，只有兩個表相匹配的行才能在結果集中出現。返回的結果集選取了兩個表中所有相匹配的數據，舍棄了不匹配的數據。

　　外連接不僅包含符合連接條件的行，還包含左表(左連接時)、右表(右連接時)或兩個邊接表(全外連接)中的所有數據行。

?

## Oracle自有的連接方法（select from語句）

【等值連接】

?鏈接兩個表的條件為=時，就是等值鏈接查詢

SQL> SELECT     emp.empno,   emp.ename, emp.deptno, dept.deptno, dept.loc2  FROM       emp, dep
3  WHERE      emp.deptno=dept.deptno;

?

注意：我們還可以使用And運算符添加其他查詢條件，如：

注意：我們還可以用列別名提高性能和限制歧義列名

• 在用到多個表時可以使用表名作前綴來限定列；
• 通過使用表前綴可以提高性能；
• 通過使用列的別名可以區分來自不同表但是名字相同的列；

如：

?

【非等值連接】

?

SQL>    SELECT  e.ename, e.sal, s.grade2    FROM    emp e,   salgrade s3    WHERE   e.sal4    BETWEEN     s.losal AND s.hisal;

?

?

【自身連接】

　　?一個表與自己進行連接，這種連接稱為表的自身連接查詢。?
　　具體實現的時候，我們可以把自己的表起兩個別名，一個是first, 一個是second.在設計的時候可以把這兩個表想成是完全兩個一樣的表，但是各自的字段我們都可以只有調用訪問。?

SQL> SELECT worker.ename||' leader is '||manager.ename2  FROM    emp worker, emp manager3  WHERE   worker.mgr = manager.empno;

?

?## SQL標準語法連接方法

?【交叉連接】

• 交叉連接會產生連個表的交叉乘積，和兩個表之間的笛卡爾積是一樣的；
• 交叉連接使用 CROSS JOIN 子句完成。
• 笛卡爾積： 第一個表中的所有行和第二個表中的所有行都發生連接。
• 笛卡爾積在下列情況產生：
  • 連接條件被省略
  • 連接條件是無效的
• 為了避免笛卡爾積的產生，通常需要在WHERE子句中包含一個有效的連接條件。

<--笛卡爾積寫法-->
SQL> SELECT     emp.empno,   emp.ename, emp.deptno,dept.deptno, dept.locFROM       emp, dept;


<--交叉連接寫法-->
SELECT     emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc 
FROM       emp 
CROSS  JOIN dept; 

?

【自然連接】

• 自然連接是對兩個表之間相同名字和數據類型的列進行的等值連接；
• 如果兩個表之間相同名稱的列的數據類型不同，則會產生錯誤；
• 使用NATURAL JOIN子句來完成。如：

  SELECT  empno,ename,sal,deptno,loc 
  FROM    emp 
  NATURAL JOIN     dept; 

【Using和On語句】

自然連接是使用所有名稱和數據類型相匹配的列作為連接條件，而USING子句可以指定用某個或某幾個相同名字和數據類型的列作為連接條件，如：

SELECT     e.ename,e.ename,e.sal,deptno,d.loc 
FROM       emp e JOIN dept d USING (deptno) 
WHERE      deptno = 20 ;

?

• 使用USING子句創建連接時，應注意以下幾點：
  • 如果如果有若干個列名稱相同但數據類型不同，自然連接子句可以用USING子句來替換，以指定產生等值連接的列。
  • 有多于一個列都匹配的情況，使用USING子句只能指定其中的一列。
  • USING子句中的用到的列不能使用表名和別名作為前綴。
  • NATURAL JOIN子句和USING子句是相互排斥的，不能同時使用
• On語句：
  • 自然連接條件基本上是具有相同列名的表之間的等值連接；
  • 如果要指定任意連接條件,或指定要連接的列，則可以使用ON子句；
  • 用ON將連接條件和其它檢索條件分隔開，其它檢索條件寫在WHERE子句；
  • ON子句可以提高代碼的可讀性。

SELECT     e.empno, e.ename, d.loc,m.ename 
FROM           emp e 
JOIN           dept d 
ON             e.deptno = d.deptno 
JOIN           emp m 
ON             e.mgr = m.empno; 

?

【左外連接、右外連接、全連接】

• 外連不但返回符合連接和查詢條件的數據行，還返回不符合條件的一些行。外連接分三類：左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）和全外連接（FULL OUTER JOIN）。
• 三者的共同點是都返回符合連接條件和查詢條件（即：內連接）的數據行。不同點如下：
  • 左外連接（?）還返回左表中不符合連接條件單符合查詢條件的數據行。
  • 右外連接（?）還返回右表中不符合連接條件單符合查詢條件的數據行。
  • 全外連接（）還返回左表中不符合連接條件單符合查詢條件的數據行，并且還返回右表中不符合連接條件單符合查詢條件的數據行。全外連接實際是上左外連接和右外連接的數學合集（去掉重復），即“全外=左外 UNION 右外”。
• 說明：左表就是在“（LEFT OUTER JOIN）”關鍵字左邊的表。在三種類型的外連接中，OUTER 關鍵字是可省略的。

<--左外連接-->
SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
LEFT OUTER JOIN dept d 
ON         (e.deptno = d.deptno); 

<--右外連接-->
SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
RIGHT OUTER JOIN dept d 
ON         (e.deptno = d.deptno); 

<--全連接-->
SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
FULL OUTER JOIN dept d 
ON         (e.deptno = d.deptno); 

?