[打卡]秦子月

-- 满外链接

-- 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需要去重，效率低
-- （满外）左上+右中
-- （满外）右上+左中
-- 左中+右中（左右各一半）

-- 中图 内连接(两个方法)
-- 1.SQL99
SELECT employee_id,department_name
from employees e join departments d
ON e.department_id=d.department_id;
-- 2.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;