2.多表查询

1. 笛卡尔积错误:多表查询必须有连接条件，否则会产生笛卡尔积错误，得到数据条数=每个表数据条数的积

select last_name,employees.department_id, department_name from employees, departments

注意：查询的字段在多个表同时存在必须带表名

2. 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
   等值连接（两个表）

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,l.city from employees e, departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id

连接n个表，需要n-1个连接条件

非等值连接：

select employee_id,last_name,salary,grade_level from employees e,job_grades j where e.salary between j.lowest_sal and j.highest_sal

自连接：
查询公司中员工‘chen’的manager信息：

select emp.last_name,manager.last_name,manager.salary,manager.email from employees emp, employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'

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

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, departments d where e.department_id(+) = d.department_id

4. SQL:1999语法：
   自然连接(natural join)：自动连接两个表中所有相同的字段，查询的字段名不能使用限定词

select last_name,department_id, department_name from employees e natural join departments d

useing:查询字段不能使用限定词，两个表的连接字段名和数据类型必须相同才能使用using

select last_name,department_id, department_name from employees e join departments d using(department_id)

内关联：join on格式

select last_name,e.department_id, department_name from employees e join departments d on e.department_id = d.department_id

select last_name,e.department_id, department_name,l.city from employees e 
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id

外关联:outer可以省略
左外关联：

select last_name,e.department_id, department_name from employees e left outer join departments d on e.department_id = d.department_id

右外关联：

select last_name,e.department_id, department_name from employees e right outer join departments d on e.department_id = d.department_id

全外关联：

select last_name,e.department_id, department_name from employees e full outer join departments d on e.department_id = d.department_id

练习题：

1. 显示所有员工的姓名，部门号和部门名称。

select last_name,e.department_id,department_name
from employees e, departments d
where e.department_id = d.department_id(+);

2. 查询90号部门员工的job_id和90号部门的location_id

select distinct job_id,location_id
from employees e left join departments d
on e.department_id = d.department_id
where d.department_id = 90

3. 选择所有有奖学金的员工的last_name,department_name,location_id,city

select last_name,department_name,d.location_id,city
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
where e.commission_pct is not null;

4. 选择city在Toronto工作的员工的last_name,job_id,department_id,department_name

select last_name,job_id,e.department_id,department_name
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
where l.city = 'Toronto';

5. 自连接练习

select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name "Manager",e2.employee_id "Mgr#"
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+);