An interviewer asks: "Find the customers who bought the Mug but never the Lamp." A WHERE on the joined rows can't answer it: it sees one row at a time, and the Lamp sits in some other row, maybe in another order. You need a test about all of a customer's rows.
EXISTS (subquery) is true when the subquery finds at least one row, and NOT EXISTS when it finds none. The subquery can use the outer query's row. For example, the products nobody has ordered yet:
SELECT name
FROM products
WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE product_id = products.id)
A second way works on whole results: A EXCEPT B keeps the rows of A that B doesn't return, each only once. The bought CTE is given: one row per product line, with customer_id, name and product_id. The Mug is product 1 and the Lamp is product 3. The check adds a June order with a Mug for Dan Weber, who also bought the Lamp. Both queries must print:
name Ana Pop Chloe Ng (2 rows) name Ana Pop Chloe Ng (2 rows)
Your task: finish query with an EXISTS for the Mug and a NOT EXISTS for the Lamp, then finish except_query with EXCEPT and the Lamp select.
Assignment
Follow the Coding Tutorial to finish both queries, one clause per line.
Hint
Both subqueries read bought and match customer_id = customers.id, so each customer is checked against their own rows; only the product id changes.
Free plan: 30 tutor messages. PRO is unlimited.
