select id,(case where name=null then ''else name end) as name,
(case where no=null then ''else no end) as no
from (select id,name,on from table1 all join table2) as aa
(select id,name,on from table1 all join table2) 此語句還有錯語,請樓主參考其它人的回答修正此語句.
那麼執行下面的sql
select ISNULL( A.id,B.id)ID, ISNULL(NAME,'') NAME, ISNULL(NO,'') NO
from A full join B on A.id=B.id
ORDER BY ID
得到的結果是
ID NAME NO
1 a 1
2 b
3 2
select distinct * from
(
select table1.ID,table1.Name,table2.NO from table1 left join table2 on table1.ID=table2.ID
union all
select table1.ID,table1.Name,table2.NO from table1 right join table2 on table1.ID=table2.ID
)
declare @table1 table(ID CHAR(2),NAME CHAR(3))
declare @table2 table(ID CHAR(2),NO CHAR(2))
INSERT INTO @table1 VALUES('1','AA')
INSERT INTO @table1 VALUES('2','BB')
INSERT INTO @table1 VALUES('3','CC')
INSERT INTO @table2 VALUES(1,1)
INSERT INTO @table2 VALUES(4,4)
INSERT INTO @table2 VALUES(5,5)
SELECT * FROM @table2
SELECT * FROM @table1
select ISNULL(T1.ID,T2.ID)AS ID ,ISNULL(T1.NAME,'') NAME ,ISNULL(T2.NO,'') NO from @table1 T1
full join @table2 T2 ON
(T1.ID=T2.ID
)
declare @table1 table(ID CHAR(2),NAME CHAR(3))
declare @table2 table(ID CHAR(2),NO CHAR(2))
INSERT INTO @table1 VALUES('1','AA')
INSERT INTO @table1 VALUES('2','BB')
INSERT INTO @table1 VALUES('3','CC')
INSERT INTO @table2 VALUES(1,1)
INSERT INTO @table2 VALUES(4,4)
INSERT INTO @table2 VALUES(5,5)
SELECT * FROM @table2
SELECT * FROM @table1 select ISNULL(T1.ID,T2.ID)AS ID ,ISNULL(T1.NAME,'') NAME ,ISNULL(T2.NO,'') NO from @table1 T1
full join @table2 T2 ON
(T1.ID=T2.ID
)