Модуль 03 — Объединение таблиц: JOIN

Данные в реляционных базах хранятся в разных таблицах. JOIN позволяет «склеить» их по общему ключу. По сути, JOIN — это операция над двумя таблицами, которая находит подходящие пары строк.

Возвращает только те строки, для которых условие соединения выполняется в обеих таблицах.

-- Дата лайка и имя пользователя
SELECT l.liked_at,
       u.name AS user_name,
       u.age
FROM   likes l
JOIN   users u ON u.id = l.user_id
ORDER BY l.liked_at, u.name;

-- JOIN трёх таблиц: имя пользователя + заголовок поста + сообщество
SELECT u.name        AS user_name,
       p.title        AS post_title,
       c.name         AS community_name
FROM   likes l
JOIN   users       u ON u.id  = l.user_id
JOIN   posts       p ON p.id  = l.post_id
JOIN   communities c ON c.id  = p.community_id
ORDER BY user_name, post_title, community_name;

LEFT JOIN возвращает ВСЕ строки из левой таблицы и только совпавшие из правой. Несовпавшие строки правой таблицы заполняются NULL.

-- Все сообщества, включая те, которые никто не просматривал
SELECT c.name, c.rating
FROM   communities c
LEFT JOIN views vw ON vw.community_id = c.id
WHERE  vw.id IS NULL        -- только непросмотренные
ORDER BY c.name;

-- Пропущенные дни в январе (1–10) для пользователей 1 и 2
SELECT gs.viewed_at::date AS missing_date
FROM   generate_series('2026-01-01'::date,
                         '2026-01-10'::date,
                         '1 day'::interval) AS gs(viewed_at)
LEFT JOIN views vw
       ON vw.viewed_at = gs.viewed_at::date
      AND vw.user_id IN (1, 2)
WHERE  vw.id IS NULL
ORDER BY missing_date;

FULL OUTER JOIN возвращает все строки из обеих таблиц. Несовпавшие строки с обеих сторон заполняются NULL.

-- Полный список: пользователи И сообщества за 1–3 января 2026
-- (включая тех, кто не заходил, и сообщества без просмотров)
SELECT
    COALESCE(u.name,  '-') AS user_name,
    vw.viewed_at,
    COALESCE(c.name, '-') AS community_name
FROM   users u
FULL JOIN views vw
       ON vw.user_id = u.id
      AND vw.viewed_at BETWEEN '2026-01-01' AND '2026-01-03'
FULL JOIN communities c ON c.id = vw.community_id
ORDER BY user_name, viewed_at, community_name;

-- USING — короткая форма JOIN ... ON a.col = b.col
-- когда столбцы-ключи в обеих таблицах названы одинаково

-- ВНИМАНИЕ: в нашей схеме likes.user_id ≠ users.id по имени,
-- поэтому USING(user_id) не сработает.
-- USING применимо, если бы столбец назывался одинаково в обеих таблицах.
-- Примеры NATURAL JOIN / USING показаны ниже на условных данных:

-- Правильный способ для нашей схемы — всегда явный ON:
SELECT l.liked_at, u.name, u.age
FROM   likes l
JOIN   users u ON u.id = l.user_id
ORDER BY l.liked_at, u.name;

-- USING работает, когда имена совпадают. Если бы FK назывался 'id':
-- SELECT liked_at, name FROM likes JOIN users USING(id);
-- На практике — используйте явный ON: надёжнее при изменениях схемы.

-- Self-JOIN: пользователи из одного города
SELECT
    u1.name AS user_name1,
    u2.name AS user_name2,
    u1.city AS common_city
FROM   users u1
JOIN   users u2
       ON  u1.city = u2.city
      AND u1.id < u2.id     -- избежать дублей (A-B и B-A)
ORDER BY user_name1, user_name2, common_city;

-- Сообщества, которые никто не просматривал — вариант с IN
SELECT name
FROM   communities
WHERE  id NOT IN (
    SELECT DISTINCT community_id
    FROM   views
);

-- Тот же результат через EXISTS
SELECT name
FROM   communities c
WHERE  NOT EXISTS (
    SELECT 1
    FROM   views vw
    WHERE  vw.community_id = c.id
);