ITPEC FE Morning October 2019 Question 29

NOT IN vs NOT EXISTS — rewrite a NOT IN subquery using NOT EXISTS.

Original query finds products NOT present in Inventory:

SELECT ProductNumber FROM Product WHERE ProductNumber NOT IN (SELECT ProductNumber FROM Inventory)

Why (d):
- NOT EXISTS with a correlated subquery checks each Product row against Inventory
- If no matching Inventory.ProductNumber exists → row is included
- Logically identical to NOT IN

Why not others:
- (a) — EXISTS without correlation → always TRUE if Product is non-empty → returns all rows
- (b) — selects from Inventory, not Product; uncorrelated NOT EXISTS on Product → returns empty if Product has rows
- (c) — EXISTS with correlation → finds products that are in Inventory (opposite logic, equivalent to IN)

Key rule: NOT IN (subquery) ≡ NOT EXISTS (correlated subquery with equality condition). The subquery must reference the outer table to be correlated.

AI-generated — may contain errors