mysql——多表——合并查询结果

我不是女神ヾ 2023-10-02 08:51 24阅读 0赞

合并查询结果

合并查询结果 是将多个select语句的查询结果合并到一起

union关键字，数据库会将所有的查询结果合并到一起，然后除掉相同的记录；

union all关键字，只是简单的合并到一起

前期表准备：

create table employee ( num int(50),
                            d_id int(50),
                            name varchar(50),
                            age int(50),
                            sex varchar(50),
                            homeadd varchar(50)
                           );
    
    insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
    insert into employee values(2,1001,'lisi',24,'nv','hunan');
    insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
    insert into employee values(4,1004,'aric',15,'nan','yingguo');
    
    select * from employee;
    
    create table department ( d_id int(50),
                              d_name varchar(50),
                              functione varchar(50),
                              address varchar(50)
                            );
    
    insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
    insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
    insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
    
    select * from department;

select d_id from department union select d_id from employee;

![2d073712f8954bc43d0879dc76fe55b7.png][]

select d_id from department union all select d_id from employee;

![ef0b65c0d99d5a9240757d410b0d19d5.png][]

[2d073712f8954bc43d0879dc76fe55b7.png]: https://img-blog.csdnimg.cn/img_convert/2d073712f8954bc43d0879dc76fe55b7.png
[ef0b65c0d99d5a9240757d410b0d19d5.png]: https://img-blog.csdnimg.cn/img_convert/ef0b65c0d99d5a9240757d410b0d19d5.png