How to implement a ZIP JOIN in T-SQL?

Question

Let say I have table #Foo:

Id Color
-- ----
1  Red
2  Green
3  Blue
4  NULL

And table #Bar:

Value
-----
1
2.5

I would like to create table Result using simple statement to get:

Id Color   Value
-- ----   -----
1  Red    1
2  Green  2.5
3  Blue   NULL
4  NULL   NULL

What I have invented so far is:

WITH cte1
AS
(
    SELECT [Id], [Color], ROW_NUMBER() OVER (ORDER BY [Id]) AS 'No'
    FROM #Foo
),
cte2
AS
(
    SELECT [Value], ROW_NUMBER() OVER (ORDER BY [Value]) AS 'No'
    FROM #Bar
)
SELECT [Id], [Color], [Value]
FROM cte1 c1
FULL OUTER JOIN cte2 c2 ON c1.[No] = c2.[No]

Do you know faster or more standard way to do ZIP JOIN in T-SQL?

Answer 1

You can simply try this.

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id, Value FROM #Bar
)
SELECT F.Id, F.Color, CTE.Value
FROM #Foo F
LEFT JOIN CTE ON CTE.Id = F.Id