Раздел 10 — SQL: JOIN, группировка, подзапросы

Нормализация разложила данные по таблицам (раздел 4). Чтобы собрать ответ на вопрос бизнеса, их нужно соединить обратно. Соединение — это декартово произведение с условием отбора.

-- Посты вместе с названием сообщества
SELECT p.title, p.score, c.name AS community
FROM   posts p
JOIN   communities c ON c.id = p.community_id;

-- INNER: слово JOIN без уточнения означает именно его
-- Пользователи и посты, которые они лайкнули
SELECT u.name, p.title, l.liked_at
FROM   likes l
JOIN   users u ON u.id = l.user_id
JOIN   posts p ON p.id = l.post_id
ORDER BY u.name, l.liked_at;

-- LEFT: все пользователи, даже без единого лайка
SELECT u.name, l.post_id
FROM   users u
LEFT JOIN likes l ON l.user_id = u.id
ORDER BY u.name;

Главное применение LEFT JOIN — найти строки без пары. Приём называется антисоединением: соединяем и оставляем те, где справа не нашлось ничего.

-- Пользователи, не поставившие ни одного лайка
SELECT u.name
FROM   users u
LEFT JOIN likes l ON l.user_id = u.id
WHERE  l.id IS NULL;

-- Сообщества без постов
SELECT c.name
FROM   communities c
LEFT JOIN posts p ON p.community_id = c.id
WHERE  p.id IS NULL;

ON или WHERE — для внешних соединений это разные вещи

Для INNER JOIN перенос условия из ON в WHERE ничего не меняет. Для LEFT JOIN — меняет всё.

-- Условие в ON: все пользователи; лайки подтягиваются только сделанные до 6 января
SELECT u.name, l.liked_at
FROM   users u
LEFT JOIN likes l
       ON l.user_id = u.id
      AND l.liked_at < DATE '2026-01-06';
-- 13 строк: все 10 пользователей, у семи — NULL в liked_at

-- Условие в WHERE: строки с NULL отсеиваются, LEFT превращается в INNER
SELECT u.name, l.liked_at
FROM   users u
LEFT JOIN likes l
       ON l.user_id = u.id
WHERE  l.liked_at < DATE '2026-01-06';
-- 6 строк: только трое пользователей, лайкавших до 6 января

-- FULL OUTER: строки из обеих таблиц, включая непарные с обеих сторон
SELECT coalesce(l.user_id, v.user_id) AS user_id,
       l.post_id, v.community_id
FROM   likes l
FULL OUTER JOIN views v ON v.user_id = l.user_id;

-- Self-join: таблица соединяется сама с собой
-- Пары пользователей из одного города
SELECT a.name AS user_a, b.name AS user_b, a.city
FROM   users a
JOIN   users b ON b.city = a.city
                AND b.id > a.id      -- чтобы пара не повторялась зеркально
ORDER BY a.city;

-- NATURAL JOIN: соединяет по всем одноимённым столбцам автоматически
SELECT * FROM posts NATURAL JOIN communities;
-- Соединится по id — не по community_id! Результат бессмысленный.

SELECT count(*)             AS total_users,
       count(city)          AS with_city,      -- меньше, если есть NULL
       count(DISTINCT city) AS cities,
       round(avg(age), 1)   AS avg_age,
       min(age), max(age)
FROM   users;

-- Число лайков по каждому пользователю
SELECT user_id, count(*) AS likes_count
FROM   likes
GROUP BY user_id
ORDER BY likes_count DESC, user_id;

-- Группировка по нескольким столбцам
SELECT u.city, u.gender, count(*) AS cnt, round(avg(u.age), 1) AS avg_age
FROM   users u
GROUP BY u.city, u.gender
ORDER BY u.city, u.gender;

-- HAVING фильтрует группы, WHERE — строки до группировки
SELECT c.name, count(*) AS posts_count, round(avg(p.score)) AS avg_score
FROM   posts p
JOIN   communities c ON c.id = p.community_id
WHERE  p.score > 500              -- отбор строк ДО группировки
GROUP BY c.name
HAVING count(*) >= 2            -- отбор групп ПОСЛЕ
ORDER BY avg_score DESC;

Многоуровневые итоги без нескольких запросов:

-- ROLLUP: итоги по городу и общий итог одной строкой
SELECT coalesce(city, 'ВСЕГО') AS city,
       coalesce(gender, 'все')  AS gender,
       count(*) AS cnt
FROM   users
GROUP BY ROLLUP (city, gender)
ORDER BY city NULLS LAST, gender NULLS LAST;

Это самая дорогая ошибка в аналитических запросах. Она не вызывает ошибку и не выглядит подозрительно — просто числа в отчёте больше настоящих.

Механика: при соединении «один ко многим» строка левой таблицы дублируется столько раз, сколько нашлось пар справа. Любой агрегат после этого считает дубликаты.

-- НЕВЕРНО: два независимых соединения один-ко-многим
SELECT u.name,
       count(l.id) AS likes_count,
       count(v.id) AS views_count
FROM   users u
LEFT JOIN likes l ON l.user_id = u.id
LEFT JOIN views v ON v.user_id = u.id
GROUP BY u.name;

У пользователя 1 два лайка и три просмотра. После двух соединений получается 2 × 3 = 6 строк, и обе величины окажутся равными шести вместо двух и трёх.

Три способа сделать правильно:

-- Способ 1: агрегировать до соединения, в CTE
WITH l AS (SELECT user_id, count(*) AS cnt FROM likes GROUP BY user_id),
     v AS (SELECT user_id, count(*) AS cnt FROM views GROUP BY user_id)
SELECT u.name,
       coalesce(l.cnt, 0) AS likes_count,
       coalesce(v.cnt, 0) AS views_count
FROM   users u
LEFT JOIN l ON l.user_id = u.id
LEFT JOIN v ON v.user_id = u.id
ORDER BY u.name;

-- Способ 2: коррелированные подзапросы — короче, но медленнее на объёме
SELECT u.name,
       (SELECT count(*) FROM likes l WHERE l.user_id = u.id) AS likes_count,
       (SELECT count(*) FROM views v WHERE v.user_id = u.id) AS views_count
FROM   users u;

-- Способ 3: считать различные значения — работает, но маскирует проблему
SELECT u.name,
       count(DISTINCT l.id) AS likes_count,
       count(DISTINCT v.id) AS views_count
FROM   users u
LEFT JOIN likes l ON l.user_id = u.id
LEFT JOIN views v ON v.user_id = u.id
GROUP BY u.name;

Одну и ту же задачу часто можно решить тремя способами. Они не эквивалентны.

-- Задача: сообщества, у которых есть хотя бы один пост

-- JOIN — вернёт сообщество столько раз, сколько у него постов!
SELECT DISTINCT c.name
FROM   communities c JOIN posts p ON p.community_id = c.id;

-- IN — по одной строке на сообщество
SELECT c.name FROM communities c
WHERE  c.id IN (SELECT community_id FROM posts);

-- EXISTS — то же, но останавливается на первом совпадении
SELECT c.name FROM communities c
WHERE  EXISTS (SELECT 1 FROM posts p WHERE p.community_id = c.id);