[打卡]陈煜杭 多表查询

#查询员工id和部门名字
select e.employee_id,d.department_name from employees e,departments d where e.department_id=d.department_id;
#角度3：内连接 vs 外连接
select * from employees;
#之前做的都是内连接
#有的员工没部门，有的有部门没员工
#如果把所有都查出来就是外连接
#查询所有员工id和部门名字 106(查询出来的)+1(空的)=107
#如果做外连接，涉及两个语法规范，SQL92 SQL99
#外连接的分类;左外连接，右外连接，满外连接

#查询所有员工id和部门名字

#SQL92
#92内连接见上（开头所写）
#92外连接: 使用 +
#报错：因为MySQL不支持使用SQL92的外连接
select e.employee_id,d.department_name from employees e,departments d where e.department_id=d.department_id(+);
#SQL99
#99内连接
select employee_id,department_name from employees e join departments d on e.department_id=d.department_id;
#也可以在join前加inner，可省略 select employee_id,department_name from employees e inner join departments d on e.department_id=d.department_id;
# 加上city
select employee_id,department_name,city from employees e join departments d on e.department_id=d.department_id join locations l on d.location_id=l.location_id;
#99外连接
#左外连接 (outer可省略)
select employee_id,department_name from employees e left outer 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;
#122-106(共有的)=16(有部门没员工的)