Внешние соединения и JOIN с группировкой

Когда пары может не быть: внешние соединения

INNER JOIN выбрасывает строки без пары. Но иногда нужно сохранить все строки одной из таблиц, даже если совпадения нет. Для этого есть внешние соединения.

• LEFT JOIN — все строки левой таблицы; если пары справа нет, столбцы правой будут NULL.
• RIGHT JOIN — зеркально: все строки правой таблицы.
• FULL JOIN — все строки обеих таблиц.

Пример: не у каждого самолёта в базе расписаны места. Чтобы увидеть все самолёты, в том числе без мест, берём LEFT JOIN и ищем те, где правая таблица пустая:

SELECT a.airplane_code, a.model
FROM airplanes a
LEFT JOIN seats s ON s.airplane_code = a.airplane_code
WHERE s.seat_no IS NULL;     -- самолёты, у которых нет мест

JOIN + GROUP BY — самые полезные отчёты

Соединение часто сочетают с группировкой: «сколько маршрутов у каждой модели», «сколько мест каждого класса». Сначала JOIN собирает строки, затем GROUP BY группирует, и считается агрегат:

SELECT a.model, COUNT(*) AS routes
FROM routes r
JOIN airplanes a ON a.airplane_code = r.airplane_code
GROUP BY a.model
ORDER BY routes DESC;

Типичные ошибки

• Условие на правую таблицу в WHERE. В airplanes a LEFT JOIN seats s … WHERE s.fare_conditions = 'Business' самолёты без мест пропадут: у них s.fare_conditions равен NULL, и WHERE их отбросит. Если такие строки нужны, условие переносят в ON: LEFT JOIN seats s ON s.airplane_code = a.airplane_code AND s.fare_conditions = 'Business'.
• COUNT(*) после LEFT JOIN. Для самолёта без мест COUNT(*) даст 1 — он посчитает строку с NULL. Чтобы получить 0, считайте столбец правой таблицы: COUNT(s.seat_no).
• Порядок таблиц в LEFT JOIN. Сохраняются строки той таблицы, что стоит слева. seats LEFT JOIN airplanes и airplanes LEFT JOIN seats — разные запросы.

💡 Тонкость с LEFT JOIN: COUNT(*) считает все строки (даже с NULL от несостоявшейся пары), а COUNT(s.seat_no) — только строки, где s.seat_no не NULL. Поэтому для «сколько реально мест у модели» берут COUNT(s.seat_no) — у самолёта без мест получится 0.