Write a query to find customers who bought Product A but never bought Product B.

Answer

Find customers whose purchased-product set contains Product A but excludes Product B, using EXISTS and NOT EXISTS together.

Solution

SELECT DISTINCT o.customer_id
FROM Orders o
WHERE EXISTS (
  SELECT 1 FROM Orders a WHERE a.customer_id = o.customer_id AND a.product_id = 'A'
)
AND NOT EXISTS (
  SELECT 1 FROM Orders b WHERE b.customer_id = o.customer_id AND b.product_id = 'B'
);

Step by Step

1. The first EXISTS confirms the customer has at least one order containing Product A.
2. The second condition, NOT EXISTS, confirms that among all of that same customer's orders, none contain Product B.
3. Combining both with AND gives exactly the customers who bought A but never bought B.

Alternative Method: Conditional Aggregation

SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING SUM(CASE WHEN product_id = 'A' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN product_id = 'B' THEN 1 ELSE 0 END) = 0;

This groups all of a customer's orders into one row and counts occurrences of each product directly - some find this more intuitive than nested EXISTS checks, and it also avoids running two separate correlated subqueries per customer.

Key Exam Traps

• A single query that mixes "bought A" and "bought B" in one WHERE with OR/AND on the same row is wrong - since a single order row can't contain both conditions about a customer's entire order history at once; that's precisely why EXISTS/NOT EXISTS (or grouped aggregation) across the customer's full order set is required, not a single-row filter.
• NOT IN with a NULL-containing subquery is a classic trap - if a naive alternative used customer_id NOT IN (SELECT customer_id FROM Orders WHERE product_id = 'B') and any product_id value in that subquery were NULL, the entire NOT IN would return no rows at all for every customer. NOT EXISTS doesn't have this problem, which is exactly why it's the safer default choice.