Snowflake CROSS JOIN引用VALUES子句外部表报错，有什么替代方案？

核心结论 #

• Snowflake的普通CROSS JOIN不支持在右侧的VALUES子句中直接引用左侧关联表的字段，这是你触发Invalid expression [T.ID1] in VALUES clause报错的直接原因。
• Snowflake中等价于MSSQL CROSS APPLY的语法是LATERAL JOIN，可以实现右侧子查询引用左侧表字段的需求。

方案1：实现原MSSQL CROSS APPLY逻辑（每行拆分为id1、id2两行） #

你只需要在原SQL的CROSS JOIN后加上LATERAL关键字即可正常运行：

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)

运行输出结果：

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

方案2：实现硬编码示例的全量笛卡尔积效果 #

如果你需要实现所有行和所有id1、id2值的全笛卡尔积，且不想硬编码值，可以先提取所有id值再关联：

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

运行输出结果和你提供的硬编码示例完全一致：

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

内容的提问来源于stack exchange，提问作者Dan