sql的表连接

标准：

natural join on using    #自然连接

outer join (left outer join ,right outer join ,full outer join )      #外连接

 

------------------------内连接-------------------------------

select e.last_name employee,m.last_name manager
from employees e,employees m
where e.manager_id=m.employee_id

 

 

-------------自然连接------当2个表中有同样的column name且data type一致时可使用自然连接----------

select e.last_name,e.salary,d.department_name

from employees e natural join departments d

 

当column name或data type不满足自然连接的要求时，可以使用 using语句

select e.last_name,e.salary,d.department_name

from employees e  join departments d

using (department_id)

 

等同于

 

select e.last_name,e.salary,d.department_name

from employees e,departments d

where e.department_id = d.department_id

 

 

------------------------外连接-------------------------------

select e.last_name,e.salary,d.department_name

from employees e join departments d                    

on (e.department_id = d.department_id)

 

 

等同于

 

select e.last_name,e.salary,d.department_name

from employees e,departments d
where e.department_id = d.department_id

 

 

------------------------左外连接-------------------------------

select e.last_name,e.salary,d.department_name

from employees e left outer join departments d

on (e.department_id = d.department_id)

 

等同于

 

select e.last_name,e.salary,d.department_name

from employees e,departments d
where e.department_id = d.department_id（+）

 

 

-------------------------右外连接------------------------------

select e.last_name,e.salary,d.department_name

from employees e right outer join departments d

on (e.department_id = d.department_id)

 

等同于

 

select e.last_name,e.salary,d.department_name

from employees e,departments d
where e.department_id（+） = d.department_id

 

 

-------------------------全外连接------------------------------

select e.last_name,e.salary,d.department_name

from employees e full outer join departments d

on (e.department_id = d.department_id)

非标准语法不能写出全连接

 

 

-------------------------交叉连接------------------------------------

select e.last_name,e.salary,d.department_name

from employees e cross join departments d