Причина
- Условие в WHERE: `WHERE o.amount > $100$` убирает все строки, где `o` = NULL. Это превращает `LEFT JOIN` в эффективный `INNER JOIN` — пользователи без заказов или с заказами ≤ $100$ исчезают из результата. Поэтому результат кажется «неочевидным».
Корректные варианты запроса (в зависимости от цели)
1) Если нужно вывести всех пользователей и посчитать для каждого число заказов с amount > $100$ (показывать $0$ при отсутствии таких заказов):
SELECT u.name, COUNT(o.id) AS cnt
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id AND o.amount > $100$ GROUP BY u.name;
2) Альтернатива через условную агрегацию (можно оставить условие в ON или в WHERE):
SELECT u.name, COUNT(CASE WHEN o.amount > $100$ THEN 1 END) AS cnt
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id
GROUP BY u.name;
3) Если нужно только тех пользователей, у которых есть заказы с amount > $100$ (т.е. исключить остальных):
SELECT u.name, COUNT(o.id) AS cnt
FROM Users u
JOIN Orders o ON u.id = o.user_id
WHERE o.amount > $100$ GROUP BY u.name;
Замечание по COUNT
- Для вариантов с LEFT JOIN используйте `COUNT(o.id)` или `COUNT( CASE ... )`, а не `COUNT(*)`, чтобы не считать строку с NULL из правой таблицы как $1$.
Оптимизация индексов для больших объёмов
- Подумайте о composite-индексе, покрывающем и условие, и JOIN:
- Если фильтр по `o.amount > $100$` очень селективен (малый набор строк), полезен индекс `(amount, user_id)`:
CREATE INDEX idx_orders_amount_user ON Orders(amount, user_id);
- Если чаще выполняются по-юзеровые выборки/джойны, лучше `(user_id, amount)`:
CREATE INDEX idx_orders_user_amount ON Orders(user_id, amount);
- Индекс должен быть «покрывающим» для запроса, чтобы избежать лишних чтений строк (если нужны только `user_id` и `amount`).
- Всегда проверяйте план выполнения (`EXPLAIN`) и статистику селективности; при экстремальных объёмах рассмотрите партицирование по диапазонам amount или предагрегированные таблицы/материализованные представления.