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

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

20 питань · ~20 хв · Версія v3.0

Увійдіть, щоб продовжити

JOIN і підзапити, GROUP BY і HAVING, віконні функції й CTE, сортування й набори - питання для MySQL і PostgreSQL, від junior до senior.

За спробу
20
У пулі
58
Проходжень
1
Середній бал
65%
Пройшли на 70%+
0%

Питання для підготовки

28 питань
  • 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) не працює саме через порядок виконання.

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

У запиті з GROUP BY кожна колонка в SELECT має бути або в GROUP BY, або всередині агрегатної функції (COUNT, SUM, MAX...). Інакше незрозуміло, яке значення показати для групи з кількох рядків.

SELECT user_id, status, COUNT(*)
FROM orders
GROUP BY user_id;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'orders.status'...

У користувача кілька замовлень з різними статусами - який із них показати?

Режим ONLY_FULL_GROUP_BY (увімкнений за замовчуванням з MySQL 5.7) забороняє такі запити. Старі версії MySQL виконували їх, повертаючи значення з довільного рядка групи - і код роками працював з випадковими даними. Після оновлення MySQL такі запити раптом починають падати.

Як виправити - вирішити, що насправді потрібно:

-- групувати й за статусом
SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status;

-- агрегат: останній статус за датою - вже інша задача (віконна функція)
SELECT user_id, MAX(created_at) AS last_order_at FROM orders GROUP BY user_id;

-- значення справді однакове для групи (валюта клієнта), а MySQL цього не знає
SELECT user_id, ANY_VALUE(currency), COUNT(*) FROM orders GROUP BY user_id;

Функціональна залежність: MySQL дозволяє колонки, однозначно визначені колонками GROUP BY. Якщо групують за первинним ключем users.id, можна вибирати users.name - вона залежить від ключа.

Чого не робити: вимикати режим ('strict' => false у config/database.php Laravel чи прибирати його з sql_mode), щоб «запрацювало». Так помилка ховається, а результат лишається недетермінованим. PostgreSQL такі запити не дозволяє взагалі.

Докладніше в документації: Обробка GROUP BY у MySQL

Таблиця в SQL - це множина рядків без визначеного порядку. Якщо в запиті немає ORDER BY, база повертає рядки в тому порядку, в якому їй зручно: за індексом, яким скористалася, за фізичним розташуванням, за порядком паралельного виконання.

SELECT * FROM products LIMIT 10;

Сьогодні це «перші 10 вставлених», а завтра - після оновлення MySQL, нового індексу, OPTIMIZE TABLE чи іншого плану запиту - інші 10. Код, що на це покладався, ламається без жодних змін.

Ще підступніше - пагінація з неповним ORDER BY:

SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 20;

Якщо багато товарів мають однаковий created_at (масовий імпорт), порядок серед них не визначений. Одні товари з'являтимуться на двох сторінках, інші - на жодній.

Правило: ORDER BY має задавати однозначний порядок - додайте унікальну колонку останньою:

SELECT * FROM products ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;

Коли порядок справді не важливий: «будь-які 10 записів для перевірки», EXISTS, вибірка для обробки порціями, де кожен рядок однаково підходить.

Швидкодія LIMIT з ORDER BY: якщо є індекс, що віддає рядки в потрібному порядку, MySQL читає лише перші N і зупиняється. Без індексу - сортує всі рядки (Using filesort у EXPLAIN), хоча й оптимізує сортування для невеликого LIMIT.

OFFSET на далеких сторінках все одно читає й відкидає всі попередні рядки. Для глибокої пагінації - keyset: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20.

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

Обидва об'єднують результати кількох 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

Прочитати - ще не значить знати

20 питань, по одному на екран, ~20 хв. Після завершення - розбір кожної помилки з посиланням на питання.