[打卡]陈煜杭- 多表查询

#满外链接
#mycql不支持full outer join做满外链接
select employee_id,department_name from employees e full outer join departments d on e.department_id=d.department_id;
#实际开发当中，能使用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
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;