[打卡]甄帅垚——多表查询

#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.department_id=d.department_id;

#左外连接
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;

#左中图
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 e.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 right join departments d
on e.department_id = d.department_id
union all
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 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.department_id is null;