sql查询两张表的并集union和union all

使用 union all 和 union

1.新建两张表：student、teacher
//学生表
create table student(
id int primary key,
name varchar(40)
);
//老师表
create table teacher(
id int primary key,
name varchar(40)
);

2.分别给两张表加入一条记录
insert into student(id,name) values(1,'student1');
insert into teacher(id,name) values(2,'teacher1');

3.查询并集：
方式一（不去重复）：
select * from student
union all
select * from teacher;

方式二（去掉重复）：
select * from student
union
select * from teacher;

带where子句的：
select * from student
where id = 1
union all
select * from teacher;

加字段区分记录是数据哪个表的:
select a.*, '1' as belongf from student a
union all
select b.*, '0' as belongf from teacher b;

加order by子句：
select * from
(
select a.*, '1' as belongf from student a
union all
select b.*, '0' as belongf from teacher b
) t
order by belongf desc,id desc;

查并集的总记录数：
select count(1) as mycount from (
select a.*, '1' as belongf from student a
union all
select b.*, '0' as belongf from teacher b
) d;

 

转载于:https://www.cnblogs.com/chentu279203653/p/9450143.html