SQL: запити, з'єднання й агрегати

• INNER JOIN повертає лише ті рядки, для яких знайшлася пара в обох таблицях.
• LEFT JOIN повертає всі рядки лівої таблиці; де пари в правій немає, її колонки заповнюються NULL.

-- Лише користувачі, які мають замовлення
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- Усі користувачі; без замовлень - з NULL у o.total
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Типова задача: «користувачі без жодного замовлення» - LEFT JOIN і фільтр на NULL з правого боку:

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

Пастка: умова на праву таблицю в WHERE (WHERE o.status = 'paid') відкидає рядки з NULL і непомітно перетворює LEFT JOIN на INNER JOIN. Якщо треба зберегти всіх користувачів, умову ставлять в ON: LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'.

Ще є RIGHT JOIN (дзеркальний до LEFT, на практиці рідкісний) і FULL JOIN - усі рядки з обох боків.

Докладніше в документації: Табличні вирази: з'єднання

Різниця в моменті, коли вони спрацьовують:

• WHERE фільтрує рядки до групування. Агрегатів у ньому ще немає.
• HAVING фільтрує групи після GROUP BY і може використовувати агрегати.

SELECT user_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
WHERE status = 'paid'          -- лише оплачені замовлення
GROUP BY user_id
HAVING SUM(total) > 10000;     -- лише ті, хто витратив понад 10 000

Порядок виконання запиту: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Звідси й правило: умову на звичайну колонку ставлять у WHERE, бо так база відкидає зайві рядки раніше й групує менше даних.

Типові помилки:

• WHERE COUNT(*) > 5 - помилка: на етапі WHERE груп ще немає.
• HAVING status = 'paid' без status у GROUP BY - помилка і в PostgreSQL, і в MySQL з режимом ONLY_FULL_GROUP_BY (він увімкнений за замовчуванням). А навіть там, де такий запит проходить, він фільтрує пізніше, ніж міг би.
• Посилання в WHERE на псевдонім з SELECT (WHERE spent > 100) не працює саме через порядок виконання.

Докладніше в документації: Агрегатні функції

Задача «найновіший рядок у кожній групі» - класика співбесід. Кілька способів:

1. Віконна функція - працює і в PostgreSQL, і в MySQL 8+:

SELECT *
FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

2. DISTINCT ON - коротко, але лише PostgreSQL:

SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

3. LATERAL (PostgreSQL, MySQL 8.0.14+) - зручно, коли потрібні кілька останніх, а не одне:

SELECT u.id, last.*
FROM users u
CROSS JOIN LATERAL (
    SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3
) last;

Чого уникати: GROUP BY user_id разом із MAX(created_at) і рештою колонок у SELECT - MySQL у старому режимі поверне колонки з довільного рядка групи, а не з останнього.

Для швидкодії потрібен індекс (user_id, created_at DESC): з ним LATERAL бере кілька рядків з індексу, не сортуючи всю таблицю. У Laravel цю задачу розв'язує зв'язок latestOfMany().

Докладніше в документації: Віконні функції

CTE (Common Table Expression) - іменований підзапит у блоці WITH, до якого основний запит звертається як до таблиці. Головна користь - читабельність: складний запит розбивається на кроки з іменами.

WITH paid AS (
    SELECT user_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT u.name, p.spent
FROM users u
JOIN paid p ON p.user_id = u.id
WHERE p.spent > 10000;

WITH RECURSIVE дозволяє CTE посилатися на себе - так обходять ієрархії: дерево категорій, оргструктуру, ланцюжок коментарів.

WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories WHERE id = 42          -- початок
    UNION ALL
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id      -- крок
)
SELECT * FROM tree;

Що треба знати:

• У PostgreSQL до версії 12 CTE завжди матеріалізувався й був «бар'єром оптимізації». З 12-ї простий CTE вбудовується в запит, а поведінку можна задати явно: AS MATERIALIZED / AS NOT MATERIALIZED.
• У рекурсії потрібна умова зупинки, інакше цикли в даних дадуть нескінченний запит. Захист - лічильник глибини чи масив пройдених вузлів.
• MySQL підтримує CTE з версії 8.0.

Докладніше в документації: Запити WITH (CTE)

Обидва об'єднують результати кількох SELECT в один набір, один під одним.

• UNION - прибирає дублікати: результат містить лише унікальні рядки.
• UNION ALL - залишає всі рядки як є, з повторами.

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;      -- кожен email один раз

SELECT 'order' AS type, id, created_at FROM orders
UNION ALL
SELECT 'refund', id, created_at FROM refunds   -- усі події, повтори неможливі за змістом
ORDER BY created_at DESC;

Чому UNION ALL за замовчуванням краще: щоб прибрати дублікати, UNION змушений сортувати чи хешувати весь об'єднаний результат - на великих наборах це дорого (тимчасова таблиця, можливо, на диску). Якщо дублікатів бути не може (різні таблиці, різні типи подій) або вони потрібні, - UNION ALL.

Правила:

• Кількість колонок у всіх SELECT однакова; типи - сумісні.
• Імена колонок результату беруться з першого SELECT.
• ORDER BY і LIMIT в кінці стосуються всього результату. Щоб відсортувати окрему частину, її беруть у дужки з власним LIMIT.

Типові застосування: стрічка подій з кількох таблиць, пошук по кількох сутностях («знайти серед користувачів і компаній»), заміна складного OR на кілька простих запитів, кожен з яких використовує свій індекс:

SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE phone = ?;

У Laravel - ->union($query) і ->unionAll($query).

Докладніше в документації: UNION