From a customer table (cust_id, name) and an orders table (order_id, cust_id, amount), you want the names of customers who have never placed an order. Which SQL statement is correct?

Answer: C. SELECT name FROM customer WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.cust_id = customer.cust_id)

EXISTS is a predicate that is true if the subquery in parentheses returns at least one row and false if it returns none; NOT EXISTS is the opposite. Using a column of the outer table inside the subquery, as in orders.cust_id = customer.cust_id, makes it a correlated subquery that checks, for each row of the outer customer table, whether that customer has any orders. Combined with NOT EXISTS, only customers with no orders at all remain. With EXISTS, you get the opposite: customers who have ordered. Without a condition linking the subquery to the customer, the subquery always returns rows as long as the orders table has at least one row, so NOT EXISTS is always false and the result is zero rows. The same result can also be obtained with a LEFT OUTER JOIN, selecting the rows where the orders-side columns are NULL.

• A. This uses EXISTS, so it finds customers who have placed orders.
• B. The subquery has no per-customer condition, so if the orders table has even one row, the result is zero rows.
• C. Correct. It checks for each customer whether any orders exist and keeps only customers with none.