含IN子查询的UPDATE语句优化：自研替代写法是否更优？

首先你的替代语句存在语法错误：UPDATE语句中没有给table1定义别名t1，所以不能直接使用t1.col2='val2'，正确的写法应该去掉t1.，写成and col2='val2'。

修正后的替代语句是否更优？ #

是的，修正后的语句比原语句更高效。原语句的子查询里将table2和table1做了内连接，相当于在子查询中就对table1做了一次过滤；而修正后的语句直接在UPDATE的WHERE条件里过滤table1的col2，避免了子查询中不必要的表关联，减少了数据库需要处理的关联逻辑，通常会带来更好的执行效率，尤其是当table1数据量较大时。

更好的写法推荐 #

1. 使用EXISTS子查询 #

相比IN子查询，EXISTS在多数数据库（如MySQL、PostgreSQL）中执行效率更稳定，尤其是当子查询返回的结果集较大时：

update table1 
set col1='val' 
where col2='val2'
and exists (
    select 1 
    from table2 t2 
    where t2.id = table1.id 
    and t2.col3='val3'
);

2. 使用JOIN方式更新 #

很多数据库支持直接通过JOIN完成UPDATE操作，这种写法逻辑更直观，执行效率通常也不错：

update table1 t1
inner join table2 t2 on t1.id = t2.id
set t1.col1='val'
where t1.col2='val2' 
and t2.col3='val3';

注意：不同数据库的UPDATE JOIN语法略有差异，比如SQL Server需要用UPDATE ... FROM ... JOIN写法，Oracle则使用MERGE语句，但上述写法在MySQL、PostgreSQL中可直接使用。

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