php 4个表模糊查询union all,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;