如何全关联查询
例:
表X
NO VALUE X-FACTOR
1 ONE X1
2 TWO X2

表Y
NO VALUE Y-TELENT
1 ONE Y1
3 THREE Y3

目标:
表Z
NO VALUE X-FACTOR Y-TELENT
1 ONE X1 Y1
2 TWO X2
3 THREE Y3

NO是key，求如何写sql查询语句


[解决办法]

SQL code

create table TX([NO] int, value varchar(5), [X-FACTOR] varchar(4))insert into TXselect 1, 'ONE', 'X1' union allselect 2, 'TWO', 'X2'create table TY([NO] int, value varchar(5), [Y-TELENT] varchar(4))insert into TYselect 1, 'ONE', 'Y1' union allselect 3, 'THREE', 'Y3'select isnull(x.[NO],y.[NO]) 'NO',       isnull(x.[VALUE],y.[VALUE]) 'VALUE',       isnull(x.[X-FACTOR],'') 'X-FACTOR',       isnull(y.[Y-TELENT],'') 'Y-TELENT'into TZfrom TX xfull join TY yon x.[NO]=y.[NO]select * from TZ/*NO          VALUE X-FACTOR Y-TELENT----------- ----- -------- --------1           ONE    X1       Y12           TWO    X2       3           THREE           Y3(3 row(s) affected)*/
[解决办法]
with tabletmp as
(
 select NO, VALUE
 from X
 union
 select NO, VALUE
 from Y
)
select NO,  
      VALUE,
      X.X-FACTOR,
      Y.Y-TELENT
from tabletmp
LEFT OUTER JOIN X on X.NO = tabletmp.NO
LEFT OUTER JOIN Y on Y.NO = tabletmp.NO
[解决办法]
全关联 full join 即可
[解决办法]
探讨


谢谢两位，1楼的很好，虽然我用的是oracle，要把isnull换成nvl，稍后给分