Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Питання на співбесіді: SQL-запити

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

25 питань

Поруч з 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;
Оператор Еквівалент Результат
col->'$.path' JSON_EXTRACT(col, '$.path') JSON-значення
col->>'$.path' JSON_UNQUOTE(JSON_EXTRACT(col, '$.path')) рядок без лапок

Різниця важлива в порівняннях: 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

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)

Віконна функція рахує значення по набору рядків («вікну»), але, на відміну від GROUP BY, не схлопує рядки: кожен рядок лишається у результаті й отримує свою цифру.

SELECT
    created_at::date AS day,
    total,
    SUM(total) OVER (ORDER BY created_at) AS running_total,
    RANK()     OVER (ORDER BY total DESC) AS place,
    total - LAG(total) OVER (ORDER BY created_at) AS diff_from_previous
FROM orders;

Основні функції:

  • ROW_NUMBER() - порядковий номер без повторів.
  • RANK() - однакові значення ділять місце, далі пропуск (1, 2, 2, 4). DENSE_RANK() - без пропусків (1, 2, 2, 3).
  • LAG() / LEAD() - значення з попереднього чи наступного рядка.
  • Будь-який агрегат (SUM, AVG, COUNT) з OVER.

PARTITION BY ділить вікно на групи - наприклад, рейтинг усередині кожної категорії: RANK() OVER (PARTITION BY category_id ORDER BY sales DESC).

Рамка вікна - пастка для старших: з ORDER BY агрегат за замовчуванням рахує від початку до поточного рядка з урахуванням рівних значень (RANGE). Рядки з однаковим created_at отримають однаковий накопичений підсумок. Для «рядок за рядком» потрібно явно: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Так само рахують і ковзне середнє: AVG(total) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

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

Проблема: групування за датою повертає лише дні, у які були дані. Для графіка «замовлень по днях» дні без замовлень просто зникають, і лінія графіка бреше.

SELECT DATE(created_at) AS day, COUNT(*) FROM orders GROUP BY day;
-- 2026-10-01: 15, 2026-10-03: 9   (2 жовтня пропущено, хоча там 0)

Рішення - згенерувати повний календар і приєднати до нього дані:

WITH RECURSIVE calendar AS (
    SELECT DATE('2026-10-01') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendar
    WHERE day < '2026-10-31'
)
SELECT c.day, COUNT(o.id) AS orders
FROM calendar c
LEFT JOIN orders o
    ON o.created_at >= c.day
   AND o.created_at < c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

Ключові деталі:

  • LEFT JOIN від календаря, а не від даних - щоб дні без замовлень лишилися.
  • COUNT(o.id), а не COUNT(*) - COUNT(*) порахує рядок календаря й дасть 1 замість 0.
  • Умова з'єднання діапазоном, а не DATE(o.created_at) = c.day - так працює індекс на created_at.
  • Ліміт рекурсії: cte_max_recursion_depth за замовчуванням 1000. Для календаря на кілька років його збільшують для сесії.

Часові пояси: «день» залежить від поясу. Якщо created_at зберігається в UTC, а звіт - за київським часом, межі днів треба зсунути (CONVERT_TZ або обчислені межі), інакше замовлення о 01:00 за Києвом потраплять у попередній день.

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

  • Постійна таблиця-календар з датами на роки вперед - корисна, якщо звітів багато, і в неї можна додати атрибути (вихідні, свята, номер тижня).
  • Заповнення пропусків у застосунку - згенерувати діапазон дат у PHP (CarbonPeriod) і злити з результатом запиту.

У PostgreSQL для цього є generate_series().

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

Рамка вікна визначає, які рядки враховує агрегат для поточного рядка. Є два способи її задати, і для часових даних різниця принципова.

ROWS - за кількістю рядків:

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows
FROM daily_revenue;

Бере 7 рядків. Якщо в даних є пропущені дні, «7 рядків» охоплять більше ніж 7 днів - і середнє буде неправильним.

RANGE з інтервалом - за значенням колонки сортування:

SELECT day, revenue,
       AVG(revenue) OVER (
           ORDER BY day
           RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
       ) AS avg_7_days
FROM daily_revenue;

Бере всі рядки, у яких day в межах 6 днів до поточного - незалежно від того, скільки рядків туди потрапило. Пропущені дні не «розтягують» вікно. MySQL підтримує RANGE з INTERVAL для дат і часу.

Що ще враховувати:

  • Пропущені дні в самих даних: RANGE правильно визначить межі, але AVG порахує середнє лише по наявних днях. Якщо день без продажів має рахуватися як 0 - спершу доповнити дані календарем (рекурсивний CTE).
  • Початок ряду: для перших днів у вікні менше 7 значень. Якщо це спотворює графік, - показувати середнє лише з 7-го дня (COUNT(*) OVER (...) для перевірки кількості).
  • Однакові значення сортування: з RANGE рядки з однаковим day потрапляють у рамку разом (вони «рівні»). З ROWS - ні. Для щоденних агрегатів це не проблема, для сирих подій з однаковими мітками часу - важливо.
  • Іменовані вікна зменшують повтори, коли кілька функцій мають однакове вікно:
SELECT day,
       AVG(revenue) OVER w AS avg_7,
       SUM(revenue) OVER w AS sum_7
FROM daily_revenue
WINDOW w AS (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW);

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

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

У старих версіях MySQL підзапит у WHERE ... IN (SELECT ...) часто виконувався як корельований - для кожного рядка зовнішньої таблиці. Звідси міф «підзапити в MySQL повільні, завжди пишіть JOIN». Сучасний оптимізатор перетворює їх на ефективні плани.

Semi-join - для IN і EXISTS: «рядки зовнішньої таблиці, для яких є хоч один збіг». На відміну від звичайного JOIN, не розмножує рядки при кількох збігах.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Стратегії semi-join, з яких оптимізатор обирає за вартістю:

  • Table pullout - підзапит перетворюється на звичайний JOIN, якщо збіг гарантовано один (наприклад, за первинним ключем).
  • FirstMatch - при обході зупинитися на першому збігу.
  • LooseScan - пройти індекс підзапиту, беручи лише перше значення кожної групи.
  • Materialization - обчислити підзапит один раз у тимчасову таблицю з індексом і шукати в ній.
  • Duplicate Weedout - виконати як звичайний JOIN, а дублікати прибрати потім.

Antijoin (MySQL 8.0.17+) - для NOT IN, NOT EXISTS: «рядки, для яких збігів немає». Раніше такі запити часто виконувалися значно гірше.

Як побачити, що сталося:

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

У плані видно Semi-join, Materialize чи Nested loop semijoin. Після EXPLAIN (звичайного) SHOW WARNINGS показує, на який запит оптимізатор переписав оригінал.

Коли оптимізація не спрацьовує:

  • підзапит з LIMIT, UNION, агрегатами в певних формах;
  • підзапит у UPDATE/DELETE з тієї ж таблиці;
  • NOT IN з колонкою, що може бути NULL (семантика NULL заважає antijoin - і результат може бути порожнім).

Керування - підказки оптимізатору (/*+ SEMIJOIN(FIRSTMATCH) */, NO_SEMIJOIN) і optimizer_switch. Але це крайній захід: спершу перевірити індекси й статистику (ANALYZE TABLE).

Докладніше в документації: Semi-join і antijoin

У MySQL (як і в PostgreSQL) немає оператора PIVOT. Рядки перетворюють на колонки умовною агрегацією: агрегат по групі, де враховуються лише рядки, що відповідають умові колонки.

-- продажі: рядок на кожну пару (товар, місяць)
SELECT product_id,
       SUM(CASE WHEN MONTH(created_at) = 1 THEN total ELSE 0 END) AS jan,
       SUM(CASE WHEN MONTH(created_at) = 2 THEN total ELSE 0 END) AS feb,
       SUM(CASE WHEN MONTH(created_at) = 3 THEN total ELSE 0 END) AS mar
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01'
GROUP BY product_id;

Короткий запис для підрахунків - у MySQL логічний вираз дорівнює 1 чи 0:

SELECT DATE(created_at) AS day,
       SUM(status = 'paid') AS paid,
       SUM(status = 'cancelled') AS cancelled,
       SUM(status = 'refunded') AS refunded
FROM orders
GROUP BY day;

У PostgreSQL для цього є COUNT(*) FILTER (WHERE status = 'paid').

Головне обмеження - колонки мають бути відомі заздалегідь. SQL-запит повертає фіксований набір колонок, тож «стільки колонок, скільки є статусів» одним статичним запитом не зробити.

Варіанти для динамічних колонок:

  • Побудувати запит у застосунку: отримати список значень, згенерувати вирази SUM(CASE ...) і виконати.
  • Підготовлений запит у збереженій процедурі (PREPARE / EXECUTE зі зібраного рядка) - працює, але важко підтримувати й небезпечно, якщо значення приходять від користувача.
  • Повернути «довгий» формат (рядок на кожну пару) і розвернути в таблицю на клієнті - часто найпростіше й найгнучкіше: бібліотеки графіків і таблиць саме цього й очікують.

Зворотна операція (unpivot) - колонки в рядки - робиться через UNION ALL по колонках або JSON_TABLE з масиву значень.

Обережно з NULL: ELSE 0 дає нуль там, де даних немає; без ELSE вийде NULL. Для звіту важливо вирішити, що з цього означає «нуль продажів», а що - «немає даних».

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