1、建表数据:
create table a(fname varchar(30),lname varchar(30),addr varchar(30))
insert into a values ("a_fname1","a_lname1","a_addr1");
insert into a values ("a_fname2","a_lname2","a_addr2");
insert into a values ("a_fname3","a_lname3","a_addr3");
insert into a values ("test","test","test");
create table b(last_name varchar(30),first_name varchar(30),address varchar(30));
insert into b values ("b_lastanme1","b_firstname1","b_address1");
insert into b values ("b_lastanme2","b_firstname2","b_address2");
insert into b values ("b_lastanme3","b_firstname3","b_address3");
insert into b values ("test","test","test");
create table c(company varchar(30),street varchar(30));
insert into c values ("c_company1","c_street1");
insert into c values ("c_company2","c_street2");
insert into c values ("c_company3","c_street3");
2、测试结果:
在表a和表b中有两条重复的数据,如果使用union则是不显示重复的数据,使用union all则显示重复的数据,下图为使用union,共10条数据:
下图为只是用一个union all,也是不显示重复的数据,共10条数据:
- 使用两个union all显示重复的数据,共11条数据:
注:MySQL版本为5.0;来源:MySQL UNION关键字