Middle: питання на співбесіді з теми «SQL-запити»

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

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().

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

• IN (підзапит) - перевіряє, чи значення є серед результатів підзапиту.
• EXISTS (підзапит) - перевіряє, чи підзапит повернув хоч один рядок. Значення не важливі, тож пишуть SELECT 1.

SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders);
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Для позитивних перевірок сучасні оптимізатори PostgreSQL і MySQL зазвичай перетворюють обидва варіанти на однаковий план (semi-join), тож різниця частіше в читабельності.

Головна пастка - NOT IN і NULL. Якщо підзапит повертає хоч один NULL, NOT IN не поверне жодного рядка:

SELECT * FROM users WHERE id NOT IN (SELECT manager_id FROM teams);
-- якщо в teams є рядок з manager_id = NULL - порожній результат

Причина в тризначній логіці: 5 NOT IN (1, NULL) - це 5 <> 1 AND 5 <> NULL, а 5 <> NULL дає NULL, не true. Рядок не проходить фільтр.

Тому для «немає відповідності» використовують NOT EXISTS або LEFT JOIN ... WHERE x.id IS NULL. Вони поводяться передбачувано з NULL і добре оптимізуються.

Докладніше в документації: Вирази з підзапитами

Корельований підзапит посилається на колонки зовнішнього запиту, тож логічно виконується для кожного рядка зовнішнього запиту:

SELECT u.id, u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders_count,
       (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order_at
FROM users u;

Звичайний (некорельований) підзапит не залежить від зовнішнього і виконується один раз: WHERE id IN (SELECT user_id FROM vip_list).

Чим корельовані підзапити можуть бути погані: на 100 000 користувачів - 200 000 виконань підзапитів. З індексом на orders.user_id кожне швидке, але сумарно це багато роботи. Без індексу - катастрофа.

Як переписують:

-- JOIN з агрегованою похідною таблицею: orders проходиться один раз
SELECT u.id, u.name,
       COALESCE(s.orders_count, 0) AS orders_count,
       s.last_order_at
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS orders_count, MAX(created_at) AS last_order_at
    FROM orders
    GROUP BY user_id
) s ON s.user_id = u.id;

Але не завжди варто переписувати:

• Оптимізатор MySQL сам перетворює багато підзапитів з IN / EXISTS на semi-join і виконує їх як з'єднання.
• Для сторінки з 20 рядків корельований підзапит виконається 20 разів - це дешевше, ніж агрегувати всю таблицю замовлень.
• Корельований EXISTS з індексом зупиняється на першому знайденому рядку - часто найефективніший спосіб перевірки.

Правило: дивитися EXPLAIN і час на реальних даних. Підзапит у SELECT для кожного рядка великої вибірки - підозрілий; EXISTS у WHERE з індексом - зазвичай у порядку.

У Laravel withCount('orders') генерує саме корельований підзапит - для пагінованих списків це нормально.

Докладніше в документації: Корельовані підзапити

Похідна таблиця (derived table) - підзапит у FROM, результат якого використовується як тимчасова таблиця:

SELECT c.name, t.revenue
FROM categories c
JOIN (
    SELECT category_id, SUM(total) AS revenue
    FROM orders
    GROUP BY category_id
) t ON t.category_id = c.id;

Звичайна похідна таблиця не бачить таблиць зовнішнього запиту - вона обчислюється сама по собі.

LATERAL (MySQL 8.0.14+) дозволяє похідній таблиці посилатися на колонки таблиць, що стоять лівіше в FROM. Вона обчислюється для кожного рядка зовнішньої таблиці - як корельований підзапит, але може повертати кілька рядків і колонок.

Класична задача - «топ-3 замовлення кожного користувача»:

SELECT u.name, top.id, top.total
FROM users u
JOIN LATERAL (
    SELECT o.id, o.total
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.total DESC
    LIMIT 3
) AS top ON TRUE;

З індексом (user_id, total) кожен підзапит бере три рядки прямо з індексу.

Альтернатива - віконна функція:

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

Вона нумерує всі замовлення, а потім відкидає зайві. На великій таблиці, коли користувачів у вибірці небагато, LATERAL з індексом зазвичай швидший; коли потрібні всі користувачі - віконна функція простіша.

Що варто знати:

• LEFT JOIN LATERAL ... ON TRUE - залишити користувачів без замовлень.
• Оптимізатор MySQL може «злити» просту похідну таблицю з зовнішнім запитом (derived merge) або матеріалізувати її. Поведінку видно в EXPLAIN (DERIVED).
• MySQL до 8.0.14 LATERAL не підтримує - там цю задачу розв'язували змінними чи корельованими підзапитами.

Докладніше в документації: LATERAL-похідні таблиці

MySQL підтримує багатотабличні UPDATE і DELETE з JOIN:

-- позначити замовлення заблокованих користувачів
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.status = 'cancelled'
WHERE u.is_banned = 1 AND o.status = 'pending';

-- видалити товари з кошиків, яких більше немає в продажу
DELETE ci
FROM cart_items ci
JOIN products p ON p.id = ci.product_id
WHERE p.is_active = 0;

У DELETE перед FROM вказують, з якої таблиці видаляти (DELETE ci), - інакше можна видалити з обох.

Синтаксис відрізняється між СУБД:

-- PostgreSQL
UPDATE orders o SET status = 'cancelled'
FROM users u
WHERE u.id = o.user_id AND u.is_banned;

DELETE FROM cart_items ci USING products p
WHERE p.id = ci.product_id AND NOT p.is_active;

Переносимий варіант - підзапит: WHERE user_id IN (SELECT id FROM users WHERE is_banned = 1).

Обмеження MySQL: не можна змінювати таблицю й одночасно читати з неї в підзапиті:

DELETE FROM orders WHERE id IN (SELECT id FROM orders WHERE ...);
-- ERROR 1093: You can't specify target table 'orders' for update in FROM clause

Обхід - обгорнути підзапит у ще одну похідну таблицю (MySQL матеріалізує її) або переписати через JOIN.

Обережно з масовими змінами:

• Спершу виконати той самий запит як SELECT і перевірити кількість рядків.
• Великі оновлення - порціями з LIMIT (у багатотабличних UPDATE/DELETE LIMIT не дозволений - порції роблять за діапазонами id).
• Блокування: UPDATE ... JOIN блокує рядки, які читає, і в об'єднаній таблиці теж - під навантаженням можливі очікування й deadlock'и.

У Laravel: DB::table('orders')->join('users', ...)->where(...)->update([...]) генерує багатотабличний UPDATE для MySQL.

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

Знайти дублікати - групування за колонками, що мали б бути унікальними:

SELECT email, COUNT(*) AS copies, MIN(id) AS keep_id
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Побачити всі рядки-дублікати з порядковим номером - віконна функція:

SELECT *
FROM (
    SELECT u.*,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
    FROM users u
) t
WHERE rn > 1;    -- усе, крім першого запису кожного email

ORDER BY визначає, який запис залишити: найстаріший, найновіший, найповніший.

Видалити дублікати:

DELETE u
FROM users u
JOIN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) AS rn
        FROM users
    ) ranked
    WHERE rn > 1
) dup ON dup.id = u.id;

Подвійне вкладення обходить обмеження MySQL на читання з тієї ж таблиці, з якої видаляють.

Перш ніж видаляти:

• Злити пов'язані дані: замовлення, коментарі, підписки дублікатів перепризначити на запис, що залишається. Інакше вони або зникнуть каскадом, або залишаться «сиротами».
• Нормалізувати значення: Olia@Example.com і olia@example.com - теж дублікати, якщо порівнювати з урахуванням регістру й пробілів.
• Зробити бекап або перенести дублікати в окрему таблицю, а не видаляти одразу.

Головне - не допустити повторення: додати унікальний індекс після очищення (ALTER TABLE users ADD UNIQUE (email)). Без нього дублікати з'являться знову - через гонку двох одночасних реєстрацій чи імпорт.

Докладніше в документації: Використання віконних функцій

Поруч з UNION у SQL є ще дві операції над множинами рядків:

• INTERSECT - рядки, що є в обох результатах;
• EXCEPT - рядки з першого результату, яких немає в другому.

У MySQL вони з'явилися у версії 8.0.31 (у PostgreSQL є давно).

-- клієнти, що купували і в 2025, і в 2026 році
SELECT user_id FROM orders WHERE YEAR(created_at) = 2025
INTERSECT
SELECT user_id FROM orders WHERE YEAR(created_at) = 2026;

-- зареєстровані, але жодного разу не купували
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

Як і UNION, обидві за замовчуванням прибирають дублікати; INTERSECT ALL і EXCEPT ALL - зберігають.

Чим замінювали раніше:

-- INTERSECT
SELECT DISTINCT o1.user_id
FROM orders o1
WHERE YEAR(o1.created_at) = 2025
  AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = o1.user_id AND YEAR(o2.created_at) = 2026);

-- EXCEPT
SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Чим EXCEPT кращий за NOT IN: порівняння в операціях над множинами вважає NULL рівними між собою, тож немає пастки NOT IN з NULL у підзапиті.

Коли брати що:

• INTERSECT/EXCEPT - коли порівнюються цілі рядки кількох колонок і важлива читабельність («є тут, але немає там»).
• EXISTS/NOT EXISTS - коли потрібні колонки лише з першої таблиці, а умова зв'язку складна; часто оптимізатор виконує їх ефективніше, особливо з індексами.

Пастка з YEAR(created_at): функція над колонкою не дає використати індекс на created_at. Для великих таблиць - діапазон: created_at >= '2025-01-01' AND created_at < '2026-01-01'.

Докладніше в документації: Операції над множинами

WITH ROLLUP додає до результату групування рядки підсумків: для кожного рівня групування і загальний підсумок.

SELECT country, city, SUM(total) AS revenue
FROM orders
GROUP BY country, city WITH ROLLUP;

country | city   | revenue
UA      | Київ   | 120000
UA      | Львів  | 45000
UA      | NULL   | 165000     <- підсумок по країні
PL      | Krakow | 30000
PL      | NULL   | 30000
NULL    | NULL   | 195000     <- загальний підсумок

В одному запиті - і деталі, і проміжні суми, і загальна сума. Без ROLLUP довелося б об'єднувати кілька запитів через UNION ALL чи рахувати підсумки в застосунку.

Як відрізнити рядок підсумку від справжнього NULL: функція GROUPING() (MySQL 8.0.12+) повертає 1 для колонок, «згорнутих» у підсумок:

SELECT
    IF(GROUPING(country), 'Усі країни', country) AS country,
    IF(GROUPING(city), 'Усього', city) AS city,
    SUM(total) AS revenue
FROM orders
GROUP BY country, city WITH ROLLUP;

Без цього рядок з city = NULL (замовлення без міста) неможливо відрізнити від підсумку по країні.

Що варто знати:

• Порядок колонок у GROUP BY визначає ієрархію: country, city дає підсумки по країнах, але не по містах окремо від країн.
• ORDER BY з ROLLUP дозволений з MySQL 8.0.12; раніше підсумки йшли лише в природному порядку групування.
• PostgreSQL має ROLLUP, а також CUBE (підсумки за всіма комбінаціями) і GROUPING SETS (довільні набори групувань). MySQL підтримує лише ROLLUP.

Коли це доречно: звіти й вивантаження для бухгалтерії, де підсумки потрібні прямо в даних. Для веб-інтерфейсу підсумки часто зручніше рахувати окремим запитом чи в застосунку, щоб не змішувати рядки різного типу.

Докладніше в документації: Модифікатори GROUP BY

Збережена процедура - іменований блок SQL з параметрами, що виконується на сервері через CALL. Збережена функція повертає одне значення і використовується у виразах.

DELIMITER //

CREATE PROCEDURE close_month(IN p_month DATE)
BEGIN
    START TRANSACTION;
    INSERT INTO monthly_totals (month, total)
    SELECT p_month, SUM(amount) FROM payments
    WHERE created_at >= p_month AND created_at < p_month + INTERVAL 1 MONTH;
    UPDATE payments SET closed = 1
    WHERE created_at >= p_month AND created_at < p_month + INTERVAL 1 MONTH;
    COMMIT;
END //

CREATE FUNCTION vat(amount DECIMAL(12,2)) RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL
RETURN amount * 0.2 //

DELIMITER ;

CALL close_month('2026-09-01');
SELECT id, vat(total) FROM orders;

DELIMITER - команда клієнта mysql, а не сервера: тіло процедури містить ;, тож клієнту потрібен інший роздільник. Через PDO (і DB::unprepared() у Laravel) він не потрібен - запит надсилається цілком.

Характеристики функції - DETERMINISTIC, NO SQL, READS SQL DATA. Якщо ввімкнено бінарний журнал, MySQL відмовиться створити функцію без цих позначок (або без log_bin_trust_function_creators): недетермінована функція могла б дати різні результати на джерелі й репліці. Позначка - обіцянка: неправдиве DETERMINISTIC MySQL не перевіряє.

Коли процедури доречні:

• обробка великих обсягів даних без передачі їх у застосунок;
• спільна логіка для кількох застосунків на різних мовах, що працюють з однією базою;
• адміністративні операції, які запускає DBA.

Чому в застосунках на Laravel їх зазвичай уникають:

• тестування й налагодження складніші: немає Pest, покриття, нормального налагоджувача;
• деплой і версіонування: код живе в міграціях, рев'ю й відкат незручні, легко розійтися між середовищами;
• масштабування: база - найважче для масштабування місце, а логіка в ній додає навантаження;
• обмеження: функції й тригери не можуть змінювати таблицю, яку вже читає оператор, що їх викликав; рекурсивні функції заборонені;
• знання в команді: критична логіка на мові, якою команда пише рідко.

Виклик з Laravel: DB::select('CALL report_for(?)', [$month]) для процедури з результатом, DB::statement('CALL close_month(?)', [...]) - без нього. Помилка з SIGNAL SQLSTATE '45000' приходить як QueryException.

Докладніше в документації: MySQL: CREATE PROCEDURE і CREATE FUNCTION

Витягти значення з JSON-колонки:

SELECT id,
       settings->'$.theme'   AS theme_json,   -- "dark" (JSON-значення з лапками)
       settings->>'$.theme'  AS theme         -- dark (звичайний рядок)
FROM users;

Різниця важлива в порівняннях: settings->'$.theme' = 'dark' порівнює JSON з рядком і може дати не той результат, якого очікуєте; для умов і сортування зазвичай потрібен ->>.

Шляхи: $.address.city, елемент масиву $.tags[0], усі елементи $.tags[*].

JSON_TABLE - перетворити JSON-масив на рядки й колонки, з якими працює звичайний SQL:

SELECT o.id, items.sku, items.qty
FROM orders o,
     JSON_TABLE(o.payload, '$.items[*]' COLUMNS (
         sku VARCHAR(32) PATH '$.sku',
         qty INT         PATH '$.qty' DEFAULT '1' ON EMPTY
     )) AS items
WHERE items.sku = 'A-1';

Корисно, щоб розібрати масив позицій із зовнішнього API, порахувати агрегати по елементах масиву чи перенести дані з JSON у нормальні таблиці під час міграції.

Зміна JSON без перезапису всього документа:

UPDATE users SET settings = JSON_SET(settings, '$.theme', 'light') WHERE id = 7;

Для JSON_SET, JSON_REPLACE, JSON_REMOVE InnoDB може оновити документ частково, і в бінарний журнал (з binlog_row_value_options=PARTIAL_JSON) потрапить лише зміна.

У Laravel:

User::where('settings->theme', 'dark')->get();          // json_unquote(json_extract(...)), тобто те саме, що ->>
User::whereJsonContains('settings->tags', 'php')->get();
$user->update(['settings->theme' => 'light']);          // JSON_SET

Обмеження: умова по settings->>'$.theme' не використовує звичайних індексів. Для частих фільтрів потрібна згенерована колонка з індексом або функціональний індекс; для масивів - багатозначний індекс (MEMBER OF). Якщо поле фільтрують у кожному запиті, це сигнал винести його в окрему колонку.

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