SQL进阶教程1-5 外连接的用法之 全外连接、使用外连接求差集、求异或集、求交集、求集合的商

 

一、全外连接

　　标准SQL里定义了外连接的三种类型：

　　1.左外连接 LEFT OUTER JOIN；

　　2.右外连接 RIGHT OUTER JOIN；

　　3.全外连接 FULL OUTER JOIN；

　　

　　左外连接和右外连接的区别是：用作主表的表写在运算度左边时用左外连接，写在运算符右边时用作右外连接。

　　全外连接，全外连接相当于求两个集合的并集，内连接相当于求两个集合的交集：

　　classs_a  

　　

　　classs_b

　　

　　全外连接是能够从这样的两张内容不一致的表里，没有遗漏的获取全部信息的方法。

　　

对于支持全外连接的数据库：

SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
FULL JOIN classs_b b ON a.id = b.id;

　　COALESCRE是SQL的标准函数，可以接受多个参数，功能是返回第一个非NULL的参数。

　　如果所用的数据库不支持全外连接，可以分别进行左外连接和右外连接，再把两个结果UNION合并起来，就能够达到全外连接的效果。

 

SELECT * FROM (
#左外连接
SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
LEFT JOIN classs_b b ON a.id = b.id

UNION
#右外连接
SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
RIGHT JOIN classs_b b ON a.id = b.id
) c

　　

 

二、用外连接进行集合运算 （B-A  A-B）

#A-B
SELECT a.name
FROM classs_a a 
LEFT JOIN classs_b b ON a.id = b.id
WHERE b.id IS NULL;


#B-A
SELECT b.`name`
FROM classs_a a
RIGHT JOIN classs_b b ON a.id = b.id
WHERE a.id IS NULL;

　　用外连接解决解决这个问题不太符合外连接原本的设计目的，但是对于不支持差集运算的数据库来说，这也可以作为NOT IN和NOT EXISTS之外的另外一种解法。而且，它可能是差集运算中效率最高的。

 

三、 用外连接求异或集

　　首先了解下什么是异或运算：0^0=0；   0^1=1；   1^0=1；   1^1=0；即参加运算的两个对象，如果两个相应位为“异”（值不同），则该位结果为1，否则为0.

　　两个集合的异或运算，蓝色的部分：

     

 

　　那么如何求两个集合的异或集，SQL没有定义求异或集的运算符，如果用集合运算符，可以有两种方法：

　　1.（A UNION B）EXCEPT (A INTERSECT B);

　　2. (A EXCEPT B) UNION (B EXCEPT A);

　　在支持INTERSECT   EXCEPT关键字的数据库中，INTERSECT------求交集， EXCEPT----求差集

 　　这两种方法都比较麻烦，性能开销也会增大，因为使用了多个视图。可以利用全外连接求异或集：

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;

对于MySQL这种不支持 FULL JOIN的数据库，可以如下实现：

　　

SELECT COALESCE(a_name,b_name) AS name 
FROM (
#左外连接
SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
LEFT JOIN classs_b b ON a.id = b.id

UNION
#右外连接
SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
RIGHT JOIN classs_b b ON a.id = b.id
) c ---求全外连接
WHERE a_name IS NULL OR b_name IS NULL

 四、求交集

 同理，求交集的方法：

SELECT COALESCE(a_name,b_name) AS name 
FROM (
#左外连接
SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
LEFT JOIN classs_b b ON a.id = b.id

UNION
#右外连接
SELECT COALESCE(a.id,b.id) AS id,
                a.name AS a_name,
                b.name AS b_name
FROM classs_a a
RIGHT JOIN classs_b b ON a.id = b.id
) c
WHERE a_name IS NOT NULL AND b_name IS NOT NULL

另外一种更为简洁的求交集的方法：

SELECT id, name, COUNT(*) 
FROM (
　　SELECT id, name
　　FROM classs_a
　　UNION ALL 
　　SELECT id, name
　　FROM classs_b
) a
GROUP BY id, name
HAVING COUNT(*)>1

 

五、求商

　　？？？？

　　不明白这个SQL，而且执行也是失败的！！

SELECT DISTINCT shop
FROM ShopItems SI1
WHERE NOT EXISTS(SELECT I.item
　　　　　　　　　　FROM Items I 
　　　　　　　　　　LEFT OUTER JOIN ShopItems SI2 ON I.item = SI2.item AND SI1.shop = SI2.shop
　　　　　　　　　　WHERE SI2.item IS NULL) ;