A\B表如下,B表的BA参照A
A表 B表
____________________ _____________________
A AA B BA BB
^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^
1 12 x 1 xx
2 13 y 1 yx
3 14 z null ul
4 15 a 3 ab
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
视图AB
___________________________________________________________
AA B BA BB
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
12 x 1 xx
12 y 1 yx
null z null ul
14 a 3 abcreate view 视图AB
as
select A表.AA,B表.*
from A表,B表
where B表. BA=A表.A
go
如上实现的话,结果中就少了应该有的第三条BA为null的记录!!!!应如何实现?
A表 B表
____________________ _____________________
A AA B BA BB
^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^
1 12 x 1 xx
2 13 y 1 yx
3 14 z null ul
4 15 a 3 ab
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
视图AB
___________________________________________________________
AA B BA BB
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
12 x 1 xx
12 y 1 yx
null z null ul
14 a 3 abcreate view 视图AB
as
select A表.AA,B表.*
from A表,B表
where B表. BA=A表.A
go
如上实现的话,结果中就少了应该有的第三条BA为null的记录!!!!应如何实现?
from @a a right join @b b
on a.a=b.ba
declare @b table(b char(1),ba int,bb char(2))
insert into @a select 1,12
union all select 2,13
union all select 3,14
union all select 3,15
insert into @b select 'x',1,'xx'
union all select 'y',1,'yx'
union all select 'z',null,'ul'
union all select 'a',3,'ab'
select a.aa,b.*
from @a a right join @b b
on a.a=b.ba
/*
所影响的行数为 4 行)aa b ba bb
----------- ---- ----------- ----
12 x 1 xx
12 y 1 yx
NULL z NULL ul
14 a 3 ab
15 a 3 ab(所影响的行数为 5 行)*/
from @a a right join @b b
on a.a=b.ba
right join b on a.a=b.ba
right join b on a.a=b.ba