關聯式資料函式庫的核心概念之一是將資料分散到多個相關表格中，每個表格代表一個實體，並透過鍵值建立關聯。這種設計有效避免資料冗餘並提升資料一致性。SQL 的 JOIN 操作提供了一個強大的機制，可以根據關聯條件合併來自不同表格的資料，實作更複雜的查詢需求。理解不同 JOIN 型別的特性，例如 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN，對於撰寫高效的 SQL 查詢至關重要。此外，文章也探討了使用 USING 子句簡化 JOIN 語法，以及 CROSS JOIN 的應用場景和注意事項。
連線多表格於關聯式資料函式庫
在第2章中，我們介紹了關聯式資料函式庫的概念，這是一種支援跨多個相關表格儲存資料的應用程式。在關聯模型中，每個表格通常儲存有關單一實體的資料——例如學生、汽車、購買記錄或房屋——而表格中的每一列則描述其中一個實體。一個稱為表格連線（table join）的過程允許我們將一個表格中的列與其他表格中的列進行連結。
使用 JOIN 連結表格
要連線查詢中的表格，我們使用 JOIN ... ON 結構（或其他本章將介紹的 JOIN 變體）。JOIN 是 ANSI SQL 標準的一部分，它使用 ON 子句中的布林值表示式將資料函式庫中的一個表格連結到另一個表格。常用的語法測試相等性，通常採用以下形式：
SELECT *
FROM table_a JOIN table_b
ON table_a.key_column = table_b.foreign_key_column;
這與您已經學過的基本 SELECT 陳述式相似，但不是在 FROM 子句中命名一個表格，而是命名一個表格，給出 JOIN 關鍵字，然後命名第二個表格。接著是 ON 子句，我們在其中放置使用等於比較運算元的表示式。當查詢執行時，它傳回兩個表格中 ON 子句中的表示式評估為 true 的列，也就是指定列中的值相等的列。
您可以使用任何評估為布林結果 true 或 false 的表示式。例如，您可以匹配一列中的值大於或等於另一列中的值的情況：
ON table_a.key_column >= table_b.foreign_key_column;
雖然這種情況很少見，但如果您的分析需要這樣做，它是一個可用的選項。
使用鍵列關聯表格
考慮這個使用鍵列關聯表格的例子：假設您是一位資料分析師，任務是檢查某公共機構按部門劃分的薪水支出。您提交了一份《資訊自由法》請求，要求該機構提供薪水資料，預期收到一個簡單的電子試算表，列出每位員工及其薪水，排列如下：
| dept | location | first_name | last_name | salary | |
|
|
-|
|
-| | IT | Boston | Julia | Reyes | 115300 | | IT | Boston | Janet | King | 98000 | | Tax | Atlanta | Arthur | Pappas | 72700 | | Tax | Atlanta | Michael | Taylor | 89500 |
百分位數計算與陣列處理
在 PostgreSQL 中，我們可以使用 percentile_cont 函式計算百分位數，如下所示：
SELECT percentile_cont(ARRAY[.25, .5, .75])
WITHIN GROUP (ORDER BY pop_est_2019)
AS quartiles
FROM us_counties_pop_est_2019;
結果與陣列處理
執行上述查詢後，PostgreSQL 傳回一個陣列，以大括號表示結果。每個四分位數以逗號分隔。第一四分位數是 10,902.5，表示 25% 的縣份人口等於或低於此值。第二四分位數與中位數相同：25,726。第三四分位數是 68,072.75，表示最大的 25% 縣份至少有這麼多人口。
使用 unnest() 將陣列轉換為列
為了使結果更易讀，我們可以使用 unnest() 函式將陣列轉換為列，如下所示：
SELECT unnest(
percentile_cont(ARRAY[.25, .5, .75])
WITHIN GROUP (ORDER BY pop_est_2019)
) AS quartiles
FROM us_counties_pop_est_2019;
執行後，輸出變為：
| quartiles | |
| | 10902.5 | | 25726 | | 68072.75 |
尋找眾數
我們可以使用 PostgreSQL 的 mode() 函式來尋找眾數，即出現次數最多的值。該函式不是標準 SQL 的一部分，其語法與百分位數函式類別似。如下所示：
SELECT mode() WITHIN GROUP (ORDER BY births_2019)
FROM us_counties_pop_est_2019;
結果是 86，表示有 16 個縣份的出生人數相同。
練習題
編寫一個 SQL 陳述式，計算半徑為 5 英寸的圓的面積。（如果您不記得公式，可以輕鬆地在網上搜尋。）您的計算中是否需要括號？為什麼需要或不需要？
使用 2019 年美國人口普查縣級估計資料，計算紐約州每個縣的出生與死亡比率。2019 年該州哪個地區的出生與死亡比率通常較高？
2019 年加利福尼亞州或紐約州的縣級人口估計中位數較高？
資料函式庫表格關聯與JOIN查詢基礎
在資料函式庫設計中，將資料拆分成多個相關的表格是常見的做法。這種做法遵循Codd的關聯模型，每個表格描述一個特定的實體及其屬性。例如，在一個機構的人事資料函式庫中，我們可以有兩個表格：departments（部門）和employees（員工）。
建立部門與員工表格
首先，我們來建立departments和employees兩個表格，並插入一些範例資料，如清單7-1所示。
CREATE TABLE departments (
dept_id integer,
dept text,
city text,
CONSTRAINT dept_key PRIMARY KEY (dept_id),
CONSTRAINT dept_city_unique UNIQUE (dept, city)
);
CREATE TABLE employees (
emp_id integer,
first_name text,
last_name text,
salary numeric(10,2),
dept_id integer REFERENCES departments (dept_id),
CONSTRAINT emp_key PRIMARY KEY (emp_id)
);
INSERT INTO departments
VALUES
(1, 'Tax', 'Atlanta'),
(2, 'IT', 'Boston');
INSERT INTO employees
VALUES
(1, 'Julia', 'Reyes', 115300, 1),
(2, 'Janet', 'King', 98000, 1),
(3, 'Arthur', 'Pappas', 72700, 2),
(4, 'Michael', 'Taylor', 89500, 2);
內容解密：
departments表格：包含dept_id（部門ID，主鍵）、dept（部門名稱）和city（城市）。dept_id是主鍵，用於唯一識別每個部門。(dept, city)的組合具有唯一性約束，確保不會有重複的部門名稱和城市組合。employees表格：包含emp_id（員工ID，主鍵）、first_name（名）、last_name（姓）、salary（薪水）和dept_id（部門ID，外部索引鍵）。dept_id是外部索引鍵，參照departments表格的dept_id，確保每個員工都隸屬於一個有效的部門。
為何要將資料拆分成多個表格？
將資料拆分成多個相關的表格有幾個好處：
- 避免資料重複：如果將所有資料放在一個表格中，部門名稱和位置等資訊會重複出現在每個員工的紀錄中，浪費儲存空間。
- 簡化資料管理：當部門名稱或位置需要更新時，只需修改一個地方，而不是多個重複的紀錄，降低出錯的風險。
- 保持資料完整性：透過外部索引鍵約束，確保每個員工都隸屬於一個有效的部門。
使用JOIN查詢多個表格
當我們需要從多個相關的表格中檢索資料時，可以使用SQL的JOIN操作。JOIN操作根據指定的條件，將兩個或多個表格中的資料結合起來。
JOIN查詢的基本語法
SELECT *
FROM employees
JOIN departments
ON employees.dept_id = departments.dept_id;
內容解密：
JOIN departments ON employees.dept_id = departments.dept_id：這行程式碼指定了JOIN操作，將employees和departments兩個表格根據dept_id欄位進行連線。只有當兩個表格中的dept_id值相匹配時，對應的資料列才會被結合在一起。
JOIN查詢的結果
執行上述JOIN查詢後，我們會得到一個結合了員薪水訊和對應部門資訊的結果集。例如：
| emp_id | first_name | last_name | salary | dept_id | dept_id | dept | city | |
-|
-|
|
–|
–|
–|
|
–| | 1 | Julia | Reyes | 115300 | 1 | 1 | Tax | Atlanta | | 2 | Janet | King | 98000 | 1 | 1 | Tax | Atlanta | | 3 | Arthur | Pappas | 72700 | 2 | 2 | IT | Boston | | 4 | Michael | Taylor | 89500 | 2 | 2 | IT | Boston |
這樣，我們就可以在一個查詢結果中同時看到員工的詳細資訊和他們所屬部門的資訊。
資料函式庫表格的連線：JOIN 的使用與解析
在資料函式庫查詢中，JOIN 是用來合併來自不同表格資料的重要工具。透過 JOIN，我們可以根據特定的條件將多個表格中的資料整合在一起，以獲得所需的資訊。本篇文章將探討 JOIN 的使用方式、不同型別的 JOIN，以及實際的應用範例。
基本的 JOIN 操作
在 SQL 中，JOIN 用於根據兩個表格之間的共同欄位，將它們的資料合併。以下是一個簡單的範例，展示如何使用 JOIN 連線 employees 和 departments 兩個表格：
SELECT *
FROM employees
JOIN departments
ON employees.dept_id = departments.dept_id
ORDER BY employees.dept_id;
內容解密：
- SELECT *：選擇
employees和departments表格中的所有欄位。 - FROM employees JOIN departments：指定要連線的兩個表格，分別是
employees和departments。 - ON employees.dept_id = departments.dept_id：定義連線條件，即當兩個表格中的
dept_id欄位值相等時，將對應的資料列合併。 - ORDER BY employees.dept_id：根據
employees表格中的dept_id欄位對結果進行排序。
執行此查詢後，結果將包含兩個表格中 dept_id 欄位相符的所有資料列。由於使用了 SELECT *，結果中將包含兩個表格的所有欄位，包括重複的 dept_id 欄位。
瞭解不同的 JOIN 型別
SQL 提供了多種 JOIN 型別，以滿足不同的資料檢索需求。常見的 JOIN 型別包括：
- JOIN（或 INNER JOIN）：傳回兩個表格中在連線條件下相符的資料列。
- LEFT JOIN：傳回左表格中的所有資料列，以及右表格中相符的資料列。如果右表格中沒有相符的資料，則對應的欄位將顯示為 NULL。
- RIGHT JOIN：與 LEFT JOIN 相反，傳回右表格中的所有資料列，以及左表格中相符的資料列。
- FULL OUTER JOIN：傳回兩個表格中的所有資料列，並根據連線條件合併相符的資料。如果某個表格中沒有相符的資料，則對應欄位將顯示為 NULL。
- CROSS JOIN：傳回兩個表格中所有可能的資料列組合，即笛卡爾積。
實際範例：使用不同的 JOIN 型別
假設我們有兩個表格，分別是 district_2020 和 district_2035，用於記錄某學區在不同年份的學校資料。以下是建立和填充這兩個表格的 SQL 程式碼：
CREATE TABLE district_2020 (
id integer CONSTRAINT id_key_2020 PRIMARY KEY,
school_2020 text
);
CREATE TABLE district_2035 (
id integer CONSTRAINT id_key_2035 PRIMARY KEY,
school_2035 text
);
INSERT INTO district_2020 (id, school_2020)
VALUES
(1, 'Oak Street School'),
(2, 'Roosevelt High School'),
(5, 'Dover Middle School'),
(6, 'Webutuck High School');
INSERT INTO district_2035 (id, school_2035)
VALUES
(1, 'Oak Street School'),
(2, 'Roosevelt High School'),
(3, 'Morrison Elementary'),
(4, 'Chase Magnet Academy'),
(6, 'Webutuck High School');
內容解密：
- 建立表格：使用
CREATE TABLE陳述式建立district_2020和district_2035表格，並定義id欄位為主鍵。 - 插入資料：使用
INSERT INTO陳述式向兩個表格中插入學校資料。
使用 JOIN 連線表格
現在，我們可以使用 JOIN 連線這兩個表格，以找出在兩個年份中都存在的學校。以下是範例查詢：
SELECT *
FROM district_2020
JOIN district_2035
ON district_2020.id = district_2035.id
ORDER BY district_2020.id;
內容解密：
- 連線條件：根據
id欄位連線兩個表格。 - 結果排序：根據
district_2020表格中的id欄位對結果進行排序。
執行此查詢後，結果將顯示在兩個年份中都存在的學校，即 id 為 1、2 和 6 的學校。
何時使用 JOIN？
當你需要從結構良好、維護完善的資料集中找出在所有連線表格中都存在的資料列時，通常會使用 JOIN。由於 JOIN 不會傳回只存在於其中一個表格中的資料列，因此如果你需要檢視其中一個或多個表格中的所有資料，請考慮使用其他型別的 JOIN。
SQL 連線查詢的進階應用
在處理多個資料表之間的資料時，SQL 的連線（JOIN）操作是不可或缺的工具。本文將探討幾種不同的連線型別，包括 JOIN with USING、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 和 CROSS JOIN，並透過具體範例來展示它們的用法和結果。
使用 USING 子句進行連線
當兩個資料表在連線條件中使用相同的欄位名稱時，可以使用 USING 子句來簡化查詢語法並減少冗餘輸出。以下是一個例子：
SELECT *
FROM district_2020
JOIN district_2035
USING (id)
ORDER BY district_2020.id;
內容解密：
USING (id)指定了連線的條件，即兩個資料表的id欄位必須相同。- 由於
id在兩個資料表中是相同的，因此在結果中只會顯示一次。 - 這種寫法比使用
ON子句更簡潔。
LEFT JOIN 和 RIGHT JOIN
與普通的 JOIN 不同，LEFT JOIN 和 RIGHT JOIN 會傳回一個資料表中的所有列，並根據匹配條件從另一個資料表中取得相應的資料。如果沒有匹配，則結果中對應的欄位將為空。
LEFT JOIN 範例：
SELECT *
FROM district_2020
LEFT JOIN district_2035
ON district_2020.id = district_2035.id
ORDER BY district_2020.id;
內容解密：
LEFT JOIN保證了district_2020中的所有列都會出現在結果中。- 如果
district_2035中沒有匹配的id，則對應的欄位將顯示為空值。
RIGHT JOIN 範例：
SELECT *
FROM district_2020
RIGHT JOIN district_2035
ON district_2020.id = district_2035.id
ORDER BY district_2035.id;
內容解密：
RIGHT JOIN保證了district_2035中的所有列都會出現在結果中。- 如果
district_2020中沒有匹配的id，則對應的欄位將顯示為空值。
FULL OUTER JOIN
當需要取得兩個資料表中的所有列，無論是否有匹配時，可以使用 FULL OUTER JOIN。
SELECT *
FROM district_2020
FULL OUTER JOIN district_2035
ON district_2020.id = district_2035.id
ORDER BY district_2020.id;
內容解密：
- 結果中包含了
district_2020和district_2035中的所有列。 - 如果某個
id只存在於一個資料表中，則另一個資料表的對應欄位將為空。
CROSS JOIN
CROSS JOIN 會傳回兩個資料表的笛卡爾積，即每個左表的列都會與右表的每一列進行組合。
SELECT *
FROM district_2020
CROSS JOIN district_2035
ORDER BY district_2020.id, district_2035.id;
內容解密：
- 結果的行數是兩個資料錶行數的乘積。
- 由於不需要匹配條件，因此不需要使用
ON子句。 - 在處理大資料表時應避免使用
CROSS JOIN，因為它可能產生非常龐大的結果集。
