ORACLE- join，inner join 与 left join， left outer join

表A

ID  bianhao  name
1   1001     name1
2   1002     name2
3   1002     name3
4   1003     name4

表B

ID  bianhao  jibie
1   1001     class1
2   1002     class2

1.内连接：join on 等同与 inner join on

select a.*,b.*

from test a
join test2 b on a.bianhao = b.bianhao

简写
select a.*,b.*

from test a,test2 b
where a.bianhao = b.bianhao

结果

               ID       BIANHAO  NAME              ID       BIANHAO  JIBIE

1            1            1001      name1              1            1001      class1  

2            2            1002      name2              2            1002      class2  

3            3            1002      name3              2            1002      class2  

2.外连接：left join on 等同与left outer join on

select a.*,b.*

 from test a
 left join test2 b on a.bianhao =b.bianhao

结果

               ID       BIANHAO  NAME              ID       BIANHAO  JIBIE

1            1            1001      name1              1            1001      class1  

3            2            1002      name2              2            1002      class2  

2            3            1002      name3              2            1002      class2  

4            4            1003      name4           

 

简写

select a.*,b.*

 from test a，test2 b

where a.bianhao =b.bianhao（+）

------------------------

  说明1：

A表          B表

1               1

2               1

3               2

                 2

将A表B表进行内联（内联不分主表从表）：

1

1

2

2

将A表作为主表进行外联：

1

1

2

2

3

将B表作为主表进行外联：

1

1

2

2

------------------------

  说明2：

A表          B表

1               1

1              1

联接后结果：

1

1

1

1

------------------------

  说明3：(+)与left join的链接条件

(1)left join如果将条件写到where后面则将该条件作为了内联条件，这么写table2起不到外联效果

select *

from table1 t1

left join table2 t2 on t1.field1 = t2.field1

where t2.field2 = '001'

+++++

(2)left join如果将条件写到join后面则将该条件作为了外联条件，只有这么写才能完全将table2完全作为从表链接

select *

from table1 t1

left join table2 t2 on t1.field1 = t2.field1

and t2.field2 = '001'

+++++

(3)效果同（1）

select *

from table1 t1, table t2

where t1.field1 = t2.field1(+)

and t2.field2 = '001'

+++++

(4)效果同（2），必须在从表的所有条件上加（+）

select *

from table1 t1, table t2

where t1.field1 = t2.field1(+)

and t2.field2(+) = '001'

------------------------

注：无论是内联还是外联如果有where条件则都是先进行连接查询结果集后再在结果集的基础上进行条件筛选的，如下的sql的查询结果是0条数据。

 selecta.*,b.*

fromtest a
 left join test2 b on a.bianhao =b.bianhao
 where b.bianhao = 1003

=================3表间的内外联关系====================

select *
  from t1,t2,t3
where t1.a = t2.a(+)
   and t2.b = t3.b(+)
t1外连t2外连t3
-------------------------------------------
select *
  from t1,t2,t3
where t1.a = t2.a(+)
   and t2.b = t3.b
并不是t1外连t2内连t3，而是t1,t2,t3内连
-------------------------------------------
select *
  from t1,
         (select t2.a a from t2,t3 where t2.b = t3.b) t4
where t1.a = t4.a(+)
t1外连t2内连t3
======================从表的外联条件以外的条件======================

外联时从表的条件不会影响主表取出的记录数，影响的是从表字段是否能取出。

----------(1)left join-----------
select t1.filed1, t2.filed2
from table1 t1
left join table2 t2
  on t1.filed1 = t2.filed1
----------(2)inner join-----------
select t1.filed1, t2.filed2
from table1 t1
left join table2 t2
  on t1.filed1 = t2.filed1
where t2.filed2 = 'abc'
----------(3)left join-----------
select t1.filed1, t2.filed2
from table1 t1
left join table2 t2
  on t1.filed1 = t2.filed1
 and t2.filed2 = 'abc'

+++++++++table+++++++++
table1
filed1 filed2
------------------
111  1001
112  1002
113  1003

table2
filed1 filed2
------------------
111  abc
112  def

+++++++++result+++++++++
(1)
filed1 filed2
------------------
111  abc
112  def
113  

(2)
filed1 filed2
------------------
111  abc

(3)
filed1 filed2
------------------
111  abc
112  
113