表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