資料函式庫多表連線與自連線技術解析

– 8 分鐘閱讀

在資料函式庫操作中，連線多個表格是常見的需求，用於整合不同表格的資料。本文將詳細介紹內連線、外連線、交叉連線等多表連線方式，並探討自連線的應用場景和技術細節，以 Snowflake SQL 語法為例，提供實用的程式碼範例和圖表說明。透過這些範例，讀者可以更清晰地理解不同連線型別的特性和使用方法，並學習如何根據實際需求選擇合適的連線方式。同時，文章也提供了一些最佳實踐建議，幫助讀者編寫高效且易於維護的 SQL 查詢陳述式。

資料函式庫表連線與別名使用

在資料函式庫查詢中，表連線（Join）是一種重要的操作，用於結合多個表中的資料以滿足特定的查詢需求。Snowflake SQL 支援多種表連線方式，包括內連線（Inner Join）。

表別名（Table Aliases）

表別名允許為查詢中的表指定簡短的別名，以提高查詢的可讀性和簡潔性。在 FROM 子句中使用 AS 關鍵字可以為表指定別名。例如：

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;

內容解密：

  • SELECT 子句中，o 和 c 分別是 orders 和 customer 表的別名，用於簡化欄位參照。
  • FROM 子句中，AS o 和 AS c 分別為 orders 和 customer 表指定別名。
  • ON 子句用於指定連線條件，確保只有滿足 o_custkey = c_custkey 的資料列被包含在結果集中。

內連線（Inner Joins）

內連線是最常見的連線型別，傳回兩個表中滿足連線條件的資料列。如果一個表的某個欄位值在另一個表中不存在，則該資料列不會出現在結果集中。

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

內容解密：

  • INNER JOIN 關鍵字用於指定內連線操作。
  • ON o.custkey = c.custkey 指定了連線條件，只有當 orders_simple 表中的 custkey 與 customer_simple 表中的 custkey 相匹配時，相關資料列才會被包含在結果中。
  • 結果集中不包含 orders_simple 中 custkey = 104 的資料列，因為 customer_simple 表中沒有對應的 custkey 值。

範例分析

考慮以下兩個簡單的表：

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

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

執行內連線查詢後，結果如下：

| ORDERNUM | CUSTKEY | CUSTNAME | |

|

–|

| | 990 | 101 | BOB | | 991 | 102 | KIM | | 992 | 101 | BOB |

圖表呈現

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 資料函式庫多表連線與自連線技術解析

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖示描述了內連線操作的流程。客戶表和訂單表根據 custkey 進行匹配，只有當匹配成功時，相關資料才會被傳回。

資料函式庫連線的型別與應用

在資料函式庫查詢中，連線（Join）是一種重要的操作，用於合併來自不同表格的資料。根據連線條件和需求的不同，可以分為多種型別，包括內連線（Inner Join）、外連線（Outer Join）和交叉連線（Cross Join）。

內連線（Inner Join）

內連線是最常見的連線型別，它只傳回兩個表格中滿足連線條件的行。例如，將 orders_simple 表格與 customer_simple 表格透過 custkey 進行內連線，查詢結果只包含兩個表格中 custkey 匹配的行。

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

內容解密：

  • SELECT o.ordernum, o.custkey, c.custname：選擇需要顯示的欄位，包括訂單號碼、客戶鍵值和客戶名稱。
  • FROM orders_simple AS o：指定主要的資料來源表格 orders_simple，並將其別名設為 o。
  • INNER JOIN customer_simple AS c：將 customer_simple 表格與 orders_simple 進行內連線，並將其別名設為 c。
  • ON o.custkey = c.custkey：定義連線條件，即兩個表格的 custkey 必須相等。

外連線（Outer Join）

外連線分為左外連線（Left Outer Join）和右外連線（Right Outer Join），它們會傳回一個表格中的所有行，無論另一個表格中是否存在匹配的行。

左外連線範例：

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

內容解密：

  • LEFT OUTER JOIN：表示左外連線，傳回左邊表格（orders_simple）的所有行。
  • 若右邊表格（customer_simple）沒有匹配的行，則對應欄位顯示為 NULL。

右外連線範例：

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

內容解密：

  • RIGHT OUTER JOIN：表示右外連線，傳回右邊表格（customer_simple）的所有行。
  • 若左邊表格（orders_simple）沒有匹配的行，則對應欄位顯示為 NULL。

交叉連線（Cross Join）

交叉連線傳回兩個表格的笛卡爾積，即每一個表格中的每一行都與另一個表格中的每一行進行組合。

SELECT years.yearnum, qtrs.qtrname, qtrs.startmonth, qtrs.endmonth
FROM (VALUES (2020), (2021), (2022)) AS years (yearnum)
CROSS JOIN (VALUES ('Q1',1,3), ('Q2',4,6), ('Q3',7,9),('Q4',10,12)) AS qtrs (qtrname, startmonth, endmonth)
ORDER BY 1,2;

內容解密：

  • CROSS JOIN：進行交叉連線操作，將兩個虛擬表格合併。
  • (VALUES (2020), (2021), (2022)) AS years (yearnum)：建立一個虛擬表格，包含年份資料。
  • (VALUES ('Q1',1,3), ('Q2',4,6), ('Q3',7,9),('Q4',10,12)) AS qtrs (qtrname, startmonth, endmonth)：建立另一個虛擬表格，包含季度資料。
  • ORDER BY 1,2：按第一欄位和第二欄位排序結果。

連線多個表格

在實際應用中，常常需要連線多個表格以取得所需的資料。例如，若要查詢每個訂單的客戶名稱和零件名稱，需要連線 orders_simple、customer_simple、lineitem 和 part 四個表格。

-- 描述 lineitem 表格結構
DESCRIBE TABLE lineitem;

-- 描述 part 表格結構
DESCRIBE TABLE part;

內容解密：

  • DESCRIBE TABLE lineitem; 和 DESCRIBE TABLE part; 用於顯示表格的結構，包括欄位名稱、資料型別等資訊。

多表連線與自連線技術解析

在資料函式庫查詢中，連線多個表格是常見的需求。本文將探討多表連線的技術細節，並以 Snowflake 為例，展示如何有效地進行多表連線與自連線操作。

多表連線的基本原理

多表連線允許我們從多個相關的表格中檢索資料。以下是一個範例，展示瞭如何連線四個表格：Orders、Customer、Lineitem 和 Part。

連線條件與 SQL 語法

首先，我們需要定義表格之間的連線條件：

  • Orders 連線 Customer：使用 o_custkey 和 c_custkey
  • Orders 連線 Lineitem：使用 o_orderkey 和 l_orderkey
  • Lineitem 連線 Part：使用 l_partkey 和 p_partkey
SELECT 
  o.o_orderkey, 
  o.o_orderdate, 
  c.c_name, 
  p.p_name
FROM 
  orders AS o
INNER JOIN 
  customer AS c ON o.o_custkey = c.c_custkey
INNER JOIN 
  lineitem AS l ON o.o_orderkey = l.l_orderkey
INNER JOIN 
  part AS p ON l.l_partkey = p.p_partkey
LIMIT 10;

查詢結果分析

查詢結果如下：

| O_ORDERKEY | O_ORDERDATE | C_NAME | P_NAME | |

-|

–|

-|

-| | 1800003 | 1992-01-29 | Customer#000142558 | orange sandy… | | 1800003 | 1992-01-29 | Customer#000142558 | midnight nav… | | … | … | … | … |

連線順序的影響

值得注意的是，Snowflake 會根據最佳化策略決定表格的存取順序，因此在 FROM 子句中指定的表格順序不一定會影響查詢的執行順序。

自連線技術

自連線是指將一個表格與其自身進行連線。這在處理具有自參照外部索引鍵的表格時特別有用。

建立範例表格

CREATE TABLE employee (
  empid NUMBER, 
  emp_name VARCHAR(30), 
  mgr_empid NUMBER
) AS 
SELECT * 
FROM (
  VALUES
  (1001, 'Bob Smith', NULL),
  (1002, 'Susan Jackson', 1001),
  (1003, 'Greg Carpenter', 1001),
  (1004, 'Robert Butler', 1002),
  (1005, 'Kim Josephs', 1003),
  (1006, 'John Tyler', 1004)
);

自連線查詢範例

SELECT 
  e.empid, 
  e.emp_name, 
  mgr.emp_name AS mgr_name
FROM 
  employee AS e
INNER JOIN 
  employee AS mgr ON e.mgr_empid = mgr.empid;

查詢結果如下：

| EMPID | EMP_NAME | MGR_NAME | |

|

–|

–| | 1002 | Susan Jackson | Bob Smith | | 1003 | Greg Carpenter | Bob Smith | | … | … | … |

使用外連線包含所有員工

為了包含沒有經理的員工（如 Bob Smith），我們需要使用左外連線：

SELECT 
  e.empid, 
  e.emp_name, 
  mgr.emp_name AS mgr_name
FROM 
  employee AS e
LEFT OUTER JOIN 
  employee AS mgr ON e.mgr_empid = mgr.empid;

最佳實踐建議

  • 在進行多表連線時，謹慎選擇連線條件和連線型別（內連線或外連線）。
  • 使用別名簡化 SQL 語法並提高可讀性。
  • 利用資料函式庫的最佳化功能，如 Snowflake 的自動查詢最佳化。

資料函式庫連線操作探討

在資料函式庫查詢中，連線（Join）是一種重要的操作，用於結合來自不同表格的資料。本章節將詳細介紹不同型別的連線操作，包括內連線、外連線和交叉連線，並探討如何將同一張表格多次連線到不同的表格或自身。

自連線：將表格連線到自身

自連線是一種特殊的連線方式，用於將同一張表格視為不同的實體進行連線。例如，在 Employee 表格中，每位員工都有一個經理（由 mgr_empid 表示），我們可以透過自連線來取得員工及其經理的資訊。

SELECT e1.emp_name AS employee, e2.emp_name AS manager
FROM employee e1
LEFT JOIN employee e2
ON e1.mgr_empid = e2.empid;

內容解密：

  • SELECT e1.emp_name AS employee, e2.emp_name AS manager：選擇員工姓名和經理姓名。
  • FROM employee e1 LEFT JOIN employee e2：將 employee 表格自身進行左外連線，分別賦予別名 e1 和 e2。
  • ON e1.mgr_empid = e2.empid：根據員工的經理ID與經理的員工ID進行匹配。

這種查詢方式能夠完整呈現所有員工及其對應的經理，即使某些員工的經理ID為空值。

將同一張表格多次連線到不同表格

在某些情況下，我們需要將同一張表格多次連線到不同的表格。例如，假設我們在 Employee 表格中新增了兩個欄位：birth_nationkey 和 current_nationkey，分別表示員工的出生國家和目前所在國家。我們需要透過這兩個欄位與 Nation 表格進行連線，以取得國家的名稱。

首先，我們修改 Employee 表格結構並更新資料：

ALTER TABLE employee ADD COLUMN birth_nationkey INTEGER;
ALTER TABLE employee ADD COLUMN current_nationkey INTEGER;

UPDATE employee
SET birth_nationkey = empid - 1000,
    current_nationkey = empid - 999;

然後，我們可以透過以下查詢取得員工的出生國家和目前所在國家的名稱：

SELECT e.empid, e.emp_name,
       n1.n_name AS birth_nation, n2.n_name AS current_nation
FROM employee e
INNER JOIN nation AS n1
ON e.birth_nationkey = n1.n_nationkey
INNER JOIN nation AS n2
ON e.current_nationkey = n2.n_nationkey;

內容解密：

  • SELECT e.empid, e.emp_name, n1.n_name AS birth_nation, n2.n_name AS current_nation：選擇員工ID、姓名、出生國家和目前所在國家。
  • FROM employee e INNER JOIN nation AS n1 ON e.birth_nationkey = n1.n_nationkey：將 employee 表格與 nation 表格進行內連線，以取得出生國家的名稱。
  • INNER JOIN nation AS n2 ON e.current_nationkey = n2.n_nationkey：再次將 employee 表格與 nation 表格進行內連線，以取得目前所在國家的名稱。

練習題

請根據以下 Pet_Owner 和 Pet 表格資料，編寫一個查詢陳述式，傳回每位寵物主人的姓名及其寵物的名稱。請勿包含沒有寵物的主人或沒有主人的寵物。

Pet_Owner                          Pet
+
---
-
---
---
+
---
-
---
-
---
-+       +
---
-
---
-+
---
-
---
---
+
---
-
---
---
+
| OWNER_ID | OWNER_NAME |       | PET_ID | OWNER_ID | PET_NAME |
|
---
-
---
---
|
---
-
---
-
---
-|       +
---
-
---
-+
---
-
---
---
+
---
-
---
---
+
| 1        | John       |       | 101    | 1        | Fluffy   |
| 2        | Cindy      |       | 102    | 3        | Spot     |
| 3        | Laura      |       | 103    | 4        | Rover    |
| 4        | Mark       |       | 104    | NULL     | Rosco    |
+
---
-
---
---
+
---
-
---
-
---
-+       +
---
-
---
-+
---
-
---
---
+
---
-
---
---
+

解答參考：附錄 B 第 3 章。

玄貓 BlackCat

玄貓 BlackCat

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