SQL多表查詢與連線技術解析

– 9 分鐘閱讀

在資料函式庫應用中，操作多個表格並擷取所需資訊至關重要。本文將探討 SQL 中的進階查詢技巧，涵蓋視窗函式中 ROWS 與 RANGE 的差異、PIVOT 和 UNPIVOT 的資料轉換技巧，以及 JOIN 和 UNION 的多表操作方法。同時，我們也將探討 CTE 的使用、各種 JOIN 型別的應用場景，以及自連線的特殊用法，以提升資料函式庫查詢效率和程式碼可讀性。這些技巧能協助開發者更有效率地處理複雜的資料函式庫查詢任務。

資料函式庫查詢進階概念：多表操作與查詢技巧

在資料函式倉管理系統中，如何有效地處理多個表格之間的關係以及執行複雜的查詢是一項重要的技能。本篇文章將探討資料函式庫查詢中的一些進階概念，包括視窗函式的使用、PIVOT 和 UNPIVOT 操作，以及如何使用 JOIN 和 UNION 操作來合併多個表格的資料。

視窗函式：ROWS 與 RANGE 的比較

在使用視窗函式時，我們經常需要指定一個框架（frame）來定義計算的範圍。ROWS 和 RANGE 是兩種不同的框架指定方式，它們在計算累計值或其他聚合函式時會表現出不同的行為。

SELECT month, name,
       SUM(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt_rows,
       SUM(sales) OVER (ORDER BY month RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt_range
FROM sales;

內容解密：

  1. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：此子句指定了從結果集的第一行到當前行的所有行進行計算。這意味著對於每一行，rt_rows 會累計從第一行到當前行的銷售總額。
  2. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：此子句根據 ORDER BY 子句中的排序欄位（此例中為 month）來定義框架。如果有多行具有相同的 month 值，則這些行會被視為同一範圍內的行，並一起計算。因此，對於相同的 month，rt_range 的值會相同。

PIVOT 和 UNPIVOT 操作

PIVOT 和 UNPIVOT 是用於資料轉換的操作，可以將資料從一種格式轉換為另一種格式。在 Oracle 和 SQL Server 中，這些操作可以透過特定的語法來實作。

使用 PIVOT 操作

假設我們有一個表格記錄了每個人的水果消費情況，我們希望將水果種類別作為欄位，將每個人的消費數量作為值。

-- Oracle
SELECT *
FROM fruits
PIVOT (COUNT(id) FOR fruit IN ('strawberries', 'grapefruit', 'watermelon'));

-- SQL Server
SELECT *
FROM fruits
PIVOT (COUNT(id) FOR fruit IN ([strawberries], [grapefruit], [watermelon])) AS fruits_pivot;

內容解密：

  1. PIVOT 操作：將 fruit 欄位中的不同值轉換為新的欄位，並計算每個人的消費數量。
  2. COUNT(id)：計算每個水果種類別的消費次數。
  3. FOR fruit IN (...)：指定要轉換的水果種類別。

使用 UNPIVOT 操作

相反地，如果我們有多個欄位代表不同的水果，我們可以使用 UNPIVOT 操作將這些欄位合併成一個欄位。

-- Oracle
SELECT *
FROM favorite_fruits
UNPIVOT (fruit FOR rank IN (fruit_one AS 1, fruit_two AS 2, fruit_thr AS 3));

-- SQL Server
SELECT *
FROM favorite_fruits
UNPIVOT (fruit FOR rank IN (fruit_one, fruit_two, fruit_thr)) AS fruit_unpivot
WHERE fruit <> '';

內容解密：

  1. UNPIVOT 操作：將多個水果欄位合併成一個 fruit 欄位，並建立一個新的 rank 欄位來表示原來的欄位順序。
  2. fruit FOR rank IN (...)：指定要合併的欄位及其對應的排序值。

JOIN 和 UNION 操作

在處理多個表格時，JOIN 和 UNION 是兩種常用的操作。JOIN 用於合併兩個表格的欄位，而 UNION 用於合併兩個表格的行。

-- JOIN 操作
SELECT c.id, l.city
FROM customers c
INNER JOIN loc l
ON c.lid = l.id;

-- UNION 操作
SELECT name, city
FROM employees
UNION
SELECT name, city
FROM customers;

內容解密：

  1. JOIN 操作：根據兩個表格之間的共同欄位，將它們的欄位合併。
  2. UNION 操作：將兩個查詢結果集合併成一個結果集，並去除重複的行。

SQL 多表查詢與連線技術解析

在資料函式倉管理與分析中，SQL 的多表查詢技術是至關重要的技能之一。本篇文章將探討 SQL 中的多表查詢技術，包括公用表格表示式（CTE）、各種連線（JOIN）型別及其應用。

公用表格表示式（CTE）

公用表格表示式（Common Table Expressions, CTE）是一種暫時儲存查詢結果的機制，讓後續的查詢可以參照這些結果。CTE 可以包含遞迴或階層式查詢，增強了 SQL 查詢的彈性。

CTE 範例程式碼

WITH my_cte AS (
  SELECT name, 
         SUM(order_id) AS num_orders
  FROM customers
  GROUP BY name
)
SELECT MAX(num_orders)
FROM my_cte;

內容解密：

  1. WITH my_cte AS (...)：定義了一個名為 my_cte 的 CTE。
  2. SELECT name, SUM(order_id) AS num_orders FROM customers GROUP BY name：在 CTE 內部，根據客戶名稱分組並計算每個客戶的訂單總數。
  3. SELECT MAX(num_orders) FROM my_cte：主查詢從 CTE 中找出最大的訂單數量。

SQL 中的表連線（JOIN）

表連線是將多個表格的資料結合在一個查詢中的技術。以下將介紹不同型別的連線及其應用。

連線型別

連線型別描述結果
INNER JOIN傳回兩個表格中匹配的行僅包含兩個表格中都有的資料
LEFT JOIN傳回左表格的所有行及右表格的匹配行包含左表格所有資料，右表格無匹配時顯示 NULL
RIGHT JOIN傳回右表格的所有行及左表格的匹配行包含右表格所有資料，左表格無匹配時顯示 NULL
FULL OUTER JOIN傳回兩個表格中的所有行包含兩個表格的所有資料，無匹配時顯示 NULL
CROSS JOIN傳回兩個表格的所有可能組合產生笛卡爾積，所有可能的組合

連線範例程式碼

SELECT *
FROM states s 
INNER JOIN pets p 
ON s.name = p.name;

內容解密：

  1. SELECT * FROM states s INNER JOIN pets p ON s.name = p.name：使用 INNER JOIN 連線 states 和 pets 兩個表格，條件是 name 欄位相同。
  2. 結果中僅包含 name 欄位在兩個表格中都存在的資料列。

各種連線型別的詳細解析

  1. INNER JOIN：只傳回兩個表格中符合連線條件的行。
  2. LEFT JOIN：傳回左表格的所有行，以及右表格中符合條件的行。如果右表格沒有匹配，則結果為 NULL。
  3. RIGHT JOIN：與 LEFT JOIN 相反，傳回右表格的所有行，以及左表格中符合條件的行。
  4. FULL OUTER JOIN：傳回兩個表格中的所有行，無論是否有匹配。
  5. CROSS JOIN：傳回兩個表格的所有可能組合，即笛卡爾積。

其他連線語法

除了標準的 JOIN 語法外，SQL 還提供了其他連線方式，例如 USING 和 NATURAL JOIN。

USING 範例

SELECT *
FROM states 
INNER JOIN pets 
USING (name);

內容解密：

  1. USING (name)：當連線條件是兩個表格中的同名欄位時，可以使用 USING 簡化語法。

NATURAL JOIN 範例

SELECT *
FROM states 
NATURAL JOIN pets;

內容解密：

  1. NATURAL JOIN：自動根據兩個表格中的所有同名欄位進行連線。

自連線（Self Join）

自連線是指一個表格與其自身進行連線操作，可以使用舊式語法或新的 JOIN 語法。

自連線範例

SELECT *
FROM states s1 
INNER JOIN states s2 
ON s1.region = s2.region;

內容解密：

  1. 將 states 表格與其自身連線，條件是 region 欄位相同，用於找出相同地區的資料列。

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

在資料函式倉管理系統中，多表連線是一項基本且重要的技術，用於合併不同表格中的資料。本文將探討SQL中各種表連線的方法及其應用場景。

內連線（INNER JOIN）與舊式連線語法

傳統上，SQL使用逗號分隔表格名稱來進行連線操作，語法如下：

SELECT *
FROM states s, pets p
WHERE s.name = p.name;

此語法等同於使用INNER JOIN：

SELECT *
FROM states s 
INNER JOIN pets p
ON s.name = p.name;

兩者皆傳回兩表中名稱相符的資料列。

程式碼解析：

  1. SELECT * FROM states s, pets p WHERE s.name = p.name;

    • 這是舊式的連線語法，透過WHERE子句指定連線條件。
    • 等同於INNER JOIN操作。

  2. SELECT * FROM states s INNER JOIN pets p ON s.name = p.name;

    • 使用現代SQL語法進行內連線。
    • ON子句明確指定了連線條件。

多表連線與多欄位連線

多表連線範例

SELECT *
FROM states s
INNER JOIN pets p
ON s.name = p.name
INNER JOIN lunch l
ON s.name = l.name;

此查詢連線了三個表格：states、pets和lunch，並根據name欄位進行匹配。

多欄位連線範例

SELECT *
FROM states_ages s 
INNER JOIN pets_ages p
ON s.name = p.name
AND s.age = p.age;

此查詢根據name和age兩個欄位進行連線操作。

左連線（LEFT JOIN）、右連線（RIGHT JOIN）與全外連線（FULL OUTER JOIN）

左連線（LEFT JOIN）

SELECT *
FROM states s 
LEFT JOIN pets p
ON s.name = p.name;

傳回states表中的所有記錄，以及pets表中匹配的記錄。若無匹配，則pets欄位顯示NULL。

右連線（RIGHT JOIN）

SELECT *
FROM states s 
RIGHT JOIN pets p
ON s.name = p.name;

與左連線相反，傳回pets表中的所有記錄，以及states表中匹配的記錄。

全外連線（FULL OUTER JOIN）

SELECT *
FROM states s 
FULL OUTER JOIN pets p
ON s.name = p.name;

傳回兩表中的所有記錄，無匹配的欄位以NULL填充。

USING與NATURAL JOIN

USING子句

SELECT *
FROM states 
INNER JOIN pets
USING (name);

當連線條件是兩個表中的同名欄位時，可以使用USING簡化語法。

自然連線（NATURAL JOIN）

-- MySQL, Oracle, PostgreSQL 和 SQLite 支援 NATURAL JOIN
SELECT *
FROM states 
NATURAL JOIN pets;

自動根據兩個表中所有同名欄位進行連線。

技術深度分析

  1. 效能考量：資料函式庫系統在執行JOIN操作時，通常會採用最佳化的演算法，而非簡單的笛卡爾積後再過濾。
  2. 語法選擇：現代SQL語法（如INNER JOIN）比舊式逗號分隔語法更具可讀性和維護性。
  3. 相容性問題：不同資料函式庫系統對某些JOIN語法的支援程度不同（如SQLite不支援RIGHT JOIN）。

多表查詢與連線操作

在資料函式庫查詢中，經常需要同時處理多張表格以獲得所需的資訊。本章節將探討如何使用 SQL 語言進行多表查詢與連線操作。

自然連線（NATURAL JOIN）

自然連線是一種簡化的內連線（INNER JOIN）語法，它根據兩張表格中相同名稱的欄位進行連線。與內連線不同的是，自然連線會自動合併相同名稱的欄位。

-- 使用 INNER JOIN ... ON ... 語法
SELECT *
FROM states_ages s INNER JOIN pets_ages p
ON s.name = p.name
AND s.age = p.age;

-- 使用 NATURAL JOIN 簡化語法
SELECT *
FROM states_ages NATURAL JOIN pets_ages;

內容解密：

  1. 自然連線會自動根據相同名稱的欄位進行連線，簡化了查詢語法。
  2. 使用自然連線時需要注意，如果表格結構變更，可能會導致意外的連線結果。
  3. 自然連線適合用於快速查詢，但不建議在正式的生產程式碼中使用。

交叉連線（CROSS JOIN）與自連線（Self Join）

交叉連線用於傳回兩張表格中所有可能的組合，而自連線則是將一張表格與其自身進行連線，用於比較表格內的資料。

-- 使用 CROSS JOIN 傳回所有組合
SELECT *
FROM states CROSS JOIN pets;

-- 等效的舊式語法
SELECT *
FROM states, pets;

內容解密：

  1. 交叉連線傳回笛卡爾積，即兩張表格所有可能的組合。
  2. 自連線需要使用別名來區分同一張表格的不同例項。
  3. 自連線可用於查詢員工與主管的對應關係，或同一部門內的不同員工之間的關係。

自連線範例

假設有一張員工表格，包含員工 ID、姓名、部門和主管 ID。

SELECT * FROM employee;
+
---
---
+
---
-
---
-+
---
-
---
---
+
---
-
---
-+
| dept | emp_id | emp_name | mgr_id |
+
---
---
+
---
-
---
-+
---
-
---
---
+
---
-
---
-+
| tech | 201 | lisa | 101 |
| tech | 202 | monica | 101 |
| data | 203 | nancy | 201 |
| data | 204 | olivia | 201 |
| data | 205 | penny | 202 |
+
---
---
+
---
-
---
-+
---
-
---
---
+
---
-
---
-+

查詢員工及其主管的對應關係：

SELECT e1.emp_name, e2.emp_name as mgr_name
FROM employee e1, employee e2
WHERE e1.mgr_id = e2.emp_id;

內容解密：

  1. 自連線需要使用別名 e1 和 e2 來區分同一張表格的不同例項。
  2. 連線條件 e1.mgr_id = e2.emp_id 用於匹配員工與其主管。
  3. 結果顯示了每個員工對應的主管姓名。

UNION 操作

UNION 操作用於合併多個 SELECT 陳述式的結果集，並去除重複的行。

SELECT name, origin FROM staff
UNION
SELECT name, country FROM residents;

內容解密：

  1. UNION 操作會自動去除結果集中的重複行。
  2. UNION 操作要求合併的結果集具有相同的欄位數量和資料型別。
  3. UNION 可用於合併不同表格中的資料，例如將員工和客戶的姓名列表合併。

UNION、EXCEPT 和 INTERSECT 操作比較

  • UNION：合併多個結果集並去除重複行。
  • EXCEPT（在 Oracle 中為 MINUS）：傳回第一個結果集中不存在於第二個結果集中的資料。
  • INTERSECT：傳回兩個結果集的交集，即同時存在於兩個結果集中的資料。

內容解密：

  1. UNION 操作用於合併多個查詢結果。
  2. EXCEPT 和 INTERSECT 操作可用於找出不同查詢結果之間的差異和共同點。
  3. 正確使用這些操作可以簡化複雜的查詢邏輯，提高查詢效率。
玄貓 BlackCat

玄貓 BlackCat

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