SQL基础--表的加减法和JOIN

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

一、表的加减法

1、集合运算

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

1.1 UNION-表的加法

语法： 

SELECT product_id, product_name

        FROM product

        UNION

SELECT product_id, product_name 

        FROM product2;

 UNION 等集合运算符通常都会除去重复的记录.如果希望不去重，使用UNION ALL即可

1.2 UNION与OR谓词

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

 1.3隐式类型转换

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

 2、EXCEPT-差集、补集与表的减法

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

 MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过,NOT IN 谓词同样可以实现表的减法.

3、对称差：使用 NOT IN 实现两个表的差集.

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

二、JOIN--连结 

1、INNER JOIN--内连结

语法： 

FROM 表1 

        INNER JOIN 表2 

        ON 某条件

 在上述查询中, 我们分别为两张表指定了简单的别名, 这种操作在使用连结时是非常常见的, 通过别名会让我们在编写查询时少打很多字, 并且更重要的是, 会让查询语句看起来更加简洁.
上述查询将会得到如下的结果:

注意：

要点一: 进行连结时需要在 FROM 子句中使用多张表.

要点二:必须使用 ON 子句来指定连结条件.

要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.

1.1 INNER JOIN+WHERE的情况

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

1.2 INNER JOIN+GROUP BY的情况

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待.

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

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

1.3 SELF JOIN

 之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 

1.4 NATURAL JOIN--自然连接

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.

语法：SELECT * FROM shopproduct NATURAL JOIN product

2、OUTER JOIN--外连结

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

①左连结：会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值;

语法：FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>

②右连结：会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值;

语法：FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>

③全外连结：会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.

语法：FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

外连结要点 1: 选取出单张表中全部的信息

外连结要点 2:使用 LEFT、RIGHT 来指定主表

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

3、多表连结