oracle数据库连接类型

连接类型
1.cross join 笛卡尔积
不能带条件,如on子句

2.inner join 内连接
默认的连接，inner可省略

3.outer join 外连接
FROM table1 { LEFT | RIGHT | FULL } [OUTER] JOIN table2
left:返回table1中的所有记录，table2对应列显示空
right:返回table2中的所有记录，table1对应列显示空
full: 不匹配的记录全部显示

outer :默认可缺省,oracle会自动在left,right,full后增加outer

left outer join
select d.dept_id,d.name,l.regional_group
from department d left outer join location l
on d.location_id=l.location_id
早期的写法
select d.dept_id,d.name,l.regional_group
from department d,location l
where d.location_id=l.location_id(+)
返回department表里所有记录


right outer join
select d.dept_id,d.name,l.regional_group
from department d right outer join location l
on d.location_id=l.location_id
早期的写法
select d.dept_id,d.name,l.regional_group
from department d,location l
where d.location_id(+)=l.location_id
返回location表里所有记录

full outer join
两张表不匹配的记录都显示