四、集合运算-表的加减法和join等

1. 表的加减法

1.1 表的加法

首先，创建两个新表方便讲解下面的例子

product表

product2表

我们这里说的表的加法-UNION其实就是数学中的求并集运算，同时会对查询的结果进行去重。

【例子】查询product表加上product2表的product_id,product_name

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

上面的例子中，把两个表中重复的数据行进行了合并，当不需要合并的时候，则使用UNION ALL关键字就可以实现。

【例子】保留重复行

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

1.2 表的减法

使用集合A减去另一个集合B的时候，其实就相当于将集合A中同时也在集合B中的元素减掉。

MySQL8.0还不支持EXCEPT关键字，所以用NOT IN谓词来代替EXCEPT实现相同的功能。

【例子】查询product表减去product2表的product_id,product_name

SELECT product_id,product_name
FROM product
WHERE product_id NOT IN 
(SELECT product_id 
FROM product2)

2. 连结

连结-JOIN就是使用某种关联条件，将其他表中的列添加过来，进行添加列的集合运算。

2.1 内连结

内连结需要使用INNER关键词

FROM <table_name_1> INNER JOIN <table_name_2> ON <conditions>

• 使用内连结获取两个表中的信息

通过上面的学习，我们发现product表和shop_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 shop_product AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id; # 连结两个表的公共字段

• 结合 WHERE 子句使用内连结

和正常where使用情况相同

【例子】找出每个商店里的衣服类商品的名称及价格等信息

SELECT  SP.shop_id,SP.shop_name,SP.product_id 
       ,P.product_name, P.product_type, P.purchase_price
  FROM shop_product  AS SP 
 INNER JOIN product AS P 
    ON SP.product_id = P.product_id
 WHERE P.product_type = '衣服';
# 这里没有使用子查询，使用子查询让代码更改通俗易懂

• 结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。最简单的情形, 是在内连结之前就使用 GROUP BY 子句。但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。

【例子】每个商店中, 售价最高的商品的售价分别是多少?

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

2.2 外连接

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

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.

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

# 左连结     
FROM <table_name_1> LEFT  OUTER JOIN <table_name_2> ON <conditions>
# 右连结     
FROM <table_name_1> RIGHT OUTER JOIN <table_name_2> ON <conditions>
# 全外连结
FROM <table_name_1> FULL  OUTER JOIN <table_name_2> ON <conditions>

由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都能得到相同的结果.所以以下内容都是基于左连结。

• 使用左连结获取两个表的信息

【例子】查找每个商店的商店编号, 商店名称, 商品编号, 商品名称, 商品类别, 商品售价,商品数量信息.

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM product AS P
  LEFT OUTER JOIN shop_product AS SP
    ON SP.product_id = P.product_id;

可以通过比较内连结和左连结的截图看出，左连结能够统计出在每个商店都没货的商品信息，由此可以知道外连结可以选取出表中的全部信息。

• 结合WHERE子句使用左连结

【例子】从shop_product表和product表中找出那些在某个商店库存少于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 shop_product AS SP
    ON SP.product_id = P.product_id
 WHERE quantity< 50 
 OR quantity IS NULL # 外连结使用where时，谓词也能使用

• 全外连结

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

2.3 多表连结

创建Inventoryproduct表来进行多表连结的例子。

Inventoryproduct表

• 多表内连结

【例子】找出每个商店都有那些商品, 每种商品的库存总量分别是多少.

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM shop_product 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 shop_product AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id

外连结一般能比内连结有更多的行, 从而能够比内连结给出更多关于主表的信息, 多表连结的时候使用外连结也有同样的作用.