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