SQL笔记| 自连接的用法

从物理到逻辑的跳跃

自连接（self join）是使用SQL进行高级数据处理时常用的技术，但与通常的连接相比，其处理过程让人很难理解，原因在于人们经常不知道该如何解释针对同一张表的连接条件。实际上，自连接与普通的连接没有什么不同。关键在“物理”和“逻辑”这两个层面之间跳跃。
SQL的连接运算根据其特征不同，有着不同的名称，如内连接、外连接和交叉连接等。针对相同的表进行的连接被称为“自连接”。

可重排列、排列、组合

用SQL生成有序对非常简单。像下面这样通过交叉连接生成笛卡尔积（直积），就可以得到有序对。

--用于获取可重排列的SQL语句
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;

不过，我们最好避免这种写法，因为可能会出现原本想执行有连接条件的内连接，却因为忘了写连接条件，所以最终按交叉连接执行的危险。交叉连接的消耗极高，一不小心就会浪费服务器资源，导致处理延迟。

--用于获取排列的SQL语句
SELECT p1.name AS name_1,p2.name as name_2
	from Products P1 INNER JOIN Products P2 ON p1.name <> p2.name;
--用于获取组合的SQL语句
SELECT p1.name AS name_1,p2.name as name_2
	from Products P1 INNER JOIN Products P2 ON p1.name > p2.name;

删除重复行

在关系数据库的世界里，重复行和NULL一样，都不受欢迎。下面SQL语句，我们使用Oracle数据库的rowid处理。

--用于删除重复行的SQL语句（1）：使用极值函数
DELETE FROM Products P1 
 WHERE rowid < (SELECT MAX(P2.rowid) FROM Products P2 where p1.name=p2.name and p1.price=p2.price );

无论表还是视图，本质上都是集合——集合是SQL唯一能处理的数据结构。

--用于删除重复行的SQL语句（2）：使用非等值连接
DELETE FROM Products P1 
 WHERE EXISTS (SELECT 1 FROM Products P2 where p1.name=p2.name and p1.price=p2.price and p1.rowid < p2.rowid );

查找局部不一致的列

--用于查找是同一家人但住址不同的记录的SQL语句
SELECT DISTINCT A1.name,A1.address 
 FROM Addresses A1 INNER JOIN Addresses A2 ON A1.family_id=A2.family_id AND A1.address <> A2.address ;