[打卡]王俊影 多表查询3

select employee_id,department_name
from employees e left join departments d
on e.department_id = d.department_id;

select employee_id,department_name
from employees e right join departments d
on e.department_id = d.department_id;

-- MySQL不支持 Full OUTER JOIN 做满外查询
select employee_id,department_name
from employees e full outer
join departments d
on e.department_id = d.department_id;

-- UNION来做满外查询

-- 实际开发中，能用UNION ALL 就不用UNION

-- UNION需要去重，效率低

-- 满外连接

-- 坐上 + 右中

-- 右上 + 左中

-- 左右各一半：左中+右中

-- 中图：内连接

-- SQL99

select employee_id, department_name
from employees e join departments d
on e.department_id = d.department_id;

-- SQL92
select employee_id,department_name
from employees e,departments d
where e.employee_id = d.department_id;

-- 右连接
select employee_id,department_name
from employees e right join departments d
on e.employee_id = d.department_id;

-- 左中
select employee_id,department_name
from employees e left join departments d
on e.department_id = d.department_id
where d.department_id is null;

-- 右中
select employee_id,department_name
from employees e right join departments d
on e.department_id = d.department_id
where d.department_id is null;

-- 右上+左中
select employee_id,department_name
from employees e left join departments d
on e.department_id = d.department_id
union all
select employee_id,department_name
from employees e right join departments d
on e.department_id = d.department_id
where e.department_id is null;

-- 左中+右中
select employee_id,department_name
from employees e left join departments d on e.department_id = d.department_id
where d.department_id is null
union all
select employee_id,department_name
from employees e right join departments d
on e.department_id = d.department_id
where e.employee_id is null;