declare @tab1 table(no varchar(20),ye1 int)
insert @tab1 values('A',100)
insert @tab1 values('B',200)
insert @tab1 values('C',300)
select * from @tab1declare @tab2 table(no varchar(20),ye2 int)
insert @tab2 values('A',100)insert @tab2 values('C',200)
select * from @tab2select a.no,a.ye1,ye2=isnull(b.ye2,0) from @tab1 a left join @tab2 b
on a.no=b.no
insert @tab1 values('A',100)
insert @tab1 values('B',200)
insert @tab1 values('C',300)
select * from @tab1declare @tab2 table(no varchar(20),ye2 int)
insert @tab2 values('A',100)insert @tab2 values('C',200)
select * from @tab2select a.no,a.ye1,ye2=isnull(b.ye2,0) from @tab1 a left join @tab2 b
on a.no=b.no
from A,B
where A.no *= B.no
借用二楼的表及数据:
select a.no,a.ye1,case when b.ye2 is null then 0 else b.ye2 end as ye2 from @tab1 a left join @tab2 b
on a.no=b.no
from A,B
where A.no *= B.no
FROM A FULL OUTER JOIN B
ON A.no = B.no如果A和B两个Table之间的关系是平等的则需要用完全外连接.
表1
no ye1
A 100
B 200
C 300表2
no ye2
A 100
C 200
D 200合并后的表3
no ye1 ye2
A 100 100
B 200 0
C 300 200
D 0 200
上面的方法不能显示D记录
from
(select no,ye1,0 as ye2
from table1
union all
select no,0 as ye1,ye2
from table2
)
group by no
from
(select no,ye1,0 as ye2
from table1
union all
select no,0 as ye1,ye2
from table2
) as aa
group by no
as
select *from table_1
where no=(select *from table_2)
这个是视图
form tab1 inner join tab2
on tab1.no=tab2.no