[打卡]lululu 多表查询

select 122-106;

#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
#满外连接
#左上 + 右中
#右上 + 左中
#左右各一半：左中+右中
#中图：内连接
select employee_id,department_name
from employees e join departments d
on e.department_id=d.department_id;

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
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;


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

select e.job_id,d.location_id
from employees e , departments d
where e.department_id=e.department_id;