SQL 多表連線技術：Join 實戰與案例解析

在資料函式庫開發中，從多個關聯表中提取資料是常見的需求，SQL 的 Join 技術正是為此而生。本文將探討各種 Join 的用法，並以線上汽車零件銷售系統為例，說明如何使用 Join 整合顧客、零件、供應商和訂單等多個表格的資訊，以產生報表或進行資料分析。首先，我們需要理解資料函式庫設計的基本原則，例如使用主鍵和外部索引鍵建立表格之間的關聯，避免資料冗餘並提高效率。接著，將介紹 Join 的基本語法，例如使用 JOIN 關鍵字搭配 ON 子句指定連線條件，從而將多個表格的資料關聯起來。

不同型別的 Join 會產生不同的結果集。內連線 (Inner Join) 只傳回兩個表格中符合連線條件的資料列，而外連線 (Outer Join) 則會保留某一個表格的所有資料列，即使在另一個表格中找不到比對的資料列。外連線又分為左外連線、右外連線和全外連線，它們分別保留左邊表格、右邊表格或兩個表格的所有資料列。此外，還有交叉連線 (Cross Join)，它會傳回兩個表格所有資料列的笛卡爾積，通常用於特殊場景。選擇哪種 Join 型別取決於具體的業務需求。例如，要查詢所有顧客的姓名和訂單資訊，即使該顧客沒有下過任何訂單，就應該使用左外連線。

在實際案例中，我們可以透過表格別名來簡化 SQL 查詢，提高程式碼的可讀性。例如，將 Orders 表格命名為 o，Customer 表格命名為 c，可以讓 SQL 陳述式更簡潔易懂。此外，理解資料之間的關聯性對於編寫高效的 Join 查詢至關重要。在開始編寫 SQL 之前，應該先仔細研究資料函式庫的 Schema，瞭解每個表格的主鍵和外部索引鍵，以及表格之間的關聯方式。最後，善用索引可以顯著提升 Join 查詢的效能。在經常用於 Join 條件的欄位上建立索引，可以加快資料函式庫查詢比對資料列的速度。

資料函式庫查詢的進階技巧：多表連線 (Join) 實戰

在資料函式庫的世界中，單一表格往往難以滿足複雜的業務需求。當我們需要整合來自多個表格的資訊時，連線 (Join) 就成為不可或缺的技術。身為一位資料函式庫開發者，我經常需要從多個關聯表中提取資料，以產生有意義的報表或分析結果。這篇文章中，玄貓將帶領大家深入瞭解 SQL Join 的各種用法，並分享一些實戰經驗。

為何需要 Join？資料函式庫設計的考量

在深入 Join 的語法之前，讓我們先思考一下資料函式庫設計的本質。以一個線上汽車零件銷售系統為例，我們可能會有以下幾個主要的實體 (Entity)：

顧客 (Customer)
零件 (Part)
供應商 (Supplier)
訂單 (Order)

每個實體都有其相關的屬性 (Attribute)，例如顧客的姓名、地址，零件的名稱、價格等等。為了避免資料重複和提高資料函式庫的效率，我們會將這些實體拆分成多個表格，並透過主鍵 (Primary Key) 和外部索引鍵 (Foreign Key) 建立關聯。

舉例來說，Orders 表格中會包含 custkey 欄位，這個欄位是 Customer 表格的 c_custkey 欄位的外部索引鍵，用來表示該訂單是由哪位顧客所下。

當我們需要查詢同時包含訂單資訊和顧客資訊的報表時，就需要使用 Join 將 Orders 表格和 Customer 表格連線起來。

Join 的基本語法：連線 Orders 表格與 Customer 表格

假設我們需要產生一份報表，顯示所有訂單的訂單編號、訂單狀態、訂單日期，以及下訂單的顧客姓名。我們可以這樣寫 SQL 查詢：

SELECT
    o_orderkey,
    o_orderstatus,
    o_orderdate,
    c_name
FROM
    orders
JOIN
    customer ON orders.o_custkey = customer.c_custkey
LIMIT 10;

內容解密：

SELECT o_orderkey, o_orderstatus, o_orderdate, c_name: 指定要查詢的欄位，分別來自 orders 表格和 customer 表格。
FROM orders JOIN customer: 指定要連線的兩個表格，orders 和 customer。
ON orders.o_custkey = customer.c_custkey: 指定連線條件，使用 orders 表格的 o_custkey 欄位和 customer 表格的 c_custkey 欄位進行連線。這個 ON 子句是 Join 語法的核心，它定義了兩個表格之間如何比對資料。
LIMIT 10: 限制結果集只顯示前 10 筆資料。

這個查詢會將 orders 表格和 customer 表格中，o_custkey 和 c_custkey 欄位值相同的資料列連線起來，然後傳回指定的欄位。

各種 Join 的型別：Inner Join, Outer Join, Cross Join

除了上面範例中的 JOIN (實際上是 INNER JOIN)，SQL 還提供了其他幾種 Join 型別，包括：

INNER JOIN (內連線): 只傳回兩個表格中符合連線條件的資料列。
LEFT OUTER JOIN (左外連線): 傳回左邊表格的所有資料列，以及右邊表格中符合連線條件的資料列。如果右邊表格沒有符合條件的資料列，則傳回 NULL 值。
RIGHT OUTER JOIN (右外連線): 傳回右邊表格的所有資料列，以及左邊表格中符合連線條件的資料列。如果左邊表格沒有符合條件的資料列，則傳回 NULL 值。
FULL OUTER JOIN (全外連線): 傳回兩個表格的所有資料列。如果某個表格沒有符合連線條件的資料列，則傳回 NULL 值。
CROSS JOIN (交叉連線): 傳回兩個表格所有資料列的笛卡爾積 (Cartesian Product)。也就是說，如果左邊表格有 A 筆資料列，右邊表格有 B 筆資料列，則 CROSS JOIN 會傳回 A * B 筆資料列。

實際案例：選擇適當的 Join 型別

假設我們需要查詢所有顧客的姓名和訂單資訊，即使該顧客沒有下過任何訂單。這時候，我們就不能使用 INNER JOIN，因為它只會傳回有下過訂單的顧客資訊。

正確的做法是使用 LEFT OUTER JOIN：

SELECT
    c_name,
    o_orderkey
FROM
    customer
LEFT OUTER JOIN
    orders ON customer.c_custkey = orders.o_custkey;

這個查詢會傳回 customer 表格中所有顧客的姓名，以及他們所下的訂單編號。如果某位顧客沒有下過任何訂單，則 o_orderkey 欄位會顯示 NULL 值。

其他練習題的解法

以下提供先前練習題的解答，幫助大家更熟悉 SQL 語法：

Exercise 2-2:

SELECT
    c_name,
    c_mktsegment,
    c_acctbal
FROM
    customer
WHERE
    c_mktsegment IN ('Machinery', 'Furniture')
    AND c_acctbal BETWEEN -1 AND 1;

內容解密：

此查詢選取 customer 表格中 c_name、c_mktsegment 和 c_acctbal 欄位。
WHERE 子句使用 IN 運算元篩選出 c_mktsegment 為 ‘Machinery’ 或 ‘Furniture’ 的資料列。
AND 運算元確保只選取 c_acctbal 介於 -1 和 1 之間的資料列。

Exercise 2-3:

SELECT
    c_name,
    c_mktsegment,
    c_acctbal
FROM
    customer
WHERE
    (c_mktsegment = 'Machinery' AND c_acctbal = 20)
    OR (c_mktsegment = 'Furniture' AND c_acctbal = 334);

內容解密：

此查詢選取 customer 表格中 c_name、c_mktsegment 和 c_acctbal 欄位。
WHERE 子句使用 OR 運算元連線兩個條件。
第一個條件篩選出 c_mktsegment 為 ‘Machinery’ 與 c_acctbal 為 20 的資料列。
第二個條件篩選出 c_mktsegment 為 ‘Furniture’ 與 c_acctbal 為 334 的資料列。

Exercise 2-4:

SELECT
    Acct_Num,
    Acct_Bal
FROM
    Balances
WHERE
    Acct_Bal IS NULL OR Acct_Bal != 9;

內容解密：

此查詢選取 Balances 表格中 Acct_Num 和 Acct_Bal 欄位。
WHERE 子句使用 OR 運算元連線兩個條件。
第一個條件篩選出 Acct_Bal 為 NULL 的資料列 (因為 NULL 不能使用 = 或 != 運算元比較)。
第二個條件篩選出 Acct_Bal 不等於 9 的資料列。

玄貓的經驗分享

在實際專案中，玄貓經常需要處理複雜的 Join 查詢，連線多個表格以產生報表或進行資料分析。其中一個重要的技巧是理解資料之間的關聯性。在編寫 Join 查詢之前，務必仔細研究資料函式庫的 schema，瞭解每個表格的主鍵和外部索引鍵，以及表格之間的關聯方式。

此外，善用索引 (Index) 也可以大幅提高 Join 查詢的效能。在經常用於 Join 條件的欄位上建立索引，可以讓資料函式庫更快地找到符合條件的資料列。

總之，Join 是 SQL 中非常強大的工具，可以讓我們從多個表格中提取資料，滿足複雜的業務需求。透過深入理解 Join 的各種用法，並善用索引等技巧，我們可以編寫出高效與易於維護的 SQL 查詢。

表格連結：解開資料函式庫的隱藏關係

在資料函式庫的世界中，連結（Join）就像是橋樑，它連線不同的表格，讓我們能夠從多個來源提取相關的資訊。FROM 子句列出了要連結的表格，而 ON 子句則包含了從一個表格到另一個表格的連結條件。掌握表格連結的概念後，玄貓將更深入地探討這個主題。

表格別名：讓 SQL 更簡潔易懂

在 SQL 查詢中，為表格設定別名就像是給它們取暱稱，這能讓你的程式碼更易讀，某些情況下甚至是必要的。玄貓之前介紹過欄位別名，現在讓我們看看錶格別名如何運作。

以下是一個範例：

SELECT
    '歡迎來到 Snowflake SQL!' AS welcome_message,
    5 * 3.1415927 AS circle_circumference,
    DAYNAME(CURRENT_DATE) AS day_of_week;

結果：

+---------------------------+----------------------+-------------+
| WELCOME_MESSAGE           | CIRCLE_CIRCUMFERENCE | DAY_OF_WEEK |
|---------------------------+----------------------+-------------|
| 歡迎來到 Snowflake SQL!    | 15.7079635           | Fri         |
+---------------------------+----------------------+-------------+

你也可以在 FROM 子句中為表格設定別名。這對於提高查詢的可讀性非常有用，而且在某些情況下是必須的。讓我們以前面的查詢為例，為 Orders 表格設定別名 o，為 Customer 表格設定別名 c：

SELECT
    o.o_orderkey,
    o.o_orderstatus,
    o.o_orderdate,
    c.c_name
FROM
    orders AS o
JOIN
    customer AS c
ON
    o.o_custkey = c.c_custkey
LIMIT 10;

結果：

+------------+---------------+-------------+--------------------+
| O_ORDERKEY | O_ORDERSTATUS | O_ORDERDATE | C_NAME             |
|------------+---------------+-------------+--------------------|
| 600006     | O             | 1996-09-16  | Customer#000083098 |
| 600037     | F             | 1994-06-26  | Customer#000107722 |
| 600064     | O             | 1997-11-04  | Customer#000089008 |
| 600065     | F             | 1993-09-15  | Customer#000146441 |
| 600132     | O             | 1998-01-08  | Customer#000131644 |
| 600165     | F             | 1992-12-08  | Customer#000139328 |
| 600228     | F             | 1992-03-01  | Customer#000046379 |
| 600262     | O             | 1997-03-03  | Customer#000011323 |
| 600327     | O             | 1997-02-25  | Customer#000133094 |
| 600484     | O             | 1997-08-11  | Customer#000120598 |
+------------+---------------+-------------+--------------------+

表格別名在 FROM 子句中提供（使用 AS o 和 AS c），並且在 SELECT 子句和 FROM 子句的 ON 子句中被參照。雖然不強制要求在所有地方都使用表格別名，但這是一個好的實踐，特別是在 FROM 子句中參照多個表格時。在本章的剩餘範例中，如果包含兩個或多個表格，都將使用表格別名。

內連結：精準比對的資料橋樑

在先前的範例中，如果在一個表格的 custkey 欄位中存在一個值，但在另一個表格中不存在，則從 Orders 到 Customers 的連結就不存在，並且這些列將從結果集中排除。例如，如果 Orders 表格中的一列在 o_custkey 欄位中具有值 1234，但在 Customer 表格中沒有 c_custkey 欄位的值為 1234 的列，則該訂單將不會出現在結果集中。

這種連結型別稱為內連結（Inner Join），它是最常用的連結型別。內連結是預設的連結型別，你不需要在 FROM 子句中指定 INNER 關鍵字。以下是先前的範例，但這次使用了完全指定的連結型別：

SELECT
    o.o_orderkey,
    o.o_orderstatus,
    o.o_orderdate,
    c.c_name
FROM
    orders AS o
INNER JOIN
    customer AS c
ON
    o.o_custkey = c.c_custkey
LIMIT 10;

結果：

+------------+---------------+-------------+--------------------+
| O_ORDERKEY | O_ORDERSTATUS | O_ORDERDATE | C_NAME             |
|------------+---------------+-------------+--------------------|
| 600006     | O             | 1996-09-16  | Customer#000083098 |
| 600037     | F             | 1994-06-26  | Customer#000107722 |
| 600064     | O             | 1997-11-04  | Customer#000089008 |
| 600065     | F             | 1993-09-15  | Customer#000146441 |
| 600132     | O             | 1998-01-08  | Customer#000131644 |
| 600165     | F             | 1992-12-08  | Customer#000139328 |
| 600228     | F             | 1992-03-01  | Customer#000046379 |
| 600262     | O             | 1997-03-03  | Customer#000011323 |
| 600327     | O             | 1997-02-25  | Customer#000133094 |
| 600484     | O             | 1997-08-11  | Customer#000120598 |
+------------+---------------+-------------+--------------------+

為了更詳細地探討這個概念，玄貓這次使用兩個名為 Customer_simple 和 Orders_simple 的小表格：

CREATE TABLE customer_simple (custkey, custname)
AS
SELECT *
FROM (
    VALUES
        (101, 'BOB'),
        (102, 'KIM'),
        (103, 'JIM')
);

結果：

+---------------------------------------------+
| status                                      |
|---------------------------------------------|
| Table CUSTOMER_SIMPLE successfully created. |
+---------------------------------------------+

SELECT * FROM customer_simple;

結果：

+---------+----------+
| CUSTKEY | CUSTNAME |
|---------+----------|
| 101     | BOB      |
| 102     | KIM      |
| 103     | JIM      |
+---------+----------+

CREATE TABLE orders_simple (ordernum, custkey)
AS
SELECT *
FROM (
    VALUES
        (990, 101),
        (991, 102),
        (992, 101),
        (993, 104)
);

結果：

+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table ORDERS_SIMPLE successfully created. |
+-------------------------------------------+

SELECT * FROM orders_simple;

結果：

+----------+---------+
| ORDERNUM | CUSTKEY |
|----------+---------|
| 990      | 101     |
| 991      | 102     |
| 992      | 101     |
| 993      | 104     |
+----------+---------+

檢視資料，Customer_simple 中有三列，custkey 值分別為 101、102 和 103。Orders_simple 中有四列，其中三列參照了 custkey 101 和 102，但另一列參照了 custkey 104，該值在 Customer_simple 表格中不存在。以下是玄貓使用 custkey 連結這兩個表格時發生的情況：

SELECT
    o.ordernum,
    o.custkey,
    c.custname
FROM
    orders_simple AS o
INNER JOIN
    customer_simple AS c
ON
    o.custkey = c.custkey;

結果：

+----------+---------+----------+
| ORDERNUM | CUSTKEY | CUSTNAME |
|----------+---------+----------|
| 990      | 101     | BOB      |
| 991      | 102     | KIM      |
| 992      | 101     | BOB      |
+----------+---------+----------+

從結果可見，Orders_simple 表格中 ORDERNUM 為 993 的資料因為 CUSTKEY 為 104，在 Customer_simple 表格中找不到對應的客戶，因此沒有出現在結果中。這就是內連結的特性：只傳回兩個表格中都存在的比對資料。

總結，內連結就像一座精準的橋樑，確保只有兩個表格中相互比對的資料才能透過，從而保證了結果的準確性和一致性。

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。