結構化查詢語言/SELECT：連線操作
資料應該以不包含冗餘資訊的方式儲存在資料庫中。例如，如果我們的資料庫包含一群人，他們都擁有相同的愛好，那麼我們最好避免在每個記錄中重複儲存關於某個愛好的相同靜態資訊。也就是在每個關於愛好者的記錄中。同樣地，我們也應該避免在每個關於愛好者的記錄中重複儲存關於某個愛好者的相同詳細資訊。相反，我們建立獨立的person和hobby表，並在兩者之間建立聯絡。這種將資料分組到獨立的、無冗餘的表中的技術被稱為資料庫規範化。這種分離也有助於簡化邏輯，提高為特定目的組裝所需精確項的靈活性。這種組裝透過'JOIN'操作來完成。
在我們的示例資料庫中，有兩個表：person和contact。contact表包含person_id列，它與person表的Primary-Key列id相關聯。透過評估列值，我們可以將聯絡人和人員連線在一起。
|
person 表 P
|
contact 表 C
|
連線（虛擬）表，由person和contact表建立
| P.ID | P.LASTNAME | P.FIRSTNAME | ... | C.ID | C.PERSON_ID | C.CONTACT_TYPE | C.CONTACT_VALUE |
|---|---|---|---|---|---|---|---|
| 1 | Goldstein | Larry | ... | 1 | 1 | 固定電話 | 555-0100 |
| 1 | Goldstein | Larry | ... | 2 | 1 | 電子郵件 | larry.goldstein@acme.xx |
| 1 | Goldstein | Larry | ... | 3 | 1 | 電子郵件 | lg@my_company.xx |
| 1 | Goldstein | Larry | ... | 4 | 1 | icq | 12111 |
| 2 | Burton | Tom | ... | ? | ? | ? | ? |
| 3 | Hamilton | Lisa | ... | ? | ? | ? | ? |
| 4 | Goldstein | Kim | ... | 5 | 4 | 固定電話 | 5550101 |
| 4 | Goldstein | Kim | ... | 6 | 4 | 手機 | 10123444444 |
| ... | ... | ... | ... | ... | ... | ... | ... |
因此，Larry Goldstein 在儲存的person表中只出現一次，現在在連線的虛擬表中出現了四次，每次都與他的四個聯絡項中的一個組合在一起。Kim Goldstein 和他的兩個聯絡項也是如此。
但是，Tom Burton 和 Lisa Hamilton 的聯絡資訊在哪裡？我們可能會在嘗試將他們的person資料與他們不存在的contact資訊連線時遇到一些麻煩。目前，我們用問號標記了這種情況。關於如何將問題轉化為解決方案的詳細說明將在本頁後面的內容中給出。
顯然，使用 JOIN 操作需要指定兩件事
- 相關表的名稱
- 相關列的名稱
基本語法用這兩個元素擴充套件了 SELECT 命令
SELECT <things_to_be_displayed> -- as usual
FROM <tablename_1> <table_1_alias> -- a table alias
JOIN <tablename_2> <table_2_alias> ON <join condition> -- the join criterion
... -- optionally all the other elements of SELECT command
;
讓我們先嚐試一下。
SELECT *
FROM person p
JOIN contact c ON p.id = c.person_id;
一個表名在 FROM 關鍵字後面引用（如前所示），另一個表名在新的關鍵字 JOIN 後面引用，它（毫不奇怪）指示 DBMS 執行連線操作。接下來，ON 關鍵字引入列名以及比較運算子（或者後面會看到的通用條件）。列名以表的別名 p 和 c 為字首。這是必要的，因為具有相同名稱的列（如 id）可能存在於多個表中。
當 DBMS 執行該命令時，它會提供“某些內容”，其中包含來自兩個表的所以列，包括來自各自表（person 和 contact）的兩個 id 列。結果包含九行，每行對應一個現有的人員和聯絡組合；也就是，由於'ON'表示式的存在，沒有對應聯絡記錄的人員記錄將不會出現在結果中。
提供的“某些內容”看起來像一個新表；事實上，它具有與表相同的結構、行為和資料。如果它是由檢視或作為子選擇的結果建立的，我們甚至可以在它上面執行新的 SELECT。但它與表之間有一個重要的區別：它組裝的資料未儲存在 DBMS 中；相反，資料是在執行時從真實表的數值計算出來的，並且只在 DBMS 執行你的程式時儲存在臨時記憶體中。
這個關鍵特性 - 從簡單表中組裝複雜資訊 - 是透過兩個簡單的關鍵字 JOIN 和 ON 實現的。正如你將看到的，語法可以擴充套件為構建非常複雜的查詢，這樣你就可以在連線條件的規範中新增許多額外的改進。
當結果不符合你的預期時，有時會令人困惑。如果出現這種情況，請嘗試簡化你的查詢，如這裡所示。困惑通常源於 JOIN 語法本身可能會變得相當複雜。此外，連線可以與 SELECT 命令的所有其他語法元素結合使用，這也會導致缺乏清晰度。
以下示例顯示了連線語法與其他語言元素的結合。
--
-- show only important columns
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id;
-- show only desired rows
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
-- apply any sort order
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile')
ORDER BY p.lastname, p.firstname, c.contact_type DESC;
-- use functions: min() / max() / count()
SELECT count(*)
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
-- JOIN a table with itself. Example: Search different persons with the same lastname
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname -- for the second incarnation of person we must use a different alias
WHERE p1.id != p2.id
-- sorting of p2.lastname is not necessary as it is identical to the already sorted p1.lastname
ORDER BY p1.lastname, p1.firstname, p2.firstname;
-- JOIN more than two tables. Example: contact information of different persons with same lastname
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname, c.contact_type, c.contact_value
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname
JOIN contact c ON p2.id = c.person_id -- contact info from person2. p1.id would lead to person1
WHERE p1.id != p2.id
ORDER BY p1.lastname, p1.firstname, p2.lastname;
在本頁的前面，我們看到了一個連線結果的示例，其中一些行包含人員姓名，但沒有聯絡資訊 - 相反，在後面的列中顯示了一個問號。如果使用了 JOIN 操作的基本語法，那麼那些（問號）行將被過濾掉。這種（基本語法，具有排他性結果）被稱為內部連線。還有三種不同的外部連線。外部連線的結果不僅包含內部連線結果中所有完整資料行，還包含部分資料行，即在兩個儲存表中都沒有找到資料的那些行；因此，它們被稱為左外部連線、右外部連線和全外部連線。
因此，我們可以將基本 JOIN 語法擴充套件到四個選項
- [INNER] JOIN
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
- FULL [OUTER] JOIN
方括號 [ ] 中的關鍵字是可選的。解析器從 LEFT、RIGHT 或 FULL 推斷出 OUTER，而普通的（即基本語法）JOIN 預設為 INNER。
內部連線可能是四種類型中最常用的。正如我們所見，它只生成完全匹配 ON 後面的條件的那些行。以下是一個示例，展示瞭如何建立一個包含人員及其聯絡人的列表。
-- A list of persons and their contacts
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
JOIN contact c ON p.id = c.person_id -- identical meaning: INNER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
最重要的是，沒有聯絡資訊的記錄不包含在結果中。
有時我們需要更多；例如，我們可能需要一個包含所有人員記錄的列表，包括可能為此人員提供的所有聯絡資訊記錄。注意，這與上面的示例不同：這次，結果將包含所有人員記錄，即使是那些沒有聯絡資訊記錄的人員記錄。
-- A list of ALL persons plus their contacts
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id -- identical meaning: LEFT OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
在聯絡資訊不可用的情況下，DBMS 會用“空值”或“空特殊標記”來代替（不要與字串（-型別）“空值”或“null”或二進位制 0 混淆。然而，實現細節在這裡並不重要。空特殊標記將在後面的章節中討論）。
總之，左（外）連線是一個內部連線，加上每個左側匹配項在右側沒有對應項的行。
請考慮“左”這個詞。它指的是公式左側的“FROM <table_1> LEFT JOIN <table_2>”，更準確地說，是指左側表示的表（此處：table_1）；表示該表的每一行都將在結果中至少出現一次，無論在右側表（此處：table_2）中是否找到相應的記錄。
另一個例子
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM contact c
LEFT JOIN person p ON p.id = c.person_id -- identical meaning: LEFT OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
有什麼區別？我們改變了表名的順序。請注意，我們仍然使用 LEFT 連線，但由於contact現在是“左側”參照物（FROM 子句中的物件），因此contact資料將被認為是主要重要的；因此，所有聯絡行都將出現在結果中 - 以及人員表中可能存在的任何相關資訊。碰巧的是，在我們使用的資料庫中，每個聯絡記錄都對應一個人員記錄，因此在這種情況下，結果等同於使用內部連線時的情況。但它們與上一個左連線示例的結果不同。
右聯接遵循與左聯接相同的規則，但方向相反。現在，聯接子句中引用的表中的所有記錄都將出現在結果中，包括在其他表中沒有對應記錄的記錄。同樣，DBMS 會用空值特殊標記填充每個空的右側列單元格。唯一的區別是，表的評估順序是反向的，或者換句話說，兩個表的交換角色。
-- A list of ALL contact records with any corresponding person data, even if s
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
RIGHT JOIN contact c ON p.id = c.person_id -- same as RIGHT OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
全聯接檢索左表和右表中的所有行，無論在相應的相反表中是否存在對應記錄。
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
FULL JOIN contact c ON p.id = c.person_id -- identical meaning: FULL OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
給定下面的table_1和table_2，
|
table_1
|
table_2
|
全聯接
SELECT *
FROM table_1 t1
FULL JOIN table_2 t2 ON t1.id = t2.table_1_id;
將產生
| T1.ID | T1.X | T2.ID | T2.TABLE_1_ID | T2.Y |
|---|---|---|---|---|
| 1 | 11 | 1 | 1 | 21 |
| 2 | 12 | null | null | null |
| 3 | 13 | null | null | null |
| null | null | 2 | 5 | 22 |
這些結果包含（單個）匹配行，加上每個原始表中所有其他記錄的每一行。由於這些其他行中的每一行都代表僅在一個表中找到的資料，因此它們都缺少一些資料，因此代表該缺失資料的單元格包含空值特殊標記。
注意：並非所有 DBMS 都支援全聯接。但是，因為它不是一個原子操作，所以總是可以透過多個帶有 SET 操作的 SELECT 的組合來建立所需的結果。
使用內聯接，可以省略 ON。SQL 將其解釋為 - 語法上正確的 - 將左表中的每條記錄與右表中的每條記錄組合的請求。它將返回大量行：兩個表的行數的乘積。
這種特殊的內聯接稱為笛卡爾積或 CROSS JOIN。笛卡爾積是關係代數的基本運算，它是所有 RDBMS 實現的基礎。
-- all persons combined with all contacts (some implementations replace the
-- keyword 'JOIN' with a comma)
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
JOIN contact c -- missing ON keyword: p X c will be created
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
-- count the resulting rows
SELECT count(*)
FROM person p
JOIN contact c;
因此要小心；如果您無意中省略了 ON 項，結果將比預期的大得多。例如，如果第一個表包含 10,000 條記錄，而第二個表包含 20,000 條記錄，則輸出將包含 2 億行。
我們如何建立人員及其愛好的列表？請記住：一個人可以有許多愛好，而幾個人可以有相同的愛好。因此，人員與愛好之間沒有直接聯絡。在兩個表之間，我們建立了第三個表person_hobby。它包含人員的 id 以及愛好的 id。
我們必須從person到person_hobby，然後從那裡到hobby“行走”。
-- persons combined with their hobbies
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
JOIN person_hobby ph ON p.id = ph.person_id
JOIN hobby h ON ph.hobby_id = h.id
ORDER BY p.lastname, p.firstname, h.hobbyname;
請注意，person_hobby表的任何列都不會進入結果。此表僅在中間執行步驟中起作用。即使它的id列也沒有興趣。
有些人沒有愛好。由於我們執行了 INNER JOIN，因此他們不在上面的列表中。如果我們想在列表中檢視沒有愛好的使用者，我們必須執行以前的操作：使用 LEFT OUTER JOIN 而不是 INNER JOIN。
-- ALL persons plus their hobbies (if present)
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
LEFT JOIN person_hobby ph ON p.id = ph.person_id
LEFT JOIN hobby h ON ph.hobby_id = h.id
ORDER BY p.lastname, p.firstname, h.hobbyname;
提示：如有必要，我們可以將每種型別的聯接與其他每種型別的聯接組合成任何所需的順序，例如：LEFT OUTER 與 FULL OUTER 與 INNER ...。
聯接操作的標準並不侷限於通常的公式
SELECT ...
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.fk
...
首先，我們可以使用任何列，而不僅僅是主鍵和外部索引鍵列。在上面的示例之一中，我們使用 lastname 進行聯接。Lastname 是字元型別，沒有任何鍵的意義。為了避免效能低下，一些 DBMS 將列的使用限制在具有索引的列。
其次，比較器並不限於等號。我們可以使用任何有意義的運算子，例如數字值的“大於”。
-- Which person has the greater body weight - restricted to 'de Winter' for clarity
SELECT p1.id, p1.firstname as "is heavier", p1.weight, p2.id, p2.firstname as "than", p2.weight
FROM person p1
JOIN person p2 ON p1.weight > p2.weight
WHERE p1.lastname = 'de Winter'
AND p2.lastname = 'de Winter'
ORDER BY p1.weight desc, p2.weight desc;
第三，我們可以使用任意函式。
-- short lastnames vs. long lastnames
SELECT p1.firstname, p1.lastname as "shorter lastname", p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON LENGTH(p1.lastname) < LENGTH(p2.lastname)
-- likewise ORDER BY can use functions
ORDER BY length(p1.lastname), length(p2.lastname);
顯示有 ICQ 號碼的人員的姓氏和名字以及 ICQ 號碼
SELECT p.id, p.firstname, p.lastname, c.contact_value
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type = 'icq';
顯示有 ICQ 號碼和固定電話的人員的姓氏和名字以及 ICQ 號碼和固定電話號碼。您需要兩次聯接contact表。
SELECT p.id, p.firstname, p.lastname,
c1.contact_value as icq,
c2.contact_value as "fixed line" -- looks like previous, but is different
FROM person p
JOIN contact c1 ON p.id = c1.person_id
JOIN contact c2 ON p.id = c2.person_id -- it's a second (virtual) incarnation of contact table
WHERE c1.contact_type = 'icq' -- from first incarnation
AND c2.contact_type = 'fixed line'; -- from second incarnation
-- In this example of an INNER JOIN we can convert the WHERE part to an additional JOIN criterion.
-- This may clarify the intention of the command. But be careful: This shifting in combination with
-- one of the OUTER JOINs may lead to different results.
SELECT p.id, p.firstname, p.lastname, c1.contact_value as icq, c2.contact_value as "fixed line"
FROM person p
JOIN contact c1 ON p.id = c1.person_id AND c1.contact_type = 'icq'
JOIN contact c2 ON p.id = c2.person_id AND c2.contact_type = 'fixed line';
顯示所有人員的姓氏和名字以及（如果有）ICQ 號碼
-- To retrieve ALL persons, it's necessary to use a LEFT join.
-- But the first approach is not what we expect! In this example, the LEFT JOIN is evaluated first
-- and creates an intermediate table with null-values in contact_type (eliminate the
-- WHERE clause to see this intermediate result). These rows and all other except the
-- one with 'ICQ' are then thrown away by evaluating the WHERE clause.
SELECT p.id, p.firstname, p.lastname, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id
WHERE c.contact_type = 'icq';
-- It's necessary to formulate the search criterion as part of the JOIN. Unlike with
-- the INNER JOIN in the previous example with (LEFT/FULL/RIGHT) OUTER JOINs it is not possible
-- to shift it to the WHERE clause.
SELECT p.id, p.firstname, p.lastname, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id AND c.contact_type = 'icq';
建立一個包含所有愛好以及相應人員（如果有）的列表
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
RIGHT JOIN person_hobby ph ON p.id = ph.person_id
RIGHT JOIN hobby h ON ph.hobby_id = h.id
ORDER BY h.hobbyname, p.lastname, p.firstname;
三個外聯接之一是否可能包含的行數少於相應的內聯接？
No.
All four join types contain the same rows with column-matching-values. In addition
outer joins contain rows where column values do not match - if such a situation exists.