Подводные камни соединений

Соединения мощны, и потому их ошибки опасны: большинство из них не вызывает ошибки, а лишь даёт неверный результат, который выглядит правдоподобно. Вот три самые частые.

1. Забытое ON. SQLite позволяет написать JOIN без условия. Тогда ничто не говорит, какие строки подходят друг другу, и он сопоставляет каждую строку левой таблицы с каждой строкой правой:

SELECT COUNT(*) AS row_count
FROM customers AS c
JOIN orders AS o;

14 клиентов умножить на 30 заказов: 420 строк, все возможные сочетания. Такое соединение называется перекрёстным (cross join). Иногда это именно то, что нужно (скажем, каждый клиент с каждой игрой), но обычно это забытое ON. Если в результате строк намного больше, чем вы ожидали, проверьте ON в каждом соединении. Вам может встретиться и более старый способ соединения: таблицы через запятую, а условие в WHERE: FROM customers c, orders o WHERE o.customer_id = c.id. Забудьте там WHERE — и получите те же 420 строк.

2. Размножение строк. Соединение даёт строку для каждой пары. Если к играм одновременно соединить две таблицы, например отзывы и позиции заказов, каждый отзыв об игре сопоставится с каждой её позицией. У Dragon Forge 4 отзыва, и она входила в 4 заказа:

SELECT g.title, COUNT(r.stars) AS review_count, SUM(i.quantity) AS copies
FROM games AS g
JOIN reviews AS r ON r.game_id = g.id
JOIN order_items AS i ON i.game_id = g.id
WHERE g.id = 1
GROUP BY g.id, g.title;

16 отзывов и 16 экземпляров — в четыре раза больше, чем нужно: каждый из 4 отзывов встретился с каждой из 4 позиций, а это 16 строк. Ничто не подсказывает, что что-то не так; числа просто слишком велики. Подсчёт можно спасти с помощью COUNT(DISTINCT …) от ключа: каждый отзыв принадлежит одному клиенту (ключ reviews — игра и клиент), поэтому COUNT(DISTINCT r.customer_id) считает каждый отзыв один раз, сколько бы раз ни повторялась его строка. Сумму так не спасти, потому что у двух разных позиций может быть одинаковое количество. Получите её отдельным запросом или с помощью подзапроса, о котором вы узнаете в следующем уроке.

3. Условие в ON или в WHERE. С JOIN не важно, где стоит условие: в ON или в WHERE. С LEFT JOIN — важно. Сколько пятизвёздочных отзывов у каждой игры?

SELECT g.title, COUNT(r.stars) AS five_stars
FROM games AS g
LEFT JOIN reviews AS r ON r.game_id = g.id
WHERE r.stars = 5
GROUP BY g.id, g.title
ORDER BY g.title;

Игр без пятизвёздочного отзыва в результате нет, хотя именно для них и нужен был LEFT JOIN. WHERE выполняется после соединения: строки с NULL и строки других отзывов не проходят проверку r.stars = 5, и WHERE выбрасывает их вместе с играми. Условие должно стоять в ON:

SELECT g.title, COUNT(r.stars) AS five_stars
FROM games AS g
LEFT JOIN reviews AS r ON r.game_id = g.id AND r.stars = 5
GROUP BY g.id, g.title
ORDER BY g.title;

Условие в ON решает, какие строки правой таблицы считаются парой, а LEFT JOIN в любом случае сохраняет каждую строку левой таблицы: игра без пятизвёздочного отзыва остаётся, с NULL, и COUNT даёт ей 0. Поэтому в LEFT JOIN условие на правую таблицу ставят в ON, а условие на левую таблицу — в WHERE.