4.1 表的加减法
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。
在数据库中, 所有的表--以及查询结果--都可以视为集合, 因此也可以把表视为集合进行上述集合运算, 在很多时候, 这种抽象非常有助于对复杂查询问题给出一个可行的思路。
4.1.2 表的加法—UNION
select product_id, product_name from product
union
select product_id, product_name from product2;
UNION 等集合运算符通常都会除去重复的记录。
练习题：
select product_id, product_name, product_type, sale_price, purchase_price from product
where sale_price<800
union
select product_id, product_name, product_type, sale_price, purchase_price from product
where sale_price>1.5*purchase_price;
对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但要将两个不同表的结果合并在一起, 需要使用UNION。
练习题：
--使用union
select product_id, product_name, product_type, sale_price, purchase_price from product
where sale_price<1.5*purchase_price
union
select product_id, product_name, product_type, sale_price, purchase_price from product
where purchase_price is null;
--使用or谓词
select product_id, product_name, product_type, sale_price, purchase_price from product
where sale_price<1.5*purchase_price
or purchase_price is null;
包含重复行的集合运算UNION ALL
有时候需要需要不去重的并集, 在 UNION 后面添加 ALL 关键字就可以在结果中保留重复行。
练习题：
select * from product
where sale_price<1.5*purchase_price
union all
select * from product
where sale_price<1000;
拓展阅读—bag 模型与 set 模型：Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素。由于 bag 允许元素重复出现, 对于两个 bag, 他们的并运算会按照: 1.该元素是否至少在一个 bag 里出现过, 2.该元素在两个 bag 中的最大出现次数 这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}。
隐式类型转换
通常来说, 把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型。
select product_id, product_name, '1' from product
union
select product_id, product_name, sale_price from product2;
4.1.3 MySQL 8.0不支持交运算—INTERSECT
拓展阅读-bag 的交运算：对于两个 bag, 他们的交运算会按照: 1.该元素是否同时属于两个 bag, 2.该元素在两个 bag 中的最小出现次数这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的交运算结果就等于 {1,1,2}。
4.1.4 差集，补集与表的减法
MySQL 8.0 不支持EXCEPT运算，可以通过NOT IN 谓词实现表的减法
练习题：(第二题出的不好，强行用not in）
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, 他们的差运算会按照：
1.该元素是否属于作为被减数的 bag,
2.该元素在两个 bag 中的出现次数
INTERSECT与AND谓词练习题
select * from product
where sale_price<1500 and sale_price>1.5*purchase_price;
4.1.5 对称差
两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。
练习题：
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);
借助并集和差集迂回实现交集运算——两个集合的交可以看作是两个集合的并去掉两个集合的对称差。
4.2 连接（JOIN)
连结(JOIN)是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算。可以说，连结是 SQL 查询的核心操作，掌握了连结， 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。相比子查询，连结更适合从多张表获取信息。
4.2.1 内连结（INNER JOIN)
内连结语法格式为：
from <table 1> inner join <table 2> on <condition(s)>;
INNER关键词表示使用了内连结。
（在思路上, 关联子查询更像是 excel中的vlookup 函数: 以表 A 为主表, 然后根据表 A 的关联列的每一行的取值,逐个到表 B 中的关联列中去查找取值相等的行. 当数据量较少时, 这种方式并不会有什么性能问题, 但数据量较大时, 这种方式将会导致较大的计算开销: 对于外部查询返回的每一行数据, 都会向内部的子查询传递一个关联列的值, 然后内部子查询根据传入的值执行一次查询然后返回它的查询结果. 这就使得, 例如外部主查询的返回结果有一万行, 那么子查询就会执行一万次, 这将会带来非常恐怖的时间消耗。）
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 子句来指定连结条件。（ON 子句是专门用来指定连结条件的）
要点三: SELECT 子句中的列最好按照 表**名**.列名 的格式来使用。
4.2.1.2 结合 WHERE 子句使用内连结
如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。
增加 WHERE 子句的方式有好几种：
第一种、把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件。
select * from
(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) as step
where shop_name='东京' and product_type='衣服';
第二种、标准写法，WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN ... ON 得到一个新表后, 才会执行 WHERE 子句。
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 shop_name='东京' and product_type='衣服';
上述查询的执行顺序: FROM 子句->WHERE 子句->SELECT 子句。两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列。
第三种、筛选语句写入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 JOINproduct AS P ON (SP.product_id = P.product_id
AND SP.shop_name = '东京'
AND P.product_type = '衣服') ;
先连结再筛选的标准写法的执行顺序是，两张完整的表做了连结之后再做筛选,如果要连结多张表, 或者需要做的筛选比较复杂时, 在结合 WHERE 子句使用内连结的时候, 也可以更改任务顺序, 并采用任务分解的方法，先分别在两个表使用 WHERE 进行筛选，然后把上述两个子查询连结起来。
select sp.shop_id,
sp.shop_name,
sp.product_id,
p.product_type,
p.product_type,
p.sale_price,
sp.quantity
from (select * from shopproduct where shop_name='东京') as sp
inner join (select * from product where product_type='衣服') as p
on sp.product_id=p.product_id;
先分别在两张表里做筛选, 把复杂的筛选条件按表分拆, 然后把筛选结果(作为表)连接起来, 避免了写复杂的筛选条件, 因此这种看似复杂的写法, 实际上整体的逻辑反而非常清晰。在写查询的过程中, 首先要按照最便于自己理解的方式来写, 先把问题解决了, 再思考优化的问题。
练习题：
--不使用子查询
select sp.shop_id,
sp.shop_name,
p.product_id,
p.product_name,
p.product_type,
p.purchase_price
from shopproduct as sp
inner join product as p on sp.product_id=p.product_id,
where p.product_type='衣服';
--使用子查询
select sp.shop_id,
sp.shop_name,
p.product_id,
p.product_name,
p.product_type,
p.purchase_price
from shopproduct as sp
inner join (select * from product where product_type='衣服') as p
on sp.product_id=p.product_id;
--不使用子查询
select sp.shop_id,
sp.shop_name,
p.product_id,
p.product_name,
p.product_type,
p.purchase_price
from shopproduct as sp
inner join product as p on sp.product_id=p.product_id
where sp.shop_name='东京' and p.sale_price<2000;
--使用子查询
select sp.shop_id,
sp.shop_name,
p.product_id,
p.product_name,
p.product_type,
p.purchase_price
from shopproduct as sp
inner join (select * from product where sale_price<2000) as p on sp.product_id=p.product_id
where sp.shop_name='东京';
4.2.1.3结合 GROUP BY 子句使用内连结
根据分组列位于哪个表区别对待：最简单的情形, 是在内连结之前就使用 GROUP BY 子句；但当分组列和被聚合的列不在同一张表，且二者都未被用于连结两张表，则只能先连结，再聚合。
select sp.shop_id,
sp.shop_name,
max(p.sale_price) as max_price
from shopproduct as sp
inner join product as p on sp.product_id=p.product_id
group by sp.shop_id, sp._shop_name;
4.2.1.4 自连结（SELF JOIN）
一张表也可以与自身作连结， 这种连接称之为自连结，自连结可以是外连结也可以是内连结。
4.2.1.5 内连结与关联子查询
4.2.1.6 自然连结（NATURAL JOIN）
自然连结是内连结的一种特例--当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。
select * from shopproduct natural join product;
上述查询得到的结果, 会把两个表的公共列(可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。
练习题：
select sp.shop_id,
sp.shop_name,
sp.product_id,
sp.quantity,
p.product_id,
p.product_name,
p.product_type,
p.sale_price,
p.purchase_price,
p.regist_date
from shopproduct
inner join product as p on sp.product_id=p.product_id;
在进行自然连结时, 实际上是在逐字段进行等值连结。两个缺失值用等号进行比较, 结果不为真。而连结只会返回对连结条件返回为真的那些行。
4.2.1.7 使用连结求交集
select p1.* from product as p1
inner join product2 as p2 on p1.product_id=p2.product_id;
4.2.2 外连结(OUTER JOIN)
内连结会丢弃两张表中不满足 ON 条件的行，和内连结相对的就是外连结。外连结会根据外连结的种类有选择地保留无法匹配到的行。按照保留的行位于哪张表，外连结有三种形式: 左连结, 右连结和全外连结。
左连结会保存左表中无法按照 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)>
4.2.2.1 左连结与右连结
由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别。
4.2.2.2 使用左连结从两个表获取信息
select sp._shop_id,
sp.shop_name,
sp.product_id
p.product_name,
p.sale_price
from product as p
left join shopproduct as sp on sp.product_id=p.product_id;
外连结的要点：1、选取出单张表中全部的信息（能够得到固定行数的结果）
2、使用 LEFT、RIGHT 来指定主表（使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表）
4.2.2.3 结合WHERE子句使用左连结
select p.product_id,
p.product_name,
p.sale_price,
sp.shop_id,
sp.shop_name,
sp.quantity
from product as p
left join shopproduct on p.product_id=sp.product_id
where sp.quantity<50 or sp.quantity is null;
--改为子查询
select p.product_id,
p.product_name,
p.sale_price,
sp.shop_id,
sp.shop_name,
sp.quantity
from product as p
left join (select * from shopproduct where quantity<50) as sp on p.product_id=sp.product_id;
4.2.2.4 在MySQL中实现全外连结
MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。
4.2.3多表连结
4.2.3.1 多表进行内连结
与两表连结对比，增加第三张表同样使用inner join <tb3> on <condition(s)> 语句进行添加。
4.2.3.2 多表进行外连结
和内连结基本相同，多次使用left/right outer join 语句
4.2.4 ON子句进阶—非等值连结
包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。
4.2.4.1非等值左连结（SELF JOIN)
考虑相等判断可能导致的计算错误，可以利用ID的有序性正确实现查询。
4.2.5 交叉连结—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;
进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。
内连结是交叉连结的一部分,“内”也可以理解为“包含在交叉连结结果中的部分”.相反,外连结的“外”可以理解为“交叉连结结果之外的部分”。在对笛卡儿积进行适当的限制之后, 也就得到了内连结和外连结。
4.2.6 连结的特定语法和过时语法
练习题
4.3
select sp.shop_id,
sp.shop_name,
max(p.sale_price),
p.product_type,
p.product_name
from shop_product as sp
inner join (select product_type, sale_price,product_id,product_name from product) as p on sp.product_id=p.product_id
group by p.product_type
;
4.4
--内连接
select p1.product_name,
p1.product_type,
p1.sale_price
from product as p1
inner join product as p2 on p1.product_id=p2.product_id
where p1.sale_price > p2.sale_price or (p1.sale_price=p2.sale_price and p1.product_id=p2.product_id)
group by p1.product_type;
--子查询
select p1.product_name,
p1.product_type,
p1.sale_price
from product as p1
inner join (select product_id, max(sale_price) from product group by product_type) as p2 on p1.product_id=p2.product_id;
4.5
select product_id,
product_name,
sale_price,
sum(p2_saleprice) as cum_price
from (select p1.product_id, p1.product_name, p1.sale_price,
p2.sale_price as p2_saleprice from product as p1
left join product as p2 on p1.product_id=p2.product_id) as p2
group by product_id,product_name,sale_price
order by sale_price;
