本节要点
组合查询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子句就可以了。
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。