mysql中union、union all的用法实例

时间:2022-09-20 14:24:56

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条数据:
    mysql中union、union all的用法实例

  • 下图为只是用一个union all,也是不显示重复的数据,共10条数据:
    mysql中union、union all的用法实例

  • 使用两个union all显示重复的数据,共11条数据:
    mysql中union、union all的用法实例

注:MySQL版本为5.0;来源:MySQL UNION关键字