T-SQL (Cross?) Joins

问题:

Code:

DECLARE @T1 TABLE (ID INT, Val1 VARCHAR(25), Val2 VARCHAR(25));
DECLARE @T2 TABLE (ID INT, Val3 BIT);
DECLARE @T3 TABLE (ID INT, Val4 DECIMAL(18,6));

INSERT INTO @T1 ( [ID], [Val1], [Val2] )
VALUES  ( 1, 'V1One','V2One' )
        ,(2, 'V1Two','V2Two' )
        ,(3, 'V1Three','V2Three' )
        ,(4, 'V1Four','V2Four' );

INSERT INTO @T2 ( [ID], [Val3] )
VALUES  ( 3, 1 );

INSERT INTO @T3 ( [ID], [Val4] )
VALUES  ( 4, 9.99 )
        ,( 5, 0.99 );

Desired Output:

ID  Val1    Val2    Val3    Val4
1   V1One   V2One   NULL    NULL
2   V1Two   V2Two   NULL    NULL
3   V1Three V2Three 1       NULL
4   V1Four  V2Four  NULL    9.990000
5   NULL    NULL    NULL    0.99

Goal:

To combine three tables based on [ID] field ("key column") and create the desired output. If a record exists in only two tables, return NULL for the other table's field(s) - i.e. ID = 3 and 4 If a record exists in one table but not other, return NULL for the other tables' field(s) - i.e. ID = 1, 2, and 5

The query has to perform fast as the real data-set is quite huge and much more complex.

My try so far..

SELECT COALESCE([T1].[ID],[T2].[ID],[T3].[ID]), Val1, Val2, Val3, Val4 
FROM @T1 T1
CROSS JOIN @T2 T2 
CROSS JOIN @T3 T3
--WHERE T1.[ID] = T2.[ID]
--WHERE T1.[ID] = T3.[ID]

回答1:

You want FULL join, rather than CROSS join.

SELECT COALESCE([T1].[ID],[T2].[ID],[T3].[ID]), Val1, Val2, Val3, Val4 
FROM T1 T1
FULL JOIN @T2 T2 ON T2.ID = T1.ID
FULL JOIN @T3 T3 ON T3.ID = COALESCE(T1.ID, T2.ID)