Difference between two SQL Server queries?-CodePudding

I want to know the difference between two SQL Server queries?

Tables:

What I am want to do is that join the above tables and apply filters. One of such filter is based on another table.

Query#1

UPDATE STORE_ITEM
SET user_bit_5 = '1'
FROM STORE_ITEM
     INNER JOIN tag_demand ON STORE_ITEM.item_id = tag_demand.item_id
                          AND STORE_ITEM.store_nbr = tag_demand.store_nbr
WHERE tag_demand.print_dt = '2022-04-11'
  AND tag_demand.STORE_NBR IN (SELECT store_nbr FROM store WHERE division_id = 'XYZ')
  AND user_bit_5 = '0';

Query#2

UPDATE STORE_ITEM
SET user_bit_5 = '1'
FROM STORE_ITEM
     INNER JOIN tag_demand ON STORE_ITEM.item_id = tag_demand.item_id
                          AND STORE_ITEM.store_nbr = tag_demand.store_nbr
                          AND tag_demand.print_dt = '2022-04-11'
                          AND tag_demand.STORE_NBR IN (SELECT store_nbr FROM store WHERE division_id = 'XYZ')
WHERE user_bit_5 = '0';

First query has more conditions on the join while the second query has them on the where clause.

CodePudding user response：

No differences in the particular queries you showed us. SQL Server's query planner will probably use similar, if not identical, operations to satisfy both.

Still, your second query pattern, when used with LEFT JOIN, and with SELECT not UPDATE, is quite common. With LEFT JOIN a query like your second one would always return at least one row from STORE_ITEM, even if it found no matching rows from tag_demand.

(If you use LEFT JOIN instead of ordinary INNER JOIN in your second query, you will update all rows in your STORE_ITEM table.)