1. 集合論是SQL語言的根基
1.1. UNION
- 1.1.1. SQL-86標準
1.2. NTERSECT和EXCEPT
- 1.2.1. SQL-92標準
1.3. 除法運算（DIVIDE BY）
- 1.3.1. 沒有被標準化
2. 注意事項
2.1. SQL能操作具有重復行的集合，可以通過可選項ALL來支持
-
2.1.1. 不允許重復
-
2.1.1.1. 直接使用UNION或INTERSECT
-
2.1.1.2. 集合運算子為了排除掉重復行，默認地會發生排序
-
-
2.1.2. 允許重復
-
2.1.2.1. 加上可選項ALL
2.1.2.1.1. 不會再排序，所以性能會有提升
2.1.2.1.2. 非常有效的用于優化查詢性能的方法
-
2.1.2.2. UNION ALL
2.1.2.2.1. 不具有冪等性
-
2.2. 集合運算子有優先級
-
2.2.1. INTERSECT比UNION和EXCEPT優先級更高
-
2.2.2. 括號明確地指定運算順序
2.3. 各個DBMS提供商在集合運算的實作程度上參差不齊
-
2.3.1. SQL Server從2005版開始支持INTERSECT和EXCEPT
-
2.3.2. Oracle這樣，實作了EXCEPT功能但卻命名為MINUS的資料庫
-
2.3.3. INTERSECT和EXCEPT不能在MySQL里執行
2.4. 除法運算沒有標準定義
- 2.4.1. 四則運算里的和（UNION）、差（EXCEPT）、積（CROSS JOIN）都被引入了標準SQL
3. 檢查集合相等性
3.1. “相等”指的是行數和列數以及內容都相同
3.2. “是同一個集合”
3.3. 原理1
-
3.3.1. S UNION S = S
-
3.3.2. 冪等性（indempotency）
-
3.3.2.1. 抽象代數里群論等理論中的概念
-
3.3.2.2. 二目運算子對任意S，都有SS = S成立”
-
3.4. 示例1
- 3.4.1.
SELECT COUNT(＊) AS row_cnt
FROM ( SELECT ＊
FROM tbl_A
UNION
SELECT ＊
FROM tbl_B ) TMP;
-
3.4.2. 這個查詢的結果與tbl_A及tbl_B的行數一致，則兩張表是相等的
-
3.4.3. 表tbl_A和表tbl_B的行數是一樣的
- 3.4.3.1. 如果行數不一樣，那就不需要比較其他的了
3.5. 原理2
-
3.5.1. 如果A UNION B = A INTERSECT B，則集合A和集合B相等
-
3.5.2. (A UNION B) EXCEPT (A INTERSECT B)的結果集是不是空集就可以了
-
3.5.3. INTERSECT
- 3.5.3.1. 冪等性（indempotency）
-
3.5.4. EXCEPT不具有冪等性
3.6. 示例2
- 3.6.1. --兩張表相等時回傳“相等”，否則回傳“不相等”
SELECT CASE WHEN COUNT(＊) = 0
THEN ’相等’
ELSE’不相等’END AS result
FROM ((SELECT ＊ FROM tbl_A
UNION
SELECT ＊ FROM tbl_B)
EXCEPT
(SELECT ＊ FROM tbl_A
INTERSECT
SELECT ＊ FROM tbl_B)) TMP;
-
3.6.2. 改進版不需要事先查詢兩張表的行數
-
3.6.3. 需要進行4次排序（3次集合運算加上1次DISTINCT）
- 3.6.3.1. 性能會有所下降
3.7. 示例3
- 3.7.1. --用于比較表與表的diff
(SELECT ＊ FROM tbl_A
EXCEPT
SELECT ＊ FROM tbl_B)
UNION ALL
(SELECT ＊ FROM tbl_B
EXCEPT
SELECT ＊ FROM tbl_A);
4. 用差集實作關系除法運算
4.1. 嵌套使用NOT EXISTS
4.2. 使用HAVING子句轉換成一對一關系
4.3. 把除法變成減法
-
4.3.1. 示例
- 4.3.1.1.
- 4.3.1.2. --用求差集的方法進行關系除法運算（有余數）
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp);
- 4.3.1.3. 關聯子查詢是為了使SQL能夠實作類似面向程序語言中回圈的功能而引入的
5. 尋找相等的子集
5.1. IBM過去研制的第一個關系資料庫實驗系統——System R
-
5.1.1. 用CONTAINS這一謂詞來檢查集合間的包含關系
-
5.1.2. 后來因為性能原因被洗掉掉了，直到現在也沒有恢復
-
5.1.3.
SELECT 'A CONTAINS B'
FROM SupParts
WHERE (SELECT part
FROM SupParts
WHERE sup ='A')
CONTAINS
(SELECT part
FROM SupParts
WHERE sup ='B')
5.2. 示例
- 5.2.1. --生成供應商的全部組合
SELECT SP1.sup AS s1, SP2.sup AS s2
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup ＜ SP2.sup
GROUP BY SP1.sup, SP2.sup;
- 5.2.2.
SELECT SP1.sup AS s1, SP2.sup AS s2
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup ＜ SP2.sup --生成供應商的全部組合
AND SP1.part = SP2.part --條件1：經營同種型別的零件
GROUP BY SP1.sup, SP2.sup
HAVING COUNT(＊) = (SELECT COUNT(＊) --條件2：經營的零件種類數相同
FROM SupParts SP3
WHERE SP3.sup = SP1.sup)
AND COUNT(＊) = (SELECT COUNT(＊)
FROM SupParts SP4
WHERE SP4.sup = SP2.sup);
- 5.2.3. SQL在比較兩個集合時，并不是以行為單位來比較的，而是把集合當作整體來處理的
6. 用于洗掉重復行的高效SQL
6.1. --洗掉重復行：使用關聯子查詢
DELETE FROM Products
WHERE rowid ＜ ( SELECT MAX(P2.rowid)
FROM Products P2
WHERE Products.name = P2. name
AND Products.price = P2.price ) ;
6.2. --用于洗掉重復行的高效SQL陳述句(1)：通過EXCEPT求補集
DELETE FROM Products
WHERE rowid IN ( SELECT rowid --全部rowid
FROM Products
EXCEPT --減去
SELECT MAX(rowid) --要留下的rowid
FROM Products
GROUP BY name, price) ;
6.3. --洗掉重復行的高效SQL陳述句(2)：通過NOT IN求補集
DELETE FROM Products
WHERE rowid NOT IN ( SELECT MAX(rowid)
FROM Products
GROUP BY name, price);
- 6.3.1. 不支持EXCEPT的資料庫也可以使用
6.4. 實作了行ID的資料庫只有Oracle和PostgreSQL
-
6.4.1. PostgreSQL里的相應名字是oid，如果要使用，需要事先在CREATE TABLE的時候指定可選項WITH OIDS
-
6.4.2. 如果其他資料庫想要使用這些SQL，則需要在表中創建類似的具有唯一性的“id”列
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/548862.html
標籤：PostgreSQL