查询去除空值_SQL多表查询—知识点梳理（五）

SQL多表查询学习大纲

一、表的加法

创建新的课程表course1

union函数

表的加法，将两个表中的数据按行合并在一起，并删除重复的数据只保留唯一数据。

union all函数

如果想保留重复行，在union后加上all即可

合并两个表

打开客户端—右击course—“复制表”—“结构和数量”—右击新出现的course的复制表course_copy—重命名为course1—右击course1—“打开表”—将表中的数据修改为前面所示数据

然后输入两个表合并的SQL语句

二、表的联结

学生表和成绩表通过学号进行关联，关系就是数据库中能够对应的匹配，在关系数据库中叫联结（join）

常用的联结有五种类型：交叉联结（cross join）、内联结（inner join）、左联结（left join）、右联结（right join）、全联结（full join）。

交叉联结（cross join）——笛卡尔积

将一个表中的每一行都与另一个表中的每一行合并在一起。

表1和表2进行交叉联结的过程：表1的第一行分别与表2中的每一行合并在一起，表1的第二行分别与表2中的每一行合并在一起...以此类推。交叉联结产生的表的行数是两张表行数的乘积。

交叉联结最典型的案例—扑克牌

交叉联结在实际业务中应用较少，因为产生的结果行数较多，需要花费大量的运算成本和设备的支持，并且交叉结果行没有实际的价值。即便如此，交叉联结依然是后面所有联结的基础，其他联结是在交叉联结的基础上加上了过滤条件。

内联结（inner join）

查找出同时存在于两张表中的数据。

学生表和成绩表进行内联结的过程：首先两个表通过学号进行内联结，然后取出两个表中都有的学号所在的行，最后将两个表里取出的数据进行合并（交叉联结），合并之后的表就拥有了两个表里的全部信息。

• • • • 内联结对应的SQL查询语句

第一个关键之处在于from子句中，前面所学的from子句只有一张表，而现在在from子句中同时使用两张表，由于表名太长会影响SQL的可读性，因此通过as关键字对每个表起别名方便使用。在使用列名时为了区别列是来自哪个表，所以会在列名前加上表的别名并用“.”连接，因此在select子句中都加了表的别名

第二个关键之处在于from子句中，用inner join把两张表联结起来，表示内联结。选取出同时存在于两张表中的数据

第三个关键之处在于from子句中，关键字on后面表示是通过哪个列进行匹配联结来产生关系的

左联结（left join）

• • • • 进行左联结的过程

第一步：下图中两个表通过学号产生匹配，两个表进行左联结时会将左侧的表作为主表，并读取主表中的全部数据，右侧表中只选取出与左侧表学号相同的行

第二步：把两个表取出的数据进行合并（交叉联结）。由于左侧表学号0002在右侧表没有对应的行，所以这一行对应的值为空值

• • • • 左联结对应的SQL查询语句

在之前的SQL语句中修改inner join为left join，其余地方均相同

在左联结的基础上还存在一个问题，如何表示下图红色部分？这是在左联结的基础上去除二者共有的部分

可以在前面的左联结SQL语句中，加入where子句“右侧表的学号为空值”

右联结（right join）

• • • • 进行右联结的过程

第一步：两个表通过学号进行右联结，将右侧表中的数据全部取出，左侧表只选出与右侧表有相同学号的行

第二步：将两个表里取出的数据进行合并（交叉联结）。由于右侧表学号0005在左侧表没有对应的行，所以这一行对应的值为空值

• • • • 右联结对应的SQL查询语句

在之前的SQL语句中修改left join为right join，其余地方均相同

在右联结的基础上还存在一个问题，如何表示下图红色部分？这是在右联结的基础上去除二者共有的部分

可以在前面的右联结SQL语句中，加入where子句“左侧表的学号为空值”

全联结（full join）

全联结查询结果会返回左侧表和右侧表中的所有行

当母行和另一个表中有匹配时，会进行合并；若当母行和另一个表中没有匹配时，另一个表中对应的值用null进行填充。下图多了两行空值分别是左联结和右联结结果出现的空值，这样两张表的数据都在全联结结果中了。

值得注意的是，MySQL不支持全联结，只需了解全联结的概念即可。

三、联结应用案例

• • 案例1：查询所有学生的学号、姓名、选课数、总成绩

• • 案例2：查询平均成绩大于85的所有学生的姓名和平均成绩

• • 案例3：查询学生的选课情况（三张表的联结）

四、case表达式

使用case表达式有利于解决复杂的查询问题。case表达式的作用相当于进行一个条件判断的函数，用来判断每一行是否满足某个条件。

• • when子句的<判断表达式>用来判断母行数据是否满足某个条件：
    • 若符合条件就运行后面的then子句，case表达式就运行到此结束，不会再继续运行后面的when子句
    • 若不符合条件就进入下一个when子句
    • 如果直至最后都没有找到符合条件的when子句，那么就会运行else中的表达式。

• • 案例1

• • 案例2

• • 案例3

第一步：from子句是联结两个表。因为要查找课程表里的全部数据，所以使用的是右联结

第二步：group by子句是对联结结果按课程号、课程名称来分组。之所以用两列来进行分组是因为原本可以只用课程号进行分组，但查询结果需要显示出课程名称，所以group by还加入了课程名称（此处可以加入分组的前提条件时必须不影响分组结果，因为一个课程号对应一个课程名称是一对一的关系，所以无论加入还是去除课程名称都对分组结果没有影响）。当group by子句有多个列分组时，只有这几个列的值全部都相同才算一组

第三步：select子句的查询结果。运用了case表达式和求和函数一起实现

注意事项

需要条件判断时可以使用case表达式，如涉及到自定义的分组问题：

1. else子句可以省略不写，此时默认else为空值，但为了更好的书写习惯建议不省略

2. 最后的end不能省略不写

3. case表达式可以写在SQL语句的任意子句里

五、总结

当实际业务工作中想要生成固定行数的表单，或特别说明了要哪张表里的全部数据时，会使用左联结或者右联结，其他情况都用内联结来获取两张表的公共部分

SQL运行顺序

• • • 当有多个表进行联结时，是在from子句里加入的联结，不会影响前面所学的SQL运行顺序。
    • 首先当SQL查询语句里有子查询时，总是会先运行子查询
    • 其次先运行蓝框里的子句，蓝框里的子句按书写顺序运行
    • 然后再运行select子句
    • 最后运行红框里的子句，红框里的子句按书写顺序运行

六、练习

Chestnut-J：SQL多表查询—练习（五）​zhuanlan.zhihu.com