Задачи по JOIN'ам SQL

Зачем это знать

JOIN — база SQL. Не знаете JOIN — не пройдёте ни один собес аналитика. Но «know JOIN» не значит «знать LEFT». Есть тонкости: множественные matches, NULL обработка, anti-join, self-join. Типичные ошибки дорогие.

Задача 1: users без заказов

«Найди всех users, у которых нет заказов».

SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Альтернативы:

• WHERE u.id NOT IN (SELECT user_id FROM orders) — осторожно с NULL
• WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = u.id) — лучше

Задача 2: последний заказ каждого user

SELECT DISTINCT ON (u.id)
    u.id, u.email, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;

Postgres-specific. Универсально:

SELECT u.*, o.*
FROM users u
LEFT JOIN (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) o ON o.user_id = u.id AND o.rn = 1;

Задача 3: сумма заказов per user

SELECT
    u.id,
    u.email,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email;

COALESCE для users без заказов (NULL → 0).

Задача 4: sales с product names

«Отчёт продаж с именами продуктов».

SELECT
    s.id,
    s.created_at,
    p.name AS product,
    s.quantity,
    s.total
FROM sales s
JOIN products p ON s.product_id = p.id;

INNER JOIN — если нет product, не показывать.

Задача 5: full picture — users + orders + products

SELECT
    u.email,
    o.id AS order_id,
    o.created_at,
    p.name AS product,
    s.quantity,
    s.quantity * p.price AS line_total
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN sales s ON s.order_id = o.id
JOIN products p ON p.id = s.product_id;

Multi-table JOIN.

Задача 6: self-join (employee → manager)

«Для каждого сотрудника — имя manager».

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Self-join: таблица с собой через alias.

Задача 7: all pairs (CROSS JOIN)

«Все возможные пары products для бандлов».

SELECT p1.name, p2.name
FROM products p1
CROSS JOIN products p2
WHERE p1.id < p2.id;

p1.id < p2.id убирает self-pairs и дубликаты.

Задача 8: anti-join

«Товары, которые никогда не продавались».

SELECT p.*
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM sales WHERE product_id = p.id
);

Или LEFT JOIN + NULL.

Задача 9: FULL OUTER — сверка списков

«В одном списке есть, в другом нет — и наоборот».

SELECT
    COALESCE(a.email, b.email) AS email,
    a.email IS NOT NULL AS in_a,
    b.email IS NOT NULL AS in_b
FROM list_a a
FULL OUTER JOIN list_b b ON a.email = b.email;

Shows which emails в a, в b, или в обоих.

Задача 10: множественные matches

Ловушка: если JOIN условие matches более одной строки right table, left rows дублируются.

SELECT u.id, o.id
FROM users u
JOIN orders o ON u.id = o.user_id;

User с 5 заказами → 5 строк. Используйте DISTINCT или GROUP BY если хотите по user.

Задача 11: date range JOIN

«Для каждого пользователя — события в первые 7 дней после signup».

SELECT u.id, e.*
FROM users u
JOIN events e ON e.user_id = u.id
    AND e.created_at BETWEEN u.signup_at AND u.signup_at + INTERVAL '7 days';

JOIN с date-range condition.

Задача 12: cohort JOIN

«Cohort retention: для каждого month signup — кто active через N дней».

SELECT
    DATE_TRUNC('month', u.signup_at) AS cohort,
    DATE_DIFF('day', u.signup_at, e.created_at) AS days_since,
    COUNT(DISTINCT e.user_id) AS active_users
FROM users u
LEFT JOIN events e ON e.user_id = u.id
GROUP BY 1, 2;

Задача 13: LATERAL JOIN

Для каждого user — topN recent events:

SELECT u.id, e.*
FROM users u,
LATERAL (
    SELECT * FROM events
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 5
) e;

Postgres. Subquery ссылается на outer table.

Частые ошибки

INNER когда нужен LEFT

Теряется data для non-matching rows.

Multiplicative joins

Забыть GROUP BY — получить в 5 раз больше строк.

NOT IN с NULL

NOT IN (SELECT ... WHERE может быть NULL) → пустой result. NOT EXISTS.

JOIN без index

Медленные queries. Check EXPLAIN plan.

JOIN performance

• Hash join: обычно быстро для big tables
• Merge join: sorted tables
• Nested loop: small inner table

EXPLAIN ANALYZE показывает тип.

На собесе

Прогоните задачи выше. Если знаете 10 из 13 — middle-уровень SQL.

Умейте объяснить trade-offs: когда LEFT vs INNER, когда EXISTS vs IN.

Связанные темы

• Задачи на JOIN'ы
• Типы JOIN шпаргалка
• LEFT JOIN vs INNER JOIN
• EXISTS vs IN

FAQ

Self-join часто?

В hierarchies (employee/manager), графах, comparison past/current.

LATERAL работает везде?

Postgres, Oracle. MSSQL использует CROSS APPLY.

FULL OUTER часто?

Редко. Для сверок списков (reconciliation).

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.