23-基础SQL-多表查询-联合查询（UNION、UNION ALL）

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

案例：创建部门表 和 员工表（熟悉联合查询）

-- 部门表
CREATE TABLE dept(
    id int auto_increment comment "ID" primary key ,
    name varchar(50) not null comment "部门名称"
)comment "部门表";

-- 员工表
CREATE TABLE emp(
    id int auto_increment comment "ID" primary key,
    name varchar(50) not null comment "姓名",
    age int comment "年龄",
    job varchar(20) comment "职位",
    salary int comment "薪资",
    entrydate date comment "入职时间",
    managerid int comment "直属领导ID",
    dept_id int comment "部门ID"
) comment "员工表";

-- 部门表数据
INSERT INTO dept(id,name)
VALUES
(1,"研发部"),
(2,"市场部"),
(3,"财务部"),
(4,"销售部"),
(5,"总经办")

-- 员工表数据
INSERT INTO emp(id,name,age,job,salary,entrydate,managerid,dept_id)
VALUES
(1, "员工1", 66, "总裁", 20000, "2000-01-01", null, 5),
(2, "员工2", 35, "经理", 12500, "2007-01-01", 1, 1),
(3, "员工3", 20, "开发", 8300, "2020-01-01", 2, 1),
(4, "员工4", 23, "开发", 10400, "2021-01-01", 2, 1),
(5, "员工5", 26, "开发", 11000, "2021-01-01", 3, 1)

1）将薪资低于10000的员工，和年龄大于30岁以上的员工全部查询出来

-- 联合查询
SELECT * FROM(

SELECT * FROM emp WHERE emp.salary <= 10000

UNION ALL

SELECT * FROM emp WHERE emp.age >= 30) c 

ORDER BY c.id