BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Who bought Earbuds but never a Charger?

Which customers bought Earbuds but never a Charger? It's the "never" question again, with a filter on each side: the customers who bought Earbuds, minus the customers who bought a Charger. A common slip is product <> 'Charger'. That checks one row at a time, so a customer with both still gets through on their Earbuds row.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

A but not B

SELECT DISTINCT customer_id
FROM orders
WHERE product = 'Earbuds'
  AND customer_id NOT IN (SELECT customer_id FROM orders WHERE product = 'Charger')

Result:

customer_id
C101
C103

Step by step

  1. One column, customer_id, no repeats.
    SELECT DISTINCT customer_id FROM orders WHERE product = 'Earbuds'   AND customer_id NOT IN (SELECT customer_id FROM orders WHERE product = 'Charger')
  2. Customer and product are both in orders.
    SELECT DISTINCT customer_id FROM orders WHERE product = 'Earbuds'   AND customer_id NOT IN (SELECT customer_id FROM orders WHERE product = 'Charger')
  3. WHERE product = 'Earbuds' outside, WHERE product = 'Charger' inside the brackets.
    SELECT DISTINCT customer_id FROM orders WHERE product = 'Earbuds'   AND customer_id NOT IN (SELECT customer_id FROM orders WHERE product = 'Charger')
  4. The Charger buyers' list goes in brackets.
    SELECT DISTINCT customer_id FROM orders WHERE product = 'Earbuds'   AND customer_id NOT IN (SELECT customer_id FROM orders WHERE product = 'Charger')

Practice

Try each one in your head first, then open the answer.

1. Which customers bought Earbuds but never a Charger? Show the customer_id.
SELECT DISTINCT customer_id FROM orders WHERE product = 'Earbuds' AND customer_id NOT IN (SELECT customer_id FROM orders WHERE product = 'Charger')
2. Which customers ordered in March but not in February? Show the customer_id.
SELECT DISTINCT customer_id FROM orders WHERE order_date >= '2026-03-01' AND customer_id NOT IN (SELECT customer_id FROM orders WHERE order_date < '2026-03-01')
3. Which stores sold Earbuds but never a Speaker?
SELECT DISTINCT store FROM orders WHERE product = 'Earbuds' AND store NOT IN (SELECT store FROM orders WHERE product = 'Speaker')
4. Which products were sold in Lagos but never in Accra?
SELECT DISTINCT product FROM orders WHERE store = 'Lagos' AND product NOT IN (SELECT product FROM orders WHERE store = 'Accra')
5. Which customers used a coupon, but never the NEW10 coupon? Show the customer_id.
SELECT DISTINCT customer_id FROM orders WHERE coupon IS NOT NULL AND customer_id NOT IN (SELECT customer_id FROM orders WHERE coupon = 'NEW10')

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed