标准:
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
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
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
on (e.department_id = d.department_id)
非标准语法不能写出全连接
-------------------------交叉连接------------------------------------
select e.last_name,e.salary,d.department_name
from employees e