[打卡]赵博玲

#满外连接(mysql不支持 full outer join 做满外查询)UNION做满外查询
SELECT employee_id,department_name
FROM employees e full  OUTER JOIN departments d
on e.department_id=d.department_id;
#UNION做满外查询 
#在实际开发中能用 UNION ALL就用UNION ALL（UNION需要去重，效率低）
#满外连接1.左上+右中2.右上+左中3.左右各一半：左中+右中
#中图：内连接
#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;
#右外连接(省略outer)
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 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;