[打卡]闫冬浩—多表查询

-- 满外连接
# MySQL不支持 FULL OUTER JOIN 做满外连接
select * from employees;
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需要去重，效率低
#中图：内链接
#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 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
nion 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;