Is the SQL Server query same as Oracle query?-CodePudding

Since SQL Server does not allow comparison of two columns (e.g., where (store_nbr, item_id)), I am having difficulty comparing the queries, especially when the tables have large amounts of data.

I have 3 tables

I have the below Oracle query

FORCE_TAGS_SQL = """UPDATE /*  dynamic_sampling(0) index(store_item store_item_pk) */ store_item SET user_bit_5 = '1'
                     WHERE (store_nbr, item_id) in (SELECT /*  dynamic_sampling(0) index(tag_demand tag_demand_ix1) */ d.store_nbr, d.item_id
                                                      FROM store s, tag_demand d
                                                     WHERE s.division_id = 'XYZ'
                                                       AND d.store_nbr = s.store_nbr
                                                       AND d.print_dt = TRUNC(TO_DATE('2022-04-13', 'YYYY-MM-DD')))
                       AND user_bit_5 = '0'
                 """

and below is the converted SQL Server query

FORCE_TAGS_SQL = """UPDATE STORE_ITEM SET user_bit_5 = '1'
                     WHERE (store_nbr) in (SELECT d.store_nbr
                                                      FROM STORE s, tag_demand d
                                                     WHERE s.division_id = 'XYZ'
                                                       AND d.store_nbr = s.store_nbr
                                                       AND d.print_dt = CONVERT(DATETIME, CONVERT(DATE, '2022-04-13')))
                       AND (item_id) in (SELECT  d.item_id
                                                      FROM STORE s, tag_demand d
                                                     WHERE s.division_id = 'XYZ'
                                                       AND d.store_nbr = s.store_nbr
                                                       AND d.print_dt = CONVERT(DATETIME, CONVERT(DATE, '2022-04-13')))
                      AND user_bit_5 = '0'
                 """

Is the SQL Server query same as Oracle query?

Update:

UPDATE  store_item SET user_bit_5 = '1'
FROM    store_item item INNER JOIN tag_demand d
ON      d.store_nbr = item.store_nbr AND d.item_id = item.item_id
WHERE   d.store_nbr in ((SELECT s.store_nbr FROM STORE s WHERE s.division_id = 'XYZ')) AND d.print_dt = CONVERT(DATETIME, CONVERT(DATE, '2022-04-13'))

Update#2:

 FORCE_TAGS_SQL = """UPDATE STORE_ITEM SET user_bit_5 = '1'
                                FROM STORE_ITEM
                                inner join (
                                    SELECT d.store_nbr, d.item_id
                                    FROM store s, tag_demand d
                                    WHERE d.store_nbr = s.store_nbr AND s.division_id = 'XYZ' AND d.print_dt = CONVERT(DATETIME, CONVERT(DATE, '2022-04-13'))
                                ) tagdemand
                                on tagdemand.store_nbr = STORE_ITEM.store_nbr
                                AND tagdemand.item_id = STORE_ITEM.item_id
                                where user_bit_5 = '0'
                    """

CodePudding user response：

You probably should just refactor to exists instead of trying to use to subselects for in lists because that will most probably give you a different result.

...
WHERE EXISTS (SELECT *
              FROM ...
              JOIN ...
              WHERE store.store_nbr = store_item.store_nbr
                AND tag_demand.item_id = store_item.item_id
                AND tag_demand.print_dt = ...)

CodePudding user response：

If you want identical logic you can use CONCAT(). I can't tell you how much this will slow the query down.

WHERE (CONCAT(store_nbr, item_id)) in
(SELECT CONCAT(d.store_nbr, d.item_id)
FROM...)

The difference is that your SQL server version is much freer. In a situation where the oracle version will accept (A,1) and (B,2) your SQL server version will also accept (A,2) and (B,1) even if these combinations don't exist in the sub-query.
One simple test would be to check the number of rows returned from the same data. If the SQL server version returns more rows you know that there is a problem.