Query Optimization for Correlated Nested Queries (SQL Server)

Question

Is it better or worse for performance to have a WHERE clause in your correlated nested queries or just a WHERE clause in your outer query... ex: The below nested query defines the price_key, but the outer query has references to a price_key and is also defined in the outer queries WHERE statement

INNER JOIN (
            SELECT inv.item_key, s.price_key, MAX(inv.last_receive_cost / inv.last_receive_units_case) AS [unit_cost]
            FROM inventory inv
                  INNER JOIN stores s on inv.location_key = s.store_key
            WHERE inv.on_hand_inventory_qty > 0
                  AND inv.last_receive_cost > 0
                  AND inv.last_receive_units_case > 0
                  AND s.price_key = (29)
            GROUP BY inv.item_key, s.price_key
) t ON r.item_key = t.item_key and r.price_key = t.price_key

Answer 1

INNER JOIN (
            SELECT inv.item_key --, s.price_key
                 , MAX(inv.last_receive_cost / inv.last_receive_units_case) AS [unit_cost]
            FROM inventory inv
                  INNER JOIN stores s on inv.location_key = s.store_key
            WHERE inv.on_hand_inventory_qty > 0
                  AND inv.last_receive_cost > 0
                  AND inv.last_receive_units_case > 0
                  AND s.price_key = 29
            GROUP BY inv.item_key 
-- , s.price_key
) t   ON r.item_key = t.item_key 
     and r.price_key = 29