MySQL-04：集合运算

MySQL-04：集合运算

表的加减法

集合运算符UNION, INTERSECT, EXCEPT 可以对检索结果进行并,交和差运算。

表的加法 UNION

UNION 集合运算符可以对检索取并集，且通常都会除去重复的记录。

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

UNION与OR谓词

-- 使用 OR 谓词
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3 
    OR sale_price / purchase_price IS NULL;

等价于

-- 使用 UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3
 
 UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price IS NULL;

包含重复行的集合运算 UNION ALL

实践中有时候需要需要不去重的并集, 这时只需使用UNION ALL。

隐式数据类型转换

一般，UNION连接两个类型完全一致，属性完全相同的列，但有时对于类型不完全相同的情况，UNION也可以连接两个部分。这时利用的就是隐式数据类型转换。

集合交运算INTERSECT——MySQL不支持

截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。需要用 INNER JOIN 来求得交集

SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

差集、补集与表的减法

当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素， 采取直接忽略的策略，因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

EXCEPT运算——MySQL不支持

可以通过NOT IN实现表的减法。

-- 使用 NOT IN 子句的实现方法
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

EXCEPT与NOT谓词

NOT IN 等价于EXCEPT

INTERSECT与AND谓词

AND 等价于INTERSECT

对称差

对称差：两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。
从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差。

-- 使用 NOT IN 实现两个表的差集
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

连结JOIN

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算。

内连结INNER JOIN

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

使用内连结从两个表获取信息

按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 ShopProduct.product_id=Product.product_id, 就得到了如下的查询语句:

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id;

注意要点：

1. 内连结时，需要在FROM子句中使用多张表。
2. 使用ON作为连结条件。
3. SELECT子句中最好按照：表名.列名 的格式来使用。

结合WHERE子句使用内连结

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

上述查询的执行顺序:
FROM 子句->WHERE 子句->SELECT 子句

结合 GROUP BY 子句使用内连结

最简单的情形, 是在内连结之前就使用 GROUP BY 子句.

但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。

自连结SELF JOIN

自连结：一张表也可以与自身作连结, 这种连接称之为自连结

内连结与关联子查询

找出每个商品种类当中售价高于该类商品的平均售价的商品

SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.avg_price
  FROM Product AS P1
 INNER JOIN 
   (SELECT product_type,AVG(sale_price) AS avg_price 
      FROM Product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price > P2.avg_price;

自然连结NATURAL JOIN

自然连结NATURAL JOIN其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

SELECT *  FROM shopproduct NATURAL JOIN Product

使用连结求交集

利用连结实现Product和Product2表的交集。

SELECT P1.*
  FROM Product AS P1
 INNER JOIN Product2 AS P2
    ON (P1.product_id  = P2.product_id
   AND P1.product_name = P2.product_name
   AND P1.product_type = P2.product_type
   AND P1.sale_price   = P2.sale_price
   AND P1.regist_date  = P2.regist_date)

外连结

外连结会根据外连结的种类有选择地保留无法匹配到的行。（相对地，内连结会丢弃两张表中不满足 ON 条件的行）
外连结有三种形式:

1. 左连结： 左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值
2. 右连结： 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值
3. 全外连结：全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

使用要点：

1. 选取出单张表中全部的信息
2. 使用 LEFT、RIGHT 来指定主表

多表连结

JOIN同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。

多表内连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  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
 WHERE IP.inventory_id = 'P001';

多表外连结

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.inventory_quantity
  FROM Product AS P
  LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id

非等值连结

比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

交叉连结（笛卡尔积）CROSS JOIN

在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积。两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合。

-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM ShopProduct AS SP
 CROSS JOIN Product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM ShopProduct AS SP , Product AS P;