Snowflake与SQL Server左连接更新结果差异及解决方案咨询

问题描述 #

在SQL Server和Snowflake中创建了相同的4张表并插入测试数据：

-- 插入测试数据
INSERT INTO t1 (id, name, v) VALUES
(1, 'Name1_T1', 100),
(2, 'Name2_T1', 200),
(3, 'Name3_T1', 300),
(4, 'Name4_T1', 300),
(5, 'Name5_T1', 300);

INSERT INTO t2 (id, name, v) VALUES
(4, 'Name1_T2', 400),
(1, 'Name3_T2', 600); 

INSERT INTO t3 (id, name, v) VALUES
(4, 'Name3_T3', 900);

INSERT INTO t4 (id, name, v) VALUES
(2, 'Name3_T4', 1200); 

执行相同的LEFT JOIN更新语句时，结果出现差异：

-- 原更新语句
UPDATE t1
SET v = 0
FROM t1 table1
LEFT OUTER JOIN t2 table2 ON table1.id = table2.id
LEFT OUTER JOIN t3 table3 ON table2.id = table3.id
LEFT OUTER JOIN t4 table4 ON table3.id = table4.id
WHERE table1.id = 2;

• SQL Server仅更新t1中id=2的1行数据（v设为0）
• Snowflake却将t1中全部5行的v字段都设为0

原因分析 #

Snowflake的UPDATE FROM语法逻辑与SQL Server不同：当目标表（t1）和FROM子句中的表没有显式关联条件时，Snowflake会将目标表的所有行与FROM子句过滤后的结果集进行笛卡尔积匹配。原语句中WHERE table1.id=2仅过滤了FROM子句中的table1行，但目标表t1和table1之间没有关联，导致目标表的每一行都匹配到FROM结果集中的那一行，最终所有行被更新。

解决方案 #

需要在语句中显式关联目标表t1与FROM子句中的table1，确保只有匹配的行才会被更新。以下是几种可行的写法：

写法1：在WHERE子句中添加关联条件 #

UPDATE t1
SET v = 0
FROM t1 table1
LEFT OUTER JOIN t2 table2 ON table1.id = table2.id
LEFT OUTER JOIN t3 table3 ON table2.id = table3.id
LEFT OUTER JOIN t4 table4 ON table3.id = table4.id
WHERE t1.id = table1.id -- 显式关联目标表与FROM中的t1别名
AND table1.id = 2;

写法2：直接过滤目标表并关联 #

UPDATE t1
SET v = 0
FROM t1 table1
LEFT OUTER JOIN t2 table2 ON table1.id = table2.id
LEFT OUTER JOIN t3 table3 ON table2.id = table3.id
LEFT OUTER JOIN t4 table4 ON table3.id = table4.id
WHERE t1.id = 2 -- 直接过滤目标表
AND t1.id = table1.id;

写法3：使用USING子句简化关联 #

UPDATE t1
SET v = 0
USING t1 table1
LEFT OUTER JOIN t2 table2 ON table1.id = table2.id
LEFT OUTER JOIN t3 table3 ON table2.id = table3.id
LEFT OUTER JOIN t4 table4 ON table3.id = table4.id
WHERE table1.id = 2
AND t1.id = table1.id;

执行以上任意语句后，Snowflake将仅更新t1中id=2的1行数据，与SQL Server的行为一致。

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