跳轉到內容

結構化查詢語言/SELECT：集合運算

Add links
來自華夏公益教科書，開放的書籍，開放的世界
SELECT：預定義函式 結構化查詢語言
SELECT：集合運算 		SELECT：Case 表示式



表、檢視和 SELECT 命令的結果在某種程度上類似於集合論中的集合。在這種比較中，集合的元素對應於表、檢視和 SELECT 結果的行。集合論和 SQL 結構之間的區別在於

  • 集合論中的集合不允許重複，而 SQL 允許重複。（即使同一個表的不同行也可能相同，因為沒有義務使用主鍵的概念。）在下面，當我們談論 SQL 中允許重複的集合時，我們使用術語多重集。
  • 集合論中的集合和多重集是無序的。但是對於 SELECT 命令的結果，我們可以透過可選的 ORDER BY 子句強制排序。

集合論和 SQL 之間的比較更進一步。在 SQL 中，我們有操作在多重集上執行，就集合論意義而言：SQL 操作 UNION、INTERSECT 和 EXCEPT（有些人稱之為 MINUS）處理由不同 SELECT 命令生成的中間多重集。這些操作要求多重集型別相同。這主要意味著它們必須具有相同的列數。此外，它們的資料型別也應該相關，但這不是強制性的。如果它們不同，DBMS 將嘗試將它們強制轉換為公共資料型別 - 如果可能。

UNION

[編輯 | 編輯原始碼]

UNION 操作將多個 SELECT 命令的結果合併在一起。UNION 的結果包含在第一個或第二個中間結果中的值。

-- Please consider that this is only one command (only ONE semicolon at the very end)
SELECT firstname  -- first SELECT command
FROM   person
  UNION           -- push both intermediate results together to one result
SELECT lastname   -- second SELECT command
FROM   person;
兩個中間結果的UNION

這是一個單個 SQL 命令。它由兩個 SELECT 和一個 UNION 操作組成。SELECT 首先被評估。之後，它們的結果被合併成一個單一結果。在我們的示例中，結果包含一個單獨列中的所有姓氏和名字（我們的示例在實踐中可能幫助不大，它只是 UNION 的演示）。

DISTINCT / ALL
如果我們仔細檢查結果，我們會注意到它只包含 17 個值。person 表包含十行，因此我們可能期望結果中包含二十個值。如果我們執行 'SELECT firstname ...' 和 'SELECT lastname ...' 作為單獨的命令，不使用 UNION，我們將為兩個命令都收到十個值。三個缺失值的解釋是 UNION 命令。預設情況下，UNION 會刪除重複項。因此，一些中間值被跳過。如果我們想獲得重複值，我們必須修改 UNION 操作。它的行為可以透過兩個關鍵字 DISTINCT 或 ALL 之一進行更改。DISTINCT 是預設值，它會刪除重複值，就像我們之前看到的那樣。ALL 將保留所有值，包括重複值。

-- remove (that's the default) or keep duplicates
SELECT ...
  UNION [DISTINCT | ALL]
SELECT ...
[ -- it is possible to 'UNION' more than 2 intermediate results
  UNION [DISTINCT | ALL]
SELECT ...
];

提示：Oracle 使用者：Oracle 不接受預設的 DISTINCT 關鍵字。請省略它。

通用提示
在大多數情況下，UNION 將不同表上的 SELECT 命令或同一個表的不同列上的 SELECT 命令組合在一起。同一個表同一列上的 SELECT 命令通常會將 WHERE 子句與布林邏輯結合使用。

-- A very unusual example. People apply such queries on the same table only in combination with very complex WHERE conditions.
-- This example would normally be expressed with a phrasing similar to: WHERE lastname IN ('de Winter', 'Goldstein');
SELECT *
FROM   person
WHERE  lastname = 'de Winter'
  UNION ALL
SELECT *
FROM   person
WHERE  lastname = 'Goldstein';

INTERSECT

[編輯 | 編輯原始碼]
INTERSECT

INTERSECT 操作將評估為同時出現在第一個和第二個中間結果中的值。





-- As in our example database, there is no example for the INTERSECT we insert a new person.
-- This person has the same last name 'Victor' as the first name of another person.
INSERT INTO person VALUES (21, 'Paul', 'Victor', DATE'1966-04-02', 'Washington', '078-05-1121', 66);
COMMIT;
-- All firstnames which are used as lastname.
SELECT firstname  -- first SELECT command
FROM   person
  INTERSECT       -- looking for common values
SELECT lastname   -- second SELECT command
FROM   person;

提示：MySQL 使用者：MySQL（5.5）不支援 INTERSECT 操作。但由於它不是一個基本操作，所以有一些解決方法。

EXCEPT

[編輯 | 編輯原始碼]
EXCEPT

EXCEPT 操作將評估為出現在第一個中間結果中但沒有出現在第二個中間結果中的值。





-- All firstname except for 'Victor', because there is a lastname with this value.
SELECT firstname  -- first SELECT command
FROM   person
  EXCEPT          -- are there values in the result of the first SELECT but not of second?
SELECT lastname   -- second SELECT command
FROM   person;

提示：MySQL 使用者：MySQL（5.5）不支援 EXCEPT 操作。但由於它不是一個基本操作，所以有一些解決方法。
提示：Oracle 使用者：Oracle 使用 MINUS 關鍵字代替 EXCEPT。

-- Clean up the example database
DELETE FROM person WHERE id > 10;
COMMIT;

Order By

[編輯 | 編輯原始碼]

我們可以將集合運算與 SELECT 命令的所有其他元素結合使用，特別是與 ORDER BY 和 GROUP BY 結合使用。但這可能會導致一些不確定性。因此，我們想在下面解釋一些細節。

SELECT firstname  -- first SELECT command
FROM   person
  UNION           -- push both intermediate results together to one result
SELECT lastname   -- second SELECT command
FROM   person
ORDER BY firstname;

ORDER BY 屬於命令的哪一部分？第一個 SELECT、第二個 SELECT 還是 UNION 的結果？SQL 規則規定集合運算在 ORDER BY 子句之前執行（像往常一樣，圓括號可以改變執行順序）。因此 ORDER BY 對最終結果進行排序，而不是對任何中間結果進行排序。

我們重新排列示例，希望事情變得清楚。

-- Equivalent semantic
SELECT * FROM
  (SELECT firstname  -- first SELECT command
   FROM   person
     UNION           -- push both intermediate (unnamed) results together to the next intermediate result 't'
   SELECT lastname   -- second SELECT command
   FROM   person
  ) t                -- 't' is the name for the intermediate result generated by UNION
ORDER BY t.firstname;

首先，兩個 SELECT 被評估，然後是 UNION。這個中間結果被稱為 't'。't' 被排序。


通常，人們希望第一個 SELECT 中的行獨立於第二個 SELECT 中的行進行排序。我們可以透過在每個 SELECT 語句的結果中新增一個虛擬列，並在 ORDER BY 中使用虛擬列來實現這一點。

SELECT '1' as dummy, firstname
FROM   person
  UNION
SELECT '2', lastname
FROM   person
ORDER BY dummy, firstname;

Group By

[編輯 | 編輯原始碼]

使用 GROUP BY 子句，事情比 ORDER BY 稍微複雜一些。GROUP BY 指的是最後一個 SELECT，或者換句話說，指的是它直接級別上的 SELECT。

-- Will not work because the GROUP BY belongs to the second SELECT and not to the UNION!
SELECT firstname
FROM   person
  UNION
SELECT lastname
FROM   person
GROUP BY firstname;
-- 
-- Works, but possibly not what you want to do.
-- The alias name for the (only) column of the UNION is 'firstname'.
SELECT firstname
FROM   person
  UNION
-- We group over the (only) column of the second SELECT, which is 'lastname' and results in 7 values
SELECT lastname
FROM   person
GROUP BY lastname;
--
-- Make things clear: rearrange the query to group over the final result 
SELECT * FROM
  (SELECT firstname  -- columnnames of the first SELECT determins the columnnames of the UNION
   FROM   person
     UNION
   SELECT lastname
   FROM   person
  ) t
GROUP BY t.firstname; -- now we can group over the complete result

練習

[編輯 | 編輯原始碼]

顯示最低、最高和平均體重，a) 1 行的 3 個值，b) 3 行的 1 個值。

點選檢視解決方案
-- 1 row
SELECT min(weight), max(weight), avg(weight)
FROM   person;

-- 3 rows
SELECT min(weight)
FROM   person
  UNION
SELECT max(weight)
FROM   person
  UNION
SELECT avg(weight)
FROM   person;

擴充套件之前的 3 行解決方案，以滿足另外兩個條件：a) 只考慮在舊金山出生的人員，以及
b) 新增一個虛擬列，根據相關數值顯示 'Min'、'Max' 和 'Avg'。

點選檢視解決方案
SELECT 'Min', min(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 'Max', max(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 'Avg', avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco';

擴充套件之前的解決方案，對結果進行排序：最小值在前，平均值在中間，最大值在最後。

點選檢視解決方案
-- 'ugly' solution
SELECT '1 Min' AS note, min(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT '3 Max' AS note, max(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT '2 Avg' AS note, avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
ORDER BY note;

-- 'clean' solution
SELECT 1 AS note, 'Min', min(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 3 AS note, 'Max', max(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 2 AS note, 'Avg', avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
ORDER BY note;

建立體重超過 70 公斤的人員的姓氏列表，以及
所有電子郵件值（每行一個值）。姓氏和電子郵件之間沒有對應關係。
（這個示例在實踐中幫助不大，但很有啟發性。）

點選檢視解決方案
SELECT lastname
FROM   person
WHERE  weight > 70
  UNION
SELECT contact_value
FROM   contact
WHERE  contact_type = 'email';

在前面的示例中，'de Winter' 姓氏只顯示了一次。但有多個體重超過 70 公斤的同姓人。
為什麼？
擴充套件之前的解決方案，以便顯示與標準相符的命中次數一樣多的結果行。

點選檢視解決方案
-- Extend 'UNION' to 'UNION ALL'. The default is 'UNION DISTINCT'
SELECT lastname
FROM   person
WHERE  weight > 70
  UNION ALL
SELECT contact_value
FROM   contact
WHERE  contact_type = 'email';


SELECT：預定義函式 結構化查詢語言
SELECT：集合運算 		SELECT：Case 表示式
檢索自 "https://wikibook.tw/w/index.php?title=Structured_Query_Language/SELECT:_Set_Operations&oldid=3698893"
華夏公益教科書