JOIN: все виды и практика

JOIN: все виды и практика

Главная идея

Как это выглядит на практике

1. INNER JOIN — только строки с совпадением в обеих таблицах. SELECT u.full_name, o.id AS order_id, o.total_amount FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed'. Результат: только пользователи которые сделали хотя бы один выполненный заказ. Пользователи без заказов не попадают в результат.
2. LEFT JOIN — все строки левой таблицы + совпадения из правой. SELECT u.full_name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.full_name ORDER BY order_count DESC. Пользователи без заказов попадут с order_count = 0 (COUNT(o.id) = 0 при NULL). Если бы был INNER JOIN — таких пользователей не было бы.
3. Антисоединение — LEFT JOIN + WHERE right IS NULL: SELECT u.id, u.full_name FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL. Возвращает пользователей БЕЗ заказов. Это эффективнее чем NOT IN или NOT EXISTS в большинстве случаев.
4. Несколько JOIN последовательно: SELECT o.id, u.email, u.full_name, p.name AS product, oi.quantity, oi.price_at_purchase FROM orders o JOIN users u ON o.user_id = u.id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id WHERE o.status = 'pending' ORDER BY o.created_at DESC. Четыре таблицы соединяются последовательно — получаем полную информацию о заказе.
5. Self-join для иерархии: SELECT e.full_name AS employee, m.full_name AS manager, m.full_name IS NULL AS is_top_manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id ORDER BY m.full_name NULLS FIRST, e.full_name. Таблица соединяется сама с собой: e — сотрудники, m — их менеджеры. LEFT JOIN сохраняет топ-менеджеров (у которых нет manager_id).
6. FULL OUTER JOIN для сравнения двух наборов: SELECT COALESCE(a.category, b.category) AS category, a.count_2023, b.count_2024 FROM (SELECT category, COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2023 GROUP BY category) a FULL OUTER JOIN (SELECT category, COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024 GROUP BY category) b ON a.category = b.category. Показывает категории из обоих годов, включая те что были только в одном.
7. JOIN с агрегацией — подсчёт связанных записей: SELECT p.id, p.name, COUNT(r.id) AS review_count, ROUND(AVG(r.rating), 2) AS avg_rating FROM products p LEFT JOIN reviews r ON r.product_id = p.id GROUP BY p.id, p.name HAVING AVG(r.rating) >= 4 OR COUNT(r.id) = 0 ORDER BY avg_rating DESC NULLS LAST.
8. LATERAL JOIN — для 'последнего заказа каждого пользователя': SELECT u.id, u.full_name, last_order.id AS last_order_id, last_order.created_at FROM users u LEFT JOIN LATERAL (SELECT id, created_at, total_amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1) AS last_order ON true. LATERAL выполняет подзапрос для каждой строки users.
9. JOIN без ON (неявный CROSS JOIN) — декартово произведение: SELECT d.name AS day, t.name AS time_slot FROM days d CROSS JOIN time_slots t ORDER BY d.id, t.id. Если days = 7 строк, time_slots = 8 строк — результат 56 строк. Используется для генерации расписаний, матриц, всех комбинаций.
10. N+1 проблема и её устранение: Django ORM без .select_related() делает SELECT * FROM posts, потом для каждого поста SELECT * FROM users WHERE id = post.author_id. 101 запрос вместо одного. Решение в Django: Post.objects.select_related('author') — добавляет JOIN. В SQL: SELECT posts.*, users.full_name FROM posts JOIN users ON posts.author_id = users.id.
11. Влияние индексов на план JOIN: Nested Loop использует индекс на inner table — без индекса на orders.user_id при JOIN с users каждая строка users вызывает seq scan orders. Hash Join строит хеш-таблицу из меньшей таблицы в work_mem — эффективен для больших таблиц без индексов. EXPLAIN ANALYZE показывает тип JOIN: 'Hash Join', 'Nested Loop', 'Merge Join'.
12. Условия фильтрации в ON vs WHERE: при LEFT JOIN условие в WHERE исключает строки после JOIN (превращает LEFT в INNER!). SELECT u.*, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'. Условие status в ON оставляет всех пользователей, у незаполненных заказов o.total = NULL. В WHERE: WHERE o.status = 'completed' отфильтрует пользователей без completed заказов — станет INNER JOIN де-факто.

Примеры кода

Все типы JOIN на одном примере

-- Тестовые данные
-- users: Alice(1), Bob(2), Charlie(3)
-- orders: заказы для Alice(1→u1), Alice(2→u1), Bob(3→u2)
-- Charlie не делал заказов

-- INNER JOIN: только пары где оба существуют
SELECT u.full_name, o.id AS order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Alice | 1
-- Alice | 2
-- Bob   | 3
-- Charlie: отсутствует (нет заказов)

-- LEFT JOIN: все пользователи + их заказы
SELECT u.full_name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Alice   | 1
-- Alice   | 2
-- Bob     | 3
-- Charlie | NULL  ← без заказа, но в результате

-- Антисоединение: пользователи БЕЗ заказов
SELECT u.full_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- Charlie

-- FULL OUTER JOIN: все строки обеих таблиц
SELECT u.full_name, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Alice   | 1
-- Alice   | 2
-- Bob     | 3
-- Charlie | NULL

JOIN с агрегацией — реальные запросы для дашборда

-- Статистика пользователей: количество и сумма заказов
SELECT
  u.id,
  u.full_name,
  u.email,
  COUNT(o.id)          AS total_orders,
  COALESCE(SUM(o.total_amount), 0) AS total_spent,
  MAX(o.created_at)    AS last_order_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
  AND o.status != 'cancelled'
WHERE u.is_active = true
GROUP BY u.id, u.full_name, u.email
HAVING COUNT(o.id) > 0 OR u.created_at > now() - interval '7 days'
ORDER BY total_spent DESC
LIMIT 50;

-- Продукты с количеством отзывов и средним рейтингом
SELECT
  p.id,
  p.name,
  p.price,
  COUNT(r.id)                    AS review_count,
  ROUND(AVG(r.rating)::NUMERIC, 2) AS avg_rating,
  COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star_count
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name, p.price
ORDER BY avg_rating DESC NULLS LAST, review_count DESC;

N+1 проблема и JOIN-решение

-- ПРОБЛЕМА: N+1 запросов в коде приложения
-- Псевдокод: 1 + N запросов
-- articles = SELECT * FROM articles LIMIT 20;  -- 1 запрос
-- for article in articles:
--   author = SELECT * FROM users WHERE id = article.author_id  -- 20 запросов!
-- Итого: 21 запрос вместо 1

-- РЕШЕНИЕ: один JOIN вместо N+1
SELECT
  a.id,
  a.title,
  a.published_at,
  u.id         AS author_id,
  u.full_name  AS author_name,
  u.avatar_url AS author_avatar
FROM articles a
JOIN users u ON u.id = a.author_id
WHERE a.is_published = true
ORDER BY a.published_at DESC
LIMIT 20;

-- Для нескольких связей — несколько JOIN
SELECT
  a.id, a.title,
  u.full_name                         AS author,
  COUNT(c.id)                         AS comment_count,
  COUNT(l.id)                         AS like_count,
  MAX(c.created_at)                   AS last_comment_at
FROM articles a
JOIN users u   ON u.id = a.author_id
LEFT JOIN comments c ON c.article_id = a.id
LEFT JOIN likes    l ON l.article_id = a.id
WHERE a.is_published = true
GROUP BY a.id, a.title, u.full_name
ORDER BY a.published_at DESC
LIMIT 20;

Что происходит под капотом

• PostgreSQL имеет три алгоритма JOIN: Nested Loop (перебор каждой строки внешней таблицы с поиском в внутренней через индекс; эффективен при маленькой внутренней таблице или хорошем индексе), Hash Join (хеш-таблица из меньшей таблицы в work_mem, scan по большей; эффективен при больших таблицах без индексов), Merge Join (обе стороны отсортированы по ключу JOIN; эффективен когда оба side уже отсортированы или их сортировка дешева). Планировщик выбирает алгоритм на основе статистики размеров и наличия индексов.
• Nested Loop + Index Scan: для каждой строки из внешней таблицы PostgreSQL делает Index Scan по внутренней. Стоимость: O(outer_rows * log(inner_rows)). Это лучший алгоритм если outer_rows маленький (например, WHERE user_id = 42 возвращает 1 строку) или если есть хороший индекс на inner таблице. Без индекса Nested Loop становится O(outer * inner) — очень медленно.
• Hash Join: строит хеш-таблицу из меньшей таблицы в work_mem (размер хеш-таблицы ограничен work_mem). Затем сканирует большую таблицу и делает хеш-lookup. Стоимость: O(N + M). Если хеш-таблица не помещается в work_mem — spill на диск (Batches > 1 в EXPLAIN). Увеличение work_mem SET work_mem = '256MB' может избежать spill.
• Порядок JOIN: PostgreSQL переставляет JOIN в оптимальном порядке (join reordering). Для > 8 JOIN-ов (join_collapse_limit = 8) планировщик использует эвристики. join_collapse_limit = 1 отключает переупорядочивание — полезно для отладки. geqo_threshold — порог для генетического алгоритма оптимизации при очень многих JOIN.
• ON vs WHERE для LEFT JOIN: условие в ON применяется до JOIN (фильтрует строки правой таблицы перед соединением), условие в WHERE применяется после JOIN (к результирующим строкам). При LEFT JOIN условие на правую таблицу в WHERE превращает его в INNER JOIN де-факто: строки с NULL справа отфильтровываются. Условие в ON оставляет строки с NULL — нужные NULL для 'нет записи'.
• LATERAL JOIN: позволяет подзапросу ссылаться на столбцы из предыдущих таблиц в FROM. JOIN LATERAL (SELECT ... WHERE t.id = outer.id ...) AS sub ON true. PostgreSQL выполняет подзапрос для каждой строки основной таблицы. Это позволяет 'топ-N на группу' запросы без window functions. Аналог APPLY в SQL Server.
• N+1 проблема — когда для каждой строки основного запроса выполняется дополнительный запрос. 100 постов → 100 SELECT для авторов = 101 запрос. Один JOIN заменяет всё это. N+1 невидимо в разработке (данных мало), появляется в продакшене (много данных). Django ORM: .select_related() для FK, .prefetch_related() для M2M. EXPLAIN ANALYZE на объектах ORM — обязательная практика.
• Bitmap Index Scan для JOIN: при Hash Join PostgreSQL иногда делает BitmapAnd/BitmapOr из нескольких Index Scan для одной стороны JOIN — собирает bitmap страниц, потом читает их разом. Это эффективнее Sequential Scan если selectivity высокая, но дешевле Index Scan если selectivity низкая.
• Индекс на FK-столбце дочерней таблицы: без CREATE INDEX ON orders(user_id) JOIN users u JOIN orders o ON u.id = o.user_id будет seq scan по orders для каждого пользователя при Nested Loop. Это частая причина медленных JOIN. Правило: каждый FK требует индекс на дочерней стороне.
• Параллельный Hash Join: PostgreSQL 9.6+ поддерживает параллельные JOIN. При достаточном parallel_workers и без parallel_safe ограничений — Hash Join может выполняться параллельно несколькими workers. В EXPLAIN видно 'Gather' node над Parallel Hash Join. Для аналитических запросов параллелизм может ускорить в 2-8 раз.

Термины урока

Мини-разбор реальной ситуации

Мобильное приложение для чтения статей: лента статей загружается 3 секунды. Backend-разработчик подключает django-debug-toolbar и видит: 1 запрос на список статей + 50 запросов на авторов + 50 запросов на теги + 50 запросов на количество лайков = 151 SQL-запрос для одной страницы. Каждый запрос ~5-20мс, итого 3 секунды. Первое исправление: добавляет .select_related('author') в queryset — убирает 50 N+1 для авторов. Второе: добавляет .prefetch_related('tags') — убирает 50 N+1 для тегов. Третье: для лайков пишет один аннотированный запрос: Article.objects.annotate(like_count=Count('likes')). Результат: 3 запроса вместо 151, время загрузки: 3000мс → 80мс. Урок: мониторинг SQL-запросов в разработке обязателен — каждый N+1 невидим локально, виден только под нагрузкой или с реальными данными.