[打卡]斯大林多表查询

-- 外连接分类：左外、右外、满外

-- 满外链接
SELECT employee_id,department_id
FROM employees e FULL OUTER JOIN departments d
ON e.department_id=d.department_id
-- 报错；；MySQL不支持FULL来进行满外链接

-- 在实际开发中UNION ALL>UNION
-- UNION 会执行去重操作

-- UNION ALL 不执行去重操作


-- 99
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id

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


-- 左下图：满外链接
-- //方式一：左上UNION ALL右中
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.employee_id is null

-- 方式2：右上图 union all 左中
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

-- 右下图：左中 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
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