SQL - 從多個表獲取資料

從多個表顯示資料

大型資料庫的相關表透過使用外部索引鍵和主鍵或通常稱為公共列來連結。聯接表的能力將使您能夠為生成的輸出表新增更多含義。對於在查詢中聯接的“n”個表，至少需要 (n-1) 個聯接條件。根據聯接條件，Oracle 將匹配的行對組合起來，並顯示滿足聯接條件的行。

聯接分類如下

自然聯接（也稱為等值聯接或簡單聯接） - 透過使用一個共同命名和定義的列來建立聯接。
非等值聯接 - 當要聯接的表中沒有等效行時聯接表 - 例如，將表中一列的值與另一表中的一系列值匹配。
自聯接 - 將表聯接到自身。
外聯接 - 當另一個表中沒有匹配記錄時，將表的記錄包含在輸出中。
笛卡爾積聯接（也稱為笛卡爾積或交叉聯接） - 將第一個表中的每一行與第二個表中的每一行復制。透過顯示所有可能的記錄組合在表之間建立聯接。

自然聯接

NATURAL 關鍵字可以簡化等值聯接的語法。只要兩個（或多個）表具有相同名稱的列，並且這些列是聯接相容的，即這些列具有共享的值域，就可以進行 NATURAL JOIN。聯接操作將具有相同命名列的相等列值的表的行聯接起來。

考慮 DEPARTMENTS 和 EMPLOYEES 表之間的一對多關係。每個表都包含一個名為 DEPARTMENT_ID 的列。此列是 DEPARTMENTS 表的主鍵，也是 EMPLOYEES 表的外部索引鍵。

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E NATURAL JOIN departments D;

FIRST_NAME DNAME
---------- ------
MILLER     DEPT 1
JOHN       DEPT 1
MARTIN     DEPT 2
EDWIN      DEPT 2

以下 SELECT 查詢透過使用 ON 關鍵字顯式指定聯接條件來聯接這兩個表。

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E JOIN departments D
ON (E.department_id = D.department_id);

關於 NATURAL JOIN，有一些限制。您不能使用 NATURAL JOIN 指定 LOB 列。此外，參與聯接的列不能由表名或別名限定。

USING 子句

使用自然聯接，Oracle 隱式識別構成聯接基礎的列。許多情況需要顯式宣告聯接條件。在這種情況下，我們使用 USING 子句來指定聯接條件。由於 USING 子句基於列的相等性聯接表，因此它也稱為等值聯接。它們也稱為內部聯接或簡單聯接。

語法

SELECT <column list>
FROM   TABLE1   JOIN   TABLE2	
USING (column name)

考慮以下 SELECT 查詢，EMPLOYEES 表和 DEPARTMENTS 表使用公共列 DEPARTMENT_ID 聯接。

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E JOIN departments D
USING (department_id);

自聯接

當感興趣的關係存在於儲存在單個表中的行之間時，SELF-JOIN 操作會生成一個結果表。換句話說，當一個表聯接到自身時，該聯接稱為自聯接。

考慮 EMPLOYEES 表，其中包含員工及其主管資訊。要查詢員工的主管姓名，需要對 EMP 表本身進行聯接。這是自聯接的典型候選。

SELECT e1.FirstName Manager,e2.FirstName Employee
FROM employees e1 JOIN employees e2
ON (e1.employee_id = e2.manager_id)
ORDER BY e2.manager_id DESC;

非等值聯接

當相關列無法使用等號聯接時，使用非等值聯接 - 意味著要聯接的表中沒有等效行。非等值聯接使您能夠在一個記錄的一列中儲存範圍的最小值，並在另一列中儲存最大值。因此，您可以使用非等值聯接來確定要運送的專案是否位於列中的最小值和最大值之間，而不是查詢列與列的匹配項。如果聯接確實找到了專案的匹配範圍，則可以在結果中返回相應的運費。與傳統的等值聯接方法一樣，可以在 WHERE 子句中執行非等值聯接。此外，JOIN 關鍵字可以與 ON 子句一起使用以指定聯接的相關列。

SELECT E.first_name,
            J.job_hisal,
            J.job_losal,
            E.salary
     FROM employees E JOIN job_sal J
     ON (E.salary BETWEEN J.job_losal AND J.job_losal);

我們可以使用前面討論的所有比較引數，例如等號和不等號運算子、BETWEEN、IS NULL、IS NOT NULL 和 RELATIONAL。

外聯接

外聯接用於識別一個表中的行與第二個表中的行不匹配的情況，即使這兩個表是相關的。

外聯接有三種類型：LEFT、RIGHT 和 FULL OUTER JOIN。它們都以 INNER JOIN 開始，然後添加回一些已刪除的行。LEFT OUTER JOIN 添加回聯接條件中從第一個（左側）表中刪除的所有行，並且第二個（右側）表中的輸出列設定為 NULL。RIGHT OUTER JOIN 添加回聯接條件中從第二個（右側）表中刪除的所有行，並且第一個（左側）表中的輸出列設定為 NULL。FULL OUTER JOIN 添加回兩個表中刪除的所有行。

右外聯接

RIGHT OUTER JOIN 添加回聯接條件中從第二個（右側）表中刪除的所有行，並且第一個（左側）表中的輸出列設定為 NULL。請注意，以下查詢列出了員工及其相應的部門。此外，還沒有員工被分配到部門 30。

SELECT E.first_name, E.salary, D.department_id          
FROM employees E, departments D
WHERE E.DEPARTMENT_ID (+) = D.DEPARTMENT_ID;

FIRST_NAME SALARY     DEPARTMENT_ID
---------- ---------- ----------
JOHN       6000       10
EDWIN      2000       20
MILLER     2500       10
MARTIN     4000       20
                      30

左外聯接

LEFT OUTER JOIN 添加回聯接條件中從第一個（左側）表中刪除的所有行，並且第二個（右側）表中的輸出列設定為 NULL。上面演示的查詢可以透過交換 (+) 符號的位置來用於演示左外聯接。

SELECT E.first_name, E.salary, D.department_id
FROM employees E, departments D
WHERE   D.DEPARTMENT_ID = E.DEPARTMENT_ID (+);

FIRST_NAME SALARY     DEPARTMENT_ID
---------- ---------- ----------
JOHN       6000       10
EDWIN      2000       20
MILLER     2500       10
MARTIN     4000       20
                      30

全外聯接

FULL OUTER JOIN 添加回兩個表中刪除的所有行。以下查詢顯示列出了員工及其部門。請注意，員工“MAN”目前尚未分配任何部門（為 NULL），並且部門 30 也沒有分配給任何員工。

SELECT  nvl (e.first_name,'-') first_name, nvl (to_char (d.department_id),'-') department_id
FROM employee e FULL OUTER JOIN department d
ON e. depARTMENT_ID = d. depARTMENT_ID;

FIRST_NAME DEPARTMENT_ID
---------- --------------------
MAN        -
JOHN       10
EDWIN      20
MILLER     10
MARTIN     20
-          30

6 rows selected.

笛卡爾積或交叉聯接

對於兩個實體 A 和 B，A * B 稱為笛卡爾積。笛卡爾積包含每個表中所有行的所有可能組合。因此，當一個表有 10 行與一個表有 20 行聯接時，笛卡爾積為 200 行 (10 * 20 = 200)。例如，將包含 8 行的員工表與包含 3 行的部門表聯接將生成一個包含 24 行的笛卡爾積表 (8 * 3 = 24)。

交叉聯接是指兩個表的笛卡爾積。它生成兩個表的交叉積。以上查詢可以使用 CROSS JOIN 子句編寫。

笛卡爾積結果表通常不是很有用。事實上，這樣的結果表可能會產生誤導。如果您對 EMPLOYEES 和 DEPARTMENTS 表執行以下查詢，則結果表暗示每個員工都與每個部門存在關係，我們知道事實並非如此！

SELECT E.first_name, D.DNAME
FROM employees E,departments D;

交叉聯接可以寫成，

SELECT E.first_name, D.DNAME
FROM employees E CROSS JOIN departments D;

列印頁面