连接查询

(多表查询一定要考虑左外连接、右外连接的问题)
内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

1.等值连接：连接n个表，至少需要n-1个连接条件
同时查询三表数据：
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id

from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id

2.非等植连接：
select last_name,salary,grade_level from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal

3.自连接：
查询公司中员工’Chen’的manager的信息
select emp.last_name,m.last_name from employees emp,employees m
where emp.manager_id = m.employee_id and emp.last_name = ‘Chen’

外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足
条件的行 ，这种连接称为左（或右） 外连接

1.左外连接：
显示所有员工的姓名，部门号和部门名称(如果有 where 语句，写在 on 的后面)
方法一：
select last_name,e.department_id,department_name from employees e,departments d
where e.department_id = d.department_id(+)
方法二：
select last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id = d.department_id

2.右外连接：

3.满外连接：
select employee_id,d.department_id,department_name
from employees e full outer join departments d
on e.department_id = d.department_id