ITPEC FE Morning October 2019 Question 29

A correlated NOT EXISTS subquery on Inventory — both queries return the products that appear in no inventory row.

The original keeps a Product row when its ProductNumber is not among the product numbers in Inventory. A correlated subquery asks the same question row by row: for each Product row, NOT EXISTS looks for an Inventory row with Product.ProductNumber = Inventory.ProductNumber and keeps the product only if none is found.

The two forms can differ when the subquery returns NULL, but Inventory.ProductNumber is part of the primary key, so it is never NULL here.

Answer (d)

Why not others:

• •(a) — the subquery is not correlated and reads Product itself, so EXISTS is true for every row while the table is not empty: all products are returned
• •(b) — it reads from Inventory, the wrong table, and its uncorrelated NOT EXISTS on a non-empty Product is always false, so it returns nothing
• •(c) — correlated correctly but with EXISTS, so it returns the products that do have inventory, the opposite set (the equivalent of IN)

Key rule: To rewrite IN or NOT IN as EXISTS or NOT EXISTS, move the compared column into a join condition inside the subquery that refers to the outer table.

Databases · AI-written, may contain errors