Snowflake CROSS JOIN referencing prior table in VALUES clause

Question

Does Snowflake CROSS JOIN support referencing other tables in join? This statement produces the error:

SQL compilation error: Invalid expression [T.ID1] in VALUES clause

WITH t AS
(
    SELECT *
    FROM (VALUES
        (1, 2)
    ,   (3, 4)
    ) x(id1, id2)
)
SELECT
    t.*
,   x.*
FROM t
CROSS JOIN (VALUES
    (t.id1)
,   (t.id2)
) x(id3)

This works as expected in MSSQL (using CROSS APPLY instead of CROSS JOIN).

However, hardcoding the values in the cross join works, but is not practical for my use case:

WITH t AS
(
    SELECT *
    FROM (VALUES
        (1, 2)
    ,   (3, 4)
    ) x(id1, id2)
)
SELECT
    t.*
,   x.*
FROM t
CROSS JOIN (VALUES
    (1)
,   (2)
,   (3)
,   (4)
) x(id3)

It produces the expected result:

ID1,ID2,ID3
1,2,1
3,4,1
1,2,2
3,4,2
1,2,3
3,4,3
1,2,4
3,4,4

Is there another way to produce the desired result in Snowflake?

Answer 1

There is no CROSS APPLY in Snowflake.

Is this dynamic enough?

WITH t AS
(
    SELECT *
    FROM (VALUES
        (1, 2)
    ,   (3, 4)
    ) t (id1, id2)
)
SELECT
    t.*,
    third_col
FROM t
CROSS JOIN (SELECT ID1 as third_col FROM t UNION ALL SELECT ID2 FROM t)

Answer 2

Does Snowflake CROSS JOIN support referencing other tables in join?

Yes, it is possible. The ISO SQL Standard counterpart of CROSS APPLY/OUTR APPLY is called LATERAL JOIN.

WITH t AS (
SELECT *
    FROM (VALUES
        (1, 2)
    ,   (3, 4)
    ) x(id1, id2)
)
SELECT    t.*,   x.*
FROM t
CROSS JOIN LATERAL (SELECT t.id1) x(id3) -- referencing t.id1
UNION ALL 
SELECT    t.*,   x.*
FROM t
CROSS JOIN LATERAL (SELECT t.id2) x(id3);

---

Interestingly enough using SELECT instead of VALUES in that context causes error:

WITH t AS (
SELECT *
    FROM (VALUES
        (1, 2)
    ,   (3, 4)
    ) x(id1, id2)
)
SELECT    t.*,   x.*
FROM t
CROSS JOIN LATERAL (VALUES (t.id1), (t.id2)) x(id3)  
-- regardless of single or more values

SQL execution internal error: Processing aborted due to error

db<>fiddle demo

UNION ALL to simulate multiple rows:

WITH t AS (
SELECT *
    FROM (VALUES
        (1, 2)
    ,   (3, 4)
    ) x(id1, id2)
)
SELECT    t.*,   x.*
FROM t
CROSS JOIN LATERAL (SELECT t.id1 UNION ALL SELECT t.id2) x(id3);

SQL compilation error: Unsupported subquery type cannot be evaluated