1.表的加减法

集合在数据库领域表示记录的集合.
具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行.
加法：UNION（并集）

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

UNION 等集合运算符通常都会除去重复的记录.
对于同一张表, 实际上也是可以进行求并集的.

UNION 与 OR 谓词

对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.
而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION.

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

SQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了.

隐式类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

交运算INTERSECT和减法运算符EXCEPT

截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作.
对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现.

--使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品
SELECT * 
  FROM product
 WHERE sale_price > 1.5 * purchase_price 
   AND sale_price < 1500

MySQL 8.0 还不支持表的减法运算符 EXCEPT. 不过, 借助学过的NOT IN 谓词, 我们同样可以实现表的减法.
使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果.

SELECT * 
FROM product
WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

SELECT * 
  FROM product
 WHERE sale_price > 2000 
   AND product_id NOT IN (SELECT product_id 
                            FROM product 
                           WHERE sale_price < 1.3*purchase_price)

类似于UNION ALL, EXCEPT ALL 也是按出现次数进行减法, 也是使用bag模型进行运算.
留意bag模型与set模型的区别：是否允许元素重复导致了 set 和 bag 的并交差等运算都存在一些区别，使用 bag 模型来描述数据库中的表在很多时候更加合适.

对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合.
两个集合的对称差等于A-B并上B-A, 因此在MySQL 8.0 里实践中可以用这个思路来求对称差.
示例

--使用product表和product2表的对称差来查询哪些商品只在其中一张表
-- 使用 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)

▲借助并集和差集迂回实现交集运算 INTERSECT
两个集合的交可以看作是两个集合的并去掉两个集合的对称差.

2.连结（JOIN)

前一节我们学习了 UNION和INTERSECT 等集合运算, 这些集合运算的特征就是以行方向为单位进行操作. 通俗地说, 就是进行这些集合运算时, 会导致记录行数的增减. 使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数.

但这些运算不能改变列的变化

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

可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询.

内连结（INNER JOIN)

示例

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 子句中的列最好按照 表名.列名 的格式来使用
（表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间.如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错.）

▲熟记查询的执行顺序:
FROM 子句->WHERE 子句->SELECT 子句
示例：

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 = '衣服' ;

另外, 先连结再筛选的标准写法的执行顺序是, 两张完整的表做了连结之后再做筛选,如果要连结多张表, 或者需要做的筛选比较复杂时, 在写 SQL 查询时会感觉比较吃力. 在结合 WHERE 子句使用内连结的时候, 我们也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来.

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (-- 子查询 1:从 shopproduct 表筛选出东京商店的信息
        SELECT *
          FROM shopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROM product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

结合 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)

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.
上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。

注意：运动 T 恤的 regist_date 字段为空（NULL)
在进行自然连结时, 来自于 product 和 product2 的运动 T 恤这一行数据在进行比较时, 实际上是在逐字段进行等值连结, 两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行.(NULL只能用IS或者IS NOT比较）

1.png

如果我们这样查询

SELECT * 
  FROM (SELECT product_id, product_name
          FROM product ) AS A 
NATURAL JOIN 
   (SELECT product_id, product_name 
      FROM product2) AS B;

2.png

就会得到运动T恤那一行记录。
只选取了product_id那列，这样实际上是避免了比较NULL（空值），从而返回我们想要的结果

使用连结求交集

使用内连结求 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)

3.png

外连结(OUTER JOIN)

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行.

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.
左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值;
右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值;
全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.

▲shopproduct 和 product 进行内连结时，product 表中有两种商品并未在内连结的结果里, 就是说, 这两种商品并未在任何商店有售(这通常意味着比较重要的业务信息, 例如, 这两种商品在所有商店都处于缺货状态, 需要及时补货).

4.png

我们观察上述结果可以发现, 有两种商品: 高压锅和圆珠笔, 在所有商店都没有销售.
由于我们在 SELECT 子句选择列的显示顺序以及未对结果进行排序的原因, 这个事实需要你仔细地进行观察.
所以可以猜测在数据量很大的时候执行这个操作需要进行排序

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

★结合 WHERE 子句使用左连结

由于外连结的结果很可能与内连结的结果不一样, 会包含那些主表中无法匹配到的行, 并用缺失值填写另一表中的列, 由于这些行的存在, 因此在外连结时使用WHERE子句, 情况会有些不一样.
▲主要还是缺失值（NULL）的问题
示例：
使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果.

5.png

我们可能自然地想到

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,SP.quantity
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
    ON SP.product_id = P.product_id
 WHERE quantity< 50

然而结果是

6.png

在WHERE子句中增加 quantity IS NULL 的条件是一种解决方法
然而在真实的查询环境中, 由于数据量大且数据质量并非如系统说明和我们设想的那样"干净", 我们并不能很容易地意识到缺失值等问题数据的存在，所以还是考虑如何改写我们的查询

联系到我们已经掌握了的SQL查询的执行顺序(FROM->WHERE->SELECT),我们发现, 问题可能出在筛选条件上, 因为在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了.
所以我们可以试着把WHERE子句挪到外连结之前进行: 先写个子查询,用来从shopproduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来.

SELECT P.product_id
      ,P.product_name
      ,P.sale_price
       ,SP.shop_id
      ,SP.shop_name
      ,SP.quantity 
  FROM product AS P
  LEFT OUTER JOIN-- 先筛选quantity<50的商品
   (SELECT *
      FROM shopproduct
     WHERE quantity < 50 ) AS SP
    ON SP.product_id = P.product_id

这样就可以得到我们想要的结果啦

▲MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结.

多表连结

原则上连结表的数量并没有限制.
示例：

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

连结第三张表的时候, 也是通过 ON 子句指定连结条件(这里使用最基础的等号将作为连结条件的 product 表和 shopproduct 表中的商品编号 product _id 连结了起来), 由于 product 表和 shopproduct 表已经进行了连结,因此就无需再对 product 表和Inventoryproduct 表进行连结了(虽然也可以进行连结,但结果并不会发生改变, 因为本质上并没有增加新的限制条件).

即使想要把连结的表增加到 4 张或其以上，使用 INNER JOIN 进行添加的方式也是完全相同的.

类似地，多表也可以进行外连结

ON 子句进阶–非等值连结

实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.
ON子句内不一定必须使用=号
▲####非等值自左连结(SELF JOIN)
示例：希望对 product 表中的商品按照售价赋予排名

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

交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.
交叉连结没有应用到实际业务之中的原因有两个
一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持.

注意连结的特定语法和过时语法

练习题

1.找出 product 和 product2 中售价高于 500 的商品的基本信息.

SELECT *
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;

--注意：创建视图时记得给视图指定别名AS...  否则MySQL会报错
--子查询的结果必须要有一个别名
SELECT *
FROM (SELECT *
      FROM product
      UNION 
      SELECT *
      FROM product2) AS P
WHERE product_id NOT IN (SELECT product_id 
                         FROM product
                         WHERE product_id NOT IN (SELECT product_id FROM product2)
                         UNION
                         SELECT product_id 
                         FROM product2
                         WHERE product_id NOT IN (SELECT product_id FROM product));

3.每类商品中售价最高的商品都在哪些商店有售?

SELECT SP.shop_id,SP.shop_name,product_type,
       MAX(sale_price) AS max_price 
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY P.product_type;

4.分别使用内连结和关联子查询每一类商品中售价最高的商品.

--1.内连结
SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.max_price
FROM product AS P1
INNER JOIN 
     (SELECT product_type,MAX(sale_price) AS max_price 
      FROM product 
      GROUP BY product_type) AS P2 
ON P1.product_type=P2.product_type;
--2.关联子查询
SELECT product_id,product_name,product_type,sale_price,
       (SELECT MAX(sale_price) FROM product AS P2 
        WHERE P1.product_type=P2.product_type 
        GROUP BY product_type) AS max_price
FROM product AS P1