多表连接

内连接：106(106, 11)

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id;

 

select e.last_name, d.department_name

from employees e join departments d on e.department_id=d.department_id;         inside

 

左外连接：107（106+1）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from departments d, employees e

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from employees e left outer join departments d

on e.department_id=d.department_id;

 

右外连接：122（106+16）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id;

 

完全外连接：123（106+1+16）

select e.last_name, d.department_name

from employees e full outer join departments d

on e.department_id=d.department_id;

 

多表连接的扩展：

n张表连接：

select e.last_name, d.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;

 

select e.last_name, d.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;

 

select e.last_name, d.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(+);

 

select e.last_name, d.department_name, l.city

from employees e left outer join departments d on e.department_id=d.department_id

               left outer join locations l on d.location_id=l.location_id;

转载于:https://www.cnblogs.com/shan2017/p/7294985.html