Alvin Santos Alvin Santos - 1 month ago 10
SQL Question

SQL Server Query - Which is better in performance?

I think query 1 is more readable than query 2, but I'd like to know if there is any difference between them in terms of performance?

Query 1

UPDATE table_1
SET col_1 =
CASE WHEN table_1.col_pk IS NULL THEN value1
ELSE value2
END
FROM table_1
LEFT JOIN table_2 ON table_1.col_pk = table_2.col_pk


or

Query 2

UPDATE table_1
SET col_1 = value1
FROM table_1
LEFT JOIN table_2 ON table_1.col_pk = table_2.col_pk
WHERE table_1.col_pk IS NULL

UPDATE table_1
SET col_1 = value2
FROM table_1
INNER JOIN table_2 ON table_1.col_pk = table_2.col_pk

Answer

The first query will probably have better performance because it requires only a single scan of table_1 and table_2.