Клиенты, покупающие один продукт, но не другой

Решение

Задача требует нахождения клиентов, которые совершили определённую покупку (Gel), но не совершили другую (Soap). Это классическая задача на использование подзапросов и логики исключения. Существует несколько эффективных подходов.

Вариант 1: NOT EXISTS (рекомендуемый)

SELECT DISTINCT
  c.customer_id,
  c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM purchases p
  WHERE p.customer_id = c.customer_id AND p.product = 'Gel'
)
AND NOT EXISTS (
  SELECT 1 FROM purchases p
  WHERE p.customer_id = c.customer_id AND p.product = 'Soap'
);

Вариант 2: С использованием подзапросов IN/NOT IN

SELECT 
  c.customer_id,
  c.name
FROM customers c
WHERE c.customer_id IN (
  SELECT DISTINCT customer_id FROM purchases WHERE product = 'Gel'
)
AND c.customer_id NOT IN (
  SELECT DISTINCT customer_id FROM purchases WHERE product = 'Soap'
);

Вариант 3: С GROUP BY и HAVING

SELECT 
  c.customer_id,
  c.name
FROM customers c
INNER JOIN purchases p ON c.customer_id = p.customer_id
WHERE p.product IN ('Gel', 'Soap')
GROUP BY c.customer_id, c.name
HAVING SUM(CASE WHEN p.product = 'Gel' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN p.product = 'Soap' THEN 1 ELSE 0 END) = 0;

Вариант 4: С левым соединением (LEFT JOIN)

SELECT DISTINCT
  c.customer_id,
  c.name
FROM customers c
INNER JOIN purchases p_gel ON c.customer_id = p_gel.customer_id AND p_gel.product = 'Gel'
LEFT JOIN purchases p_soap ON c.customer_id = p_soap.customer_id AND p_soap.product = 'Soap'
WHERE p_soap.purchase_id IS NULL;

Вариант 5: С использованием EXCEPT (PostgreSQL, MySQL 8.0+)

(
  SELECT DISTINCT c.customer_id, c.name
  FROM customers c
  INNER JOIN purchases p ON c.customer_id = p.customer_id
  WHERE p.product = 'Gel'
)
EXCEPT
(
  SELECT DISTINCT c.customer_id, c.name
  FROM customers c
  INNER JOIN purchases p ON c.customer_id = p.customer_id
  WHERE p.product = 'Soap'
)
ORDER BY customer_id;

Пошаговое объяснение (Вариант 1: NOT EXISTS)

1. EXISTS (покупает Gel)

• EXISTS (SELECT 1 FROM purchases WHERE customer_id = c.customer_id AND product = 'Gel')
• Проверяет наличие хотя бы одной покупки Gel для клиента
• Возвращает TRUE если есть, FALSE если нет

2. NOT EXISTS (не покупает Soap)

• NOT EXISTS (SELECT 1 FROM purchases WHERE customer_id = c.customer_id AND product = 'Soap')
• Проверяет отсутствие покупок Soap
• Возвращает TRUE если нет, FALSE если есть

3. AND логика

• Обе условия должны быть TRUE (покупает Gel И не покупает Soap)

Пример выполнения

Исходные данные:

customers:
ID | name
---|-------
1  | Иван
2  | Мария
3  | Пётр

purchases:
ID | customer_id | product
---|-------------|--------
1  | 1           | Gel
2  | 1           | Soap
3  | 2           | Gel
4  | 3           | Soap

Анализ каждого клиента:

• Иван (ID=1): Купил Gel ✓, Купил Soap ✗ → НЕ включаем (требуется НЕ покупать Soap)
• Мария (ID=2): Купила Gel ✓, НЕ купила Soap ✓ → ВКЛЮЧАЕМ
• Пётр (ID=3): НЕ купил Gel ✗, Купил Soap ✓ → НЕ включаем (требуется купить Gel)

Результат:

customer_id | name
------------|------
2           | Мария

Сравнение производительности вариантов

Важные моменты

✓ Выбор варианта — NOT EXISTS обычно самый оптимальный (не требует сортировки и группировки)

✓ NULL значения — IN/NOT IN могут вести себя неожиданно при наличии NULL. Используй NOT IN с осторожностью

✓ DISTINCT — может потребоваться, если один клиент купил один продукт несколько раз

✓ Производительность на больших данных — NOT EXISTS, как правило, быстрее, чем LEFT JOIN с WHERE IS NULL

✓ Обратная логика — если нужны клиенты, покупающие Soap но НЕ покупающие Gel, поменяй условия

✓ Множественные условия — для более сложных условий (например, три продукта) нужно добавить дополнительные EXISTS или NOT EXISTS

Расширенный вариант для анализа

SELECT 
  c.customer_id,
  c.name,
  COUNT(DISTINCT CASE WHEN p.product = 'Gel' THEN p.purchase_id END) AS gel_purchases,
  COUNT(DISTINCT CASE WHEN p.product = 'Soap' THEN p.purchase_id END) AS soap_purchases,
  COUNT(DISTINCT p.product) AS unique_products
FROM customers c
LEFT JOIN purchases p ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT CASE WHEN p.product = 'Gel' THEN p.purchase_id END) > 0
AND COUNT(DISTINCT CASE WHEN p.product = 'Soap' THEN p.purchase_id END) = 0
ORDER BY c.customer_id;

Этот вариант дополнительно показывает количество покупок каждого типа.