oracle里表连接支持标准写法,但也有oracle特殊的写法,这两种写法在某些场景下会有差异,推荐使用标准写法,这里只是介绍表连接标准语法及了解oracle的特殊写法。
标准连接语法:
select table1.column , table2.column
from table1
[corss join table2]
[national jon table2]
[join table2 using (column)]
[join table2 on (table1.column=table2.column)]
[left | right | full outer join table2 on (table1.column=table2.column)];
实际使用中on关键字后的连接字段不用括号也可以正常使用。
多表连接:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
--先连接table4和table5并将其结果集命名为table2,再与table1连接
select
table1.
column
,table2.
column
from
table1
inner
join
(
select
table4.
column
,table5.
column
from
table4
inner
join
table5
on
table4.
column
=table5.
column
)
as
table2
on
table1.
column
=table2.
column
;
等同于
select
table1.
column
,table2.
column
from
table1 ,(
select
table4.
column
,table5.
column
from
table4,table5
where
table4.
column
=table5.
column
)
as
table2
where
table1.
column
=table2.
column
;
--连接table1,table2,table3,没有连接顺序之分
select
table1.
column
,table2.
column
,table3.
column
from
table1
inner
join
table2
on
table1.
column
=table2.
column
inner
join
table3
on
table1.
column
=table3.
column
;
等同于
select
table1.
column
,table2.
column
,table3.
column
from
table1,table2.table3
where
table1.
column
=table2.
column
and
table1.
column
=table3.
column
;
|
内连接:
标准写法:
1
2
|
select
table
.
column
, table2.
column
from
table1
inner
join
table2
on
(table1.
column
=table2.
column
);
|
oracle 特殊写法:
1
2
3
|
select
table
.
column
, table2.
column
from
table1 ,table2
where
table1.
column
=table2.
column
;
|
左连接:
标准写法:
1
2
|
select
table
.
column
, table2.
column
from
table1
left
join
table2
on
(table1.
column
=table2.
column
);
|
oracle 特殊写法:
1
2
3
|
select
table
.
column
, table2.
column
from
table1 ,table2
where
table1.
column
=table2.
column
(+);
|
右连接:
标准写法:
1
2
|
select
table
.
column
, table2.
column
from
table1
right
join
table2
on
(table1.
column
=table2.
column
);
|
oracle 特殊写法:
1
2
3
|
select
table
.
column
, table2.
column
from
table1 ,table2
where
table1.
column
(+)=table2.
column
;
|
全连接:
标准写法:
1
2
|
select
table
.
column
, table2.
column
from
table1
full
join
table2
on
(table1.
column
=table2.
column
);
|
oracle 特殊写法:
1
2
3
|
select
table
.
column
, table2.
column
from
table1 ,table2
where
table1.
column
(+)=table2.
column
(+);
|
本文转自 天黑顺路 51CTO博客,原文链接:http://blog.51cto.com/mjal01/1975625,如需转载请自行联系原作者