《SQL基础教程》+ 《SQL进阶教程》 学习笔记 (9)

注意事项 ④ —— 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 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表行数的乘积（笛卡儿积）。

交叉联结在实际业务中几乎并不会使用，那为什么还要在这里进行介绍呢？这是因为交叉联结是所有联结
运算的基础。内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。

（4）全外联结（FULL OUTER JOIN）

全外联结 = 左外联结 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 香蕉    80

1、可重排列

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

苹果 橘子
香蕉 橘子
香蕉 苹果