第16课:组合查询UNION ALL

本节要点

组合查询UNION ALL
组合查询UNION
合并多个结果集
合并不同来源的结果集
UNION ALL与UNION混用
组合查询结果集的排序

组合查询UNION ALL

前面我们讲过集合与子集的概念。仍然以深圳市第一初级中学的所有学生这个集合为例,在这个大的集合里,包含了很多小的集合。

比如说,男生是一个子集合,女生是一个子集合;

初一的同学是一个子集合,初二的同学是一个子集合,初三的同学是一个子集合;

年龄大的同学是个子集合,年龄小的同学是一个子集合;

那么,再来考虑下下面这个这个查询需求:

(1)、如何同时查询出年龄为10岁或一年级一班的所有学生?

这里包含了两个条件,年龄为10岁和一年级一班。分开来查询的话,就可以写为两个SELECT语句。

SELECT * FROM student WHERE age = 10;
SELECT * FROM student WHERE class_id = 'G0101';

在前面的课程中,我们学习了OR这个逻辑操作符。那么,这两个SELECT就可以使用OR逻辑操作符简化成下面这样:

SELECT * FROM student WHERE age = 10 OR class_id = 'G0101';

假如,在学生表中,年龄为10岁的同学有14个;而一年级一班共有12个学生。

那么年龄为10岁的同学加上一年级一班的同学,是不是就是14+12=26呢?

那肯定是不一定的,原因是,可能有些同学,同时满足年龄10岁,并且是一年级一班这两个条件。

对于OR逻辑操作符来说,如果有同时满足条件的记录,那么只会返回一条。

比如说,有两个同学既满足年龄10岁,也满足一年级一班,那么使用OR逻辑操作符查询的结果就是14+12-2=24条记录。

因为,对于同时满足这两个条件的记录,返回时是不会返回两条的。

可如果对于同时满足这两个条件的记录,我们想返回多条,也就是说不去重,应该怎么写呢?

其实,最简单的做法,就是将下面这两个查询的结果集合并起来。

SELECT * FROM student WHERE age = 10;
SELECT * FROM student WHERE class_id = 'G0101';

在SQL语言中,合并结果集的关键字是UNION ALL,用来将2个结果集进行合并,并且合并的过程中不去重。写法如下:

SELECT * FROM student WHERE age = 10
UNION ALL
SELECT * FROM student WHERE class_id = 'G0101';

我们之前讲过,在数学上,集合有3个特性:确定性、互异性、无序性。而对于我们的SELECT查询结果集来说,确定性和无序性,也是遵守的。但并没有强制的互异性。

也就是说,SELECT的查询结果集,是允许两条完全相同的记录出现的。就像上面这个UNION ALL的结果,会有2条完全重复的记录返回。

组合查询UNION

可有的时候,我们又不想要这样重复的记录,怎么办呢?

很简单,将UNION ALL改为UNION就可以了。

(1)、如何同时查询出年龄为10岁或一年级一班的所有学生(去除重复)?

SELECT * FROM student WHERE age = 10
UNION
SELECT * FROM student WHERE class_id = 'G0101';

这样的话,SELECT查询在返回结果前,会自动将2条重复的数据去重。也就是说,返回的结果与使用OR操作符的结果,是一样的。

所以说,UNION ALL与UNION,都是用来合并2个结果集的,但UNION ALL在合并的时候,并不会去除重复记录,而UNION在合并完成后,还会将重复的记录删除后再返回。

这就是UNION ALL与UNION的区别。

合并多个结果集

前面我们合并的是2个结果集,那2个以上的结果集如何合并呢?

(1)、如何同时查询出年龄为10岁或一年级一班或性别为男的所有学生?

SELECT * FROM student WHERE age = 10
UNION
SELECT * FROM student WHERE class_id = 'G0101'
UNION
SELECT * FROM student WHERE gender = '男';

合并起来也很简单,多合并一个结果集,就多加一个UNION就可以了。当然了,如果不想去重,使用UNION ALL也是一样的。

合并不同来源的结果集

上面我们合并的结果集,都是来源于同一张表的。

那来源于不同表的结果集,可不可以合并呢?

其实也是可以的,但会有一些限制。

(1)、如何同时查询出所有的学生编号、学生姓名和老师编号、老师姓名?

SELECT student_id,student_name FROM student
UNION
SELECT teacher_id,teacher_name FROM teacher;

上面的UNION是可以执行成功的。

那么,不同来源表的结果集,什么情况下可以合并,什么情况下不能合并呢?

再来看一个例子:

(2)、如何同时查询出所有的学生编号、学生姓名、学生年龄和老师编号、老师姓名?

SELECT student_id,student_name,age FROM student
UNION
SELECT teacher_id,teacher_name FROM teacher;

这个执行结果是报错的!

为什么呢?

因为待合并的2个结果集的字段个数不一致。

那么,也就是说,在使用UNION ALL和UNION去合并2个结果集时,这2个结果集返回的字段个数必须是一致的。

再看一个例子:

(3)、如何同时查询出所有的学生编号、学生姓名、学生年龄和老师编号、老师姓名、老师性别?

SELECT student_id,student_name,age FROM student
UNION
SELECT teacher_id,teacher_name,gender FROM teacher;

这个SQL虽然不会报错,但查询结果集的最后一个字段,在第一个结果集里是age,类型为INT;在第二个结果集里是gender,类型为VARCHAR;

一个是数值类型,一个是字符类型。那么,就会默认将第二个结果集的字符类型转换为数值类型。转换后的结果,可能跟来源表中的数据就不一样了,也就没有达到我们预期想要的结果。

其实,在多个结果集合并时,要符合以下几点:

合并后的结果集的字段名与第一个结果集保持一致。
待合并的结果集的字段顺序、字段类型的大类及字段值的含义尽量保持一致。

UNION ALL与UNION混用

在SQL语言中,也支持UNION ALL与UNION混合使用的。

(1)、如何同时查询出年龄为10岁或一年级一班(前面两个结果集需要去除重复)或性别为男(合并时不去除重复)的所有学生?

SELECT * FROM student WHERE age = 10
UNION
SELECT * FROM student WHERE class_id = 'G0101'
UNION ALL
SELECT * FROM student WHERE gender = '男';

在混合使用时,一定要注意执行顺序。

比较一下上面的SQL与下面这句SQL的执行结果的差异:

SELECT * FROM student WHERE class_id = 'G0101'
UNION ALL
SELECT * FROM student WHERE gender = '男'
UNION
SELECT * FROM student WHERE age = 10;

因为UNION ALL与UNION的执行优先级是相同的,那么,谁在前面就会先执行谁。

前面我们讲过,可以使用括号()改变操作符的执行顺序(优先级),但UNION ALL与UNION是不可以的。

所以,一般来说,不建议将UNION ALL与UNION混合使用。

组合查询结果集的排序

有时候,我们希望组合查询的结果集,按一定顺序排序后输出。

(1)、如何同时查询出年龄为10岁或一年级一班的所有学生(按姓名升序排序)?

SELECT * FROM student WHERE age = 10
UNION ALL
SELECT * FROM student WHERE class_id = 'G0101'
ORDER BY student_name;

也就是说,跟普通的SELECT查询一样,在最后加一个ORDER BY子句就可以了。

课后习题:

1、对于第6课课后习题中的论坛注册用户表、第15课课后习题中的文章表、用户访问的文章记录表,请编写SQL完成以下查询。

(1)、请查询出今天有访问论坛文章的用户以及今天注册的用户,需要将重复的用户去重。

(2)、查询出评论次数最多的前2个文章分类以及访问次数最多的前2个文章分类,不需要去重。

(3)、查询今天有访问且上月末也有访问的用户编号。

(4)、查询每个用户的常用浏览器(用来访问文章次数最多的浏览器)。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code