SQL---ch04 集合运算

1 表的加减法

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

1.1 表的加法--UNION

UNION 等集合运算符通常都会除去重复的记录。 

SELECT product_id, product_name
  FROM Product
 UNION
SELECT product_id, product_name
  FROM Product2;

UNION 与 OR 谓词：对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.。 

-- 使用 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 ：

-- 保留重复行
SELECT product_id, product_name
  FROM Product
 UNION ALL
SELECT product_id, product_name
  FROM Product2;

隐式数据类型转换 ：

 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜 

即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM Product
 UNION
SELECT product_id, product_name,sale_price
  FROM Product2;

1.2 MySQL 8.0 不支持交运算INTERSECT

需要用 inner join 来求得交集

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

1.3 差集,补集与表的减法

 MySQL 8.0 还不支持 EXCEPT 运算，借助NOT IN 谓词, 我们同样可以实现表的减法 

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

EXCEPT 与 NOT 谓词

INTERSECT 与 AND 谓词

1.4 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合.

-- 使用 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)

1.5 交集

两个集合的交可以看作是两个集合的并去掉两个集合的对称差

2 连结(JOIN)

2.1 内连结(INNER JOIN)

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

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

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;

注意：

•  进行连结时需要在 FROM 子句中使用多张表.
• 必须使用 ON 子句来指定连结条件.
• SELECT 子句中的列最好按照 表名.列名 的格式来使用。
• 结合 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 = '衣服' ;

  结合 GROUP BY 子句使用内连结

  SELECT SP.shop_id
        ,SP.shop_name
        ,MAX(P.sale_price) AS max_price
    FROMshopproduct AS SP
   INNER JOINproduct AS P
      ON SP.product_id = P.product_id
   GROUP BY SP.shop_id,SP.shop_name

   自连结(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) ：当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

SELECT *  FROM shopproduct NATURAL JOIN Product

2.2 外连结(OUTER JOIN)

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 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)>

要点：

• 选取出单张表中全部的信息
• 使用 LEFT、RIGHT 来指定主表：LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表. 

 2.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

2.4  ON 子句进阶--非等值连结

使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

非等值自左连结(SELF JOIN) :

希望对 Product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品:

SELECT  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS my_rank
  FROM (--使用自左连结对每种商品找出价格不低于它的商品
        SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM Product AS P1 
          LEFT OUTER JOIN Product AS P2 
            ON P1.sale_price <= P2.sale_price 
        ) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY my_rank; 

2.5 交叉连结—— CROSS JOIN(笛卡尔积)

- 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;