一条SELECT查询语句,返回一个结果集。不同SELECT语句查询出的结果集之间可以完成一些集合操作。

比如:合并、排除、交集等。

UNION/UNION ALL合并结果集

语法如下:

select 子句1
union [all]
select 子句2

union:合并结果集的同时,删除重复记录;

union all:合并结果集,不删除重复记录;

使用多个UNION/UNION ALL关键字,可以一次性合并多个结果集。

connect-database-workbench-1

示例:

查询年龄在10岁以下或15岁以上的学生:

select * from school.student where age < 10
union
select * from school.student where age > 15;
或
select * from school.student where age < 10
union all
select * from school.student where age > 15;

查询年龄在10岁以下及性别为男的学生(去除重复数据):

select * from school.student where age < 10
union
select * from school.student where gender = '男';

查询年龄在10岁以下及性别为男的学生(保留重复数据):

select * from school.student where age < 10
union all
select * from school.student where gender = '男';

查询年龄在10岁以下或性别为男或姓王的学生(不保留重复数据):

select * from school.student where age < 10
union
select * from school.student where gender = '男'
union
select * from school.student where student_name like '王%';

EXCEPT排除结果集

语法如下:

select 子句1
except
select 子句2

except:返回在SELECT子句1里,但不在SELECT子句2里的记录;

connect-database-workbench-1

示例:

查询年龄在10岁以下但不姓王的学生:

select * from school.student where age < 10
except
select * from school.student where student_name like '王%';

INTERSECT取结果集的交集

语法如下:

select 子句1
intersect
select 子句2

intersect:返回既在SELECT子句1里,又在SELECT子句2里的记录;

connect-database-workbench-1

示例:

查询年龄在10岁以下且姓王的学生:

select * from school.student where age < 10
intersect
select * from school.student where student_name like '王%';

picture loss