针对于多表连接的的问题,union,union all 以及 在连接汇总null值的处理问题【Ps:如果有重复项,UNION ALL 也将一并合并。如果你希望过滤掉重复项,可以使用 UNION 】点击此处,获取建表语句及其数据
查询出所有策略中台的员工,并且显示所有部门
思路说明:查询出 dept_id =10 的员工,然后和部门进行上下连接,union all连接的上下文,结构必须一致,且字段类型一致
select emp_name,dept_id from emp where dept_id = 10
union all
select dept_name,dept_id from dept
查询那个部门没有员工
思路说明:看dept表的部分,是否存在员工表
SELECT
dept_name,
dept_id
FROM
dept
WHERE
dept_id NOT IN ( SELECT dept_id FROM emp )
-- ----------------------------------------
SELECT
*
FROM
dept
LEFT OUTER JOIN emp ON dept.dept_id = emp.dept_id
WHERE
emp.dept_id IS NULL
查询员工的 姓名 所在地,有绩效的员工显示出入职时间
思路说明:进行表连接,连接之后,使用if判断,判断是否有绩效
SELECT
emp_name,
location,
if(commission is null , null, induction_date) as induction_date
FROM
emp
LEFT JOIN dept on emp.dept_id = dept.dept_id
确定两个表的数据是否相同
思路说明:找出存在 a 表 但是不在 b 表的 union all 存在 b 表但是不在 a 表的 如果有数据,表示不同
-- 创建一个测试的视图表
create view V
as
select * from emp where dept_id != 10
union all
select * from emp where emp_name = '孙三'
-- 判断两个表的数据
select * from (
SELECT
emp_id,emp_name,position,managers,induction_date,salary,commission,dept_id,COUNT(*) cou
FROM
emp
GROUP BY
emp_id, emp_name, position, managers, induction_date, salary, commission, dept_id)e
where not exists (
select * from(
select
emp_id,emp_name,position,managers,induction_date,salary,commission,dept_id,COUNT(*) cou
FROM
v
GROUP BY
emp_id, emp_name, position, managers, induction_date, salary, commission, dept_id
) v
where e.emp_id = v.emp_id
and e.emp_name = v.emp_name
and e.position = v.position
and e.managers = v.managers
and e.induction_date = v.induction_date
and e.salary = v.salary
and e.commission = v.commission
and e.dept_id = v.dept_id )
union all
select * from (
SELECT emp_id,emp_name,position,managers,induction_date,salary,commission,dept_id,COUNT(*) cou
FROM
v
GROUP BY
emp_id, emp_name, position, managers, induction_date, salary, commission, dept_id) v1
where not exists (
select * from(
select
emp_id,emp_name,position,managers,induction_date,salary,commission,dept_id,COUNT(*) cou
FROM
emp
GROUP BY
emp_id, emp_name, position, managers, induction_date, salary, commission, dept_id
) e1
where e1.emp_id = v1.emp_id
and e1.emp_name = v1.emp_name
and e1.position = v1.position
and e1.managers = v1.managers
and e1.induction_date = v1.induction_date
and e1.salary = v1.salary
and e1.commission = v1.commission
and e1.dept_id = v1.dept_id )