A customer table (cust_id, name) has four rows: (C1, Aoki), (C2, Inoue), (C3, Ueda), (C4, Eto). An orders table (order_id, cust_id, amount) has five rows: (O1, C1, 3000), (O2, C2, 8000), (O3, C1, 6000), (O4, C3, 2000), (O5, C2, 9000). How many rows does SELECT name FROM customer WHERE cust_id IN (SELECT cust_id FROM orders WHERE amount > 5000) return?

Answer: B. 2 rows

First, the subquery in parentheses returns the cust_id of orders whose amount exceeds 5000. These are O2 (C2), O3 (C1) and O5 (C2), so the set of cust_id values is C2, C1, C2. The outer query checks, for each row of the customer table, whether its cust_id is in this set. Aoki (C1) and Inoue (C2) are, and although Inoue appears twice in the set, he is returned only once as one row of the customer table, so the result has two rows. Three rows is the count if one row is produced for every matching order, as when the customer and orders tables are joined and then filtered by amount. One row is the count of only the customers whose every order exceeds 5000 (Inoue).

• A. This counts only customers whose every order exceeds 5000. IN includes a customer with even one matching order.
• B. Correct. Aoki and Inoue, who have orders above 5000, give two rows.
• C. This is the count when a join produces one row per order. With IN, the same customer is returned only once.