A outer join B on condition1 where condition2 -----------------????A right /left /full outer join B on condition1 where condition2
A outer join B on condition1 where condition2 A inner join B on condition1 and condition2第一个是外连接,加left , right(以一个为标准去连接另一个,可能出现空值) 第二个是内连接.只有相同(相等)的才连接.(不会出现空值.)
我的意思是sel * from A (left/right) outer join B on condition1 where condition2 sel * from A inner join B on condition1 and condition2从查询结果上看是否等价?因为试过俩例子结果是相同的
on后是联接条件,where 就成了某个表的筛选条件 declare @t1 table(id int,col int,col2 int) declare @t2 table(id int,col int,col2 int) insert @t1 select 1,1,1 insert @t1 select 4,3,2 insert @t1 select 2,1,1insert @t2 select 1,1,1 insert @t2 select 3,3,2 insert @t2 select 2,1,1select * from @t1 a inner join @t2 b on a.id = b.idselect * from @t1 a right join @t2 b on a.id = b.idselect * from @t1 a left join @t2 b on a.id = b.id select * from @t1 a full join @t2 b on a.id = b.id/*id col col2 id col col2 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 1 2 1 1 2 1 1(所影响的行数为 2 行)id col col2 id col col2 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 1 NULL NULL NULL 3 3 2 2 1 1 2 1 1(所影响的行数为 3 行)id col col2 id col col2 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 1 4 3 2 NULL NULL NULL 2 1 1 2 1 1(所影响的行数为 3 行)id col col2 id col col2 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 1 NULL NULL NULL 3 3 2 2 1 1 2 1 1 4 3 2 NULL NULL NULL(所影响的行数为 4 行)*/
declare @t1 table(id int,col int,col2 int) declare @t2 table(id int,col int,col2 int) insert @t1 select 1,1,1 insert @t1 select 4,3,2 insert @t1 select 2,1,1insert @t2 select 1,1,1 insert @t2 select 3,3,2 insert @t2 select 2,1,1 select * from @t1 a full join @t2 b on a.id = b.id and a.col = b.col select * from @t1 a full join @t2 b on a.id = b.id where a.col = b.col /* id col col2 id col col2 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 1 NULL NULL NULL 3 3 2 2 1 1 2 1 1 4 3 2 NULL NULL NULL(所影响的行数为 4 行)id col col2 id col col2 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 1 2 1 1 2 1 1(所影响的行数为 2 行)*/
-----------------????A right /left /full outer join B on condition1 where condition2
第二个是内连接.只有相同(相等)的才连接.(不会出现空值.)
declare @t1 table(id int,col int,col2 int)
declare @t2 table(id int,col int,col2 int)
insert @t1 select 1,1,1
insert @t1 select 4,3,2
insert @t1 select 2,1,1insert @t2 select 1,1,1
insert @t2 select 3,3,2
insert @t2 select 2,1,1select *
from @t1 a
inner join @t2 b on a.id = b.idselect *
from @t1 a
right join @t2 b on a.id = b.idselect *
from @t1 a
left join @t2 b on a.id = b.id
select *
from @t1 a
full join @t2 b on a.id = b.id/*id col col2 id col col2
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
2 1 1 2 1 1(所影响的行数为 2 行)id col col2 id col col2
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
NULL NULL NULL 3 3 2
2 1 1 2 1 1(所影响的行数为 3 行)id col col2 id col col2
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
4 3 2 NULL NULL NULL
2 1 1 2 1 1(所影响的行数为 3 行)id col col2 id col col2
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
NULL NULL NULL 3 3 2
2 1 1 2 1 1
4 3 2 NULL NULL NULL(所影响的行数为 4 行)*/
declare @t2 table(id int,col int,col2 int)
insert @t1 select 1,1,1
insert @t1 select 4,3,2
insert @t1 select 2,1,1insert @t2 select 1,1,1
insert @t2 select 3,3,2
insert @t2 select 2,1,1
select *
from @t1 a
full join @t2 b on a.id = b.id and a.col = b.col
select *
from @t1 a
full join @t2 b on a.id = b.id
where a.col = b.col
/*
id col col2 id col col2
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
NULL NULL NULL 3 3 2
2 1 1 2 1 1
4 3 2 NULL NULL NULL(所影响的行数为 4 行)id col col2 id col col2
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
2 1 1 2 1 1(所影响的行数为 2 行)*/