使用 union all 和 union

1.新建兩張表：student、teacher
//學(xué)生表
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.查詢并集：
方式一（不去重復(fù)）：
select * from student
union all
select * from teacher;

方式二（去掉重復(fù)）：
select * from student
union
select * from teacher;

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

加字段區(qū)分記錄是數(shù)據(jù)哪個(gè)表的:
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;

查并集的總記錄數(shù)：
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;

?

轉(zhuǎn)載于:https://www.cnblogs.com/chentu279203653/p/9450143.html

總結(jié)

以上是生活随笔為你收集整理的sql查询两张表的并集union和union all的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。