[打卡]陈洁 多表查询

----- 满外连接
#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 ALL
# 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 e.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;