注意事项 ④ —— UNION 和 INTERSECT 都具有幂等性,而 EXCEPT 不具有
(1)应用 - 删除重复行 -- 方法一 : 通过 集合运算符 EXCEPT 求补集 DELETE FROM Products WHERE rowid IN ( SELECT rowid -- 全部rowid FROM Products EXCEPT -- 减去 SELECT MAX(rowid) -- 要留下的rowid FROM Products GROUP BY name, price ); -- 方法二 : 或者省略集合运算符 EXCEPT ,直接通过 NOT IN 求补集 DELETE FROM Products WHERE rowid NOT IN ( SELECT MAX(rowid) FROM Products GROUP BY name, price ); 2、联结(以列为单位)联结其实属于 表的乘法(笛卡尔积)。
(1)内联结(INNER JOIN)它是应用最广泛的联结。
例子:
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id; (2)外联结 (OUTER JOIN)外联结分:
LEFT OUTER JOIN —— 简写 LEFT JOIN
RIGHT OUTER JOIN —— 简写 RIGHT JOIN
外联结指定主表的关键字是 LEFT 和 RIGHT。最终的结果中会包含主表的所有数据。
平时还是习惯用左联结多一些。左联结有一个优势:一般情况下表头都出现在左边(笔者没遇见过表头出现在右边的情况)。使用左边的表作为主表的话,SQL 就能和执行结果在格式上保持一致。这样一来,在看到 SQL 语句时,我们很容易就能想象出执行结果的格式。
(3)交叉联结(CROSS JOIN)例子:
SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1 CROSS JOIN Products P2; -- 旧写法 SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2;进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句,这是因为交叉联结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表行数的乘积(笛卡儿积)。
交叉联结在实际业务中几乎并不会使用,那为什么还要在这里进行介绍呢?这是因为交叉联结是所有联结
运算的基础。内联结是交叉联结的一部分,“内”也可以理解为“包含在交叉联结结果中的部分”。相反,外联结的“外”可以理解为“交叉联结结果之外的部分”。
全外联结 = 左外联结 UNION 右外联结
全外联结是能够从这样两张内容不一致的表里,没有遗漏地获取全部信息的方法,所以也可以理解成“把两张表都当作主表来使用”的连接。
-- 全外联结 SELECT COALESCE(A.id, B.id) AS id, A.name AS A_name, B.name AS B_name FROM Class_A A FULL OUTER JOIN Class_B B ON A.id = B.id; -- 数据库不支持全外联结时的替代方案 SELECT A.id AS id, A.name, B.name FROM Class_A A LEFT OUTER JOIN Class_B B ON A.id = B.id UNION SELECT B.id AS id, A.name, B.name FROM Class_A A RIGHT OUTER JOIN Class_B B ON A.id = B.id;拓展:A 和 B 的异或
一种是 (A UNION B) EXCEPT (A INTERSECT B),另一种是 (A EXCEPT B) UNION (B EXCEPT A)。
两种方法都比较麻烦,性能开销也大。建议用 FULL OUTER JOIN 来做:
SELECT COALESCE(A.id, B.id) AS id, COALESCE(A.name , B.name ) AS name FROM Class_A A FULL OUTER JOIN Class_B B ON A.id = B.id WHERE A.name IS NULL OR B.name IS NULL; (5)多表联结 -- …… FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id INNER JOIN InventoryProduct AS IP ON SP.product_id = IP.product_id现在是 3 张表,即使把联结的表增加到 4 张、5 张以上也是完全相同的写法。
3、集合的除法截至目前并没有 DBMS 实现集合的除法。
因此,必须自己实现。方法比较多,其中具有代表性的:
嵌套使用 NOT EXISTS。
使用 HAVING 子句转换成一对一关系。
把除法变成减法。
4、进阶 - 自连接 与 非等值连接 (1)自连接针对相同的表进行的连接被称为“自连接”(self join)。
原书 《SQL 基础教程》里都叫 xx 联结,到《SQL 进阶教程》又都变成了 xx 连接。
可见 联结 和 连接 可以通用。 本文又跟着使用混乱,请谅解。
应用1:可重排列、去重排列、组合假如有 Products 表:
name(商品名称) price(价格) 苹果 50 橘子 100 香蕉 801、可重排列
SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2;结果:
name_1 name_2
苹果 苹果
苹果 橘子
苹果 香蕉
橘子 苹果
橘子 橘子
橘子 香蕉
香蕉 苹果
香蕉 橘子
香蕉 香蕉
2、去重排列(考虑顺序,即有序对)
SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2 WHERE P1.name <> P2.name;结果:
name_1 name_2
苹果 橘子
苹果 香蕉
橘子 苹果
橘子 香蕉
香蕉 苹果
香蕉 橘子
3、去重排列(不考虑顺序,即无序对)
SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2 WHERE P1.name > P2.name;结果:
name_1 name_2
苹果 橘子
香蕉 橘子
香蕉 苹果