vyvchy
    Теми розділу

    05 · Бази даних і SQL для QA

    Підзапити і CTE (WITH), операції з множинами

    Зміст

    Доти, доки запит зводиться до «дістань рядки з однієї таблиці» або «склей дві таблиці через JOIN», вистачає того, що вже вміє SELECT. Але щойно завдання звучить як «знайди клієнтів без жодного замовлення», «покажи товари, дорожчі за середню ціну», «звір, що список у UI збігається з двома джерелами разом» — простого SELECT замало. Треба вкласти один запит усередину іншого або скласти кілька результатів у стос. Саме про це підзапити, узагальнені табличні вирази (CTE) й операції з множинами.

    Для QA це не академічна тема. Перевірки цілісності даних — «немає осиротілих рядків», «агрегат у звіті сходиться з сумою по деталях», «жоден запис не втратив пару після міграції» — це майже завжди підзапит або анти-з'єднання. А найпідступніша пастка розділу, NOT IN з NULL, вміє тихо зробити ваш перевірковий запит фальшиво зеленим: він поверне «порушень не знайдено» не тому, що їх немає, а тому, що зламався сам. Тому механіку треба розуміти, а не завчати шаблони.

    Підзапити: запит усередині запиту

    Підзапит (subquery) — це SELECT, вкладений в інший запит. Місце вкладення визначає, як він поводиться. Є три позиції.

    У WHERE — найчастіший випадок. Підзапит повертає значення (чи набір значень), з якими фільтрують зовнішній запит.

    -- Скалярний підзапит: повертає рівно одне значення
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Підзапит-набір: повертає стовпець значень для IN
SELECT name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE archived = false);

    Скалярний підзапит (scalar subquery) — той, що повертає один рядок і один стовпець. Якщо він раптом поверне більше рядків, СУБД впаде з помилкою на кшталт more than one row returned by a subquery used as an expression. Це корисно як сигнал: якщо ваш «унікальний» підзапит зненацька повертає кілька рядків — дані не такі унікальні, як ви думали.

    У FROM — підзапит виступає похідною таблицею (derived table): проміжним результатом, з якого зовнішній запит вибирає далі. Похідна таблиця потребує аліаса: у MySQL, SQL Server і PostgreSQL до версії 16 без нього запит навіть не виконається (PostgreSQL 16 і Oracle дозволяють ім'я опустити — але не привчайтеся: з аліасом запит і переносний, і читабельний).

    SELECT category_id, avg_price
FROM (
  SELECT category_id, AVG(price) AS avg_price
  FROM products
  GROUP BY category_id
) AS category_stats
WHERE avg_price > 100;

    У SELECT — скалярний підзапит як окремий обчислюваний стовпець, значення якого рахується для кожного рядка результату.

    SELECT
  c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_count
FROM customers c;

    Останній приклад уже особливий: підзапит усередині посилається на c.id із зовнішнього запиту. Це корельований підзапит — про нього далі.

    Корельовані підзапити

    Звичайний (некорельований) підзапит самодостатній: його можна виконати окремо, він рахується один раз, а результат підставляється у зовнішній запит. Корельований підзапит (correlated subquery) так не вміє — він посилається на стовпці зовнішнього запиту, тому концептуально виконується заново для кожного зовнішнього рядка.

    -- Працівники, що заробляють більше за середнє у СВОЄМУ відділі
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = e.dept_id   -- кореляція із зовнішнім e.dept_id
);

    Ментальна модель: для кожного рядка e внутрішній запит рахує середнє саме по відділу цього рядка. «Концептуально» — бо на практиці оптимізатор нерідко вміє переписати таку конструкцію у з'єднання й не ганяти підзапит мільйон разів. Але писати й читати його треба саме як «для кожного рядка».

    Корельований підзапит — природна пара до EXISTS, який без кореляції майже не має сенсу.

    IN, EXISTS і пастка NOT IN з NULL

    Два способи спитати «чи є цей рядок у наборі» виглядають взаємозамінними, але поводяться по-різному саме на межах — а межі для QA і є робота.

    IN порівнює значення зі списком, який повернув підзапит. EXISTS перевіряє сам факт наявності хоча б одного рядка у корельованому підзапиті й зупиняється на першому знайденому.

    -- Через IN: збери всі customer_id із orders, потім звір
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Через EXISTS: для кожного клієнта спитай «чи існує хоч одне замовлення»
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

    Для позитивної перевірки обидва дають однаковий результат, і сучасні оптимізатори нерідко зводять їх до одного плану (semi-join). Старе правило «EXISTS завжди швидший за IN» сьогодні здебільшого міф — орієнтуйтеся на план виконання (EXPLAIN), а не на прикмети. У EXISTS після SELECT пишуть 1 чи * суто за звичкою: значення не використовується, важливий лише факт існування рядка.

    А тепер головна пастка розділу — заперечення. NOT IN з підзапитом, серед значень якого трапляється NULL, не поверне жодного рядка. Ніколи. Незалежно від даних.

    -- Виглядає як «керівники, які нікому не підпорядковані».
-- Якщо серед manager_id є хоч один NULL (а в найвищого боса він саме NULL) —
-- запит поверне 0 рядків завжди.
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

    Чому так. x NOT IN (a, b, NULL) розкривається у x <> a AND x <> b AND x <> NULL. Порівняння з NULL дає не TRUE і не FALSE, а UNKNOWN (це тризначна логіка — three-valued logic, докладно в главі /sql/funktsii-case-i-pastky-null). А TRUE AND TRUE AND UNKNOWN дорівнює UNKNOWN, і рядок до результату не потрапляє. Отже, NOT IN з NULL-ом у наборі не може повернути TRUE для жодного рядка — тільки FALSE або UNKNOWN.

    x NOT IN підзапит
    з NULL у наборі

    x <> a AND x <> b AND x <> NULL

    ... AND UNKNOWN

    Увесь предикат = UNKNOWN

    Рядок НЕ проходить фільтр

    Результат: 0 рядків — завжди

    x NOT IN підзапит
    з NULL у наборі

    x <> a AND x <> b AND x <> NULL

    ... AND UNKNOWN

    Увесь предикат = UNKNOWN

    Рядок НЕ проходить фільтр

    Результат: 0 рядків — завжди

    Безпечна заміна — NOT EXISTS, який на NULL не ведеться, бо перевіряє існування рядка, а не рівність значень:

    SELECT name FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees m WHERE m.manager_id = e.id
);

    Для QA висновок жорсткий: якщо ви пишете перевірковий запит «покажи записи-порушники» через NOT IN, а стовпець підзапиту може містити NULL — ваш тест ризикує завжди повертати порожньо й рапортувати «все чисто». Це найгірший тип фальшивого зеленого. За замовчуванням для заперечень беріть NOT EXISTS або анти-з'єднання (див. нижче).

    CTE (WITH): коли підзапит стає нечитабельним

    Узагальнений табличний вираз (common table expression, CTE) — це іменований проміжний результат, оголошений через WITH перед основним запитом. По суті це той самий підзапит із FROM, але винесений «нагору» і названий — тому запит читається згори вниз, як послідовність кроків, а не як матрьошка дужок.

    WITH category_stats AS (
  SELECT category_id, AVG(price) AS avg_price
  FROM products
  GROUP BY category_id
),
expensive_categories AS (
  SELECT category_id FROM category_stats WHERE avg_price > 100
)
SELECT p.name, p.price
FROM products p
JOIN expensive_categories e ON e.category_id = p.category_id;

    CTE не дає нової потужності, якої не було б у підзапитів, — його цінність суто в читабельності й повторному використанні. Один CTE можна згадати кілька разів у головному запиті, не дублюючи текст підзапиту. Коли логіка перевірки розростається на три-чотири кроки (порахувати агрегат, відфільтрувати, зіставити), CTE перетворює нечитабельну вкладеність на лінійний конспект — а перевірковий SQL, який читає інша людина на рев'ю, вартий цього.

    Окрема здатність — рекурсивний CTE (WITH RECURSIVE) для ієрархій: дерево категорій, ланцюжок «керівник → підлеглий», граф залежностей. Це нішевий, але впізнаваний інструмент; глибоко його зазвичай не питають.

    Важливий нюанс продуктивності, який любить спливати на співбесідах. Історично PostgreSQL завжди матеріалізував CTE — виконував його окремо й складав результат у проміжну таблицю (так званий «optimization fence»). Починаючи з PostgreSQL 12, простий CTE планувальник може вбудовувати (inline) у головний запит, як звичайний підзапит, — а примусову матеріалізацію тепер вмикають явно через WITH ... AS MATERIALIZED. Тобто «CTE завжди повільніший за підзапит» — застаріле твердження, прив'язане до конкретних версій СУБД.

    Операції з множинами: UNION, INTERSECT, EXCEPT

    Якщо JOIN клеїть таблиці вшир (додає стовпці), то операції з множинами (set operations) складають результати вгору — стосом, рядок під рядком. Обов'язкові умови: обидва запити мають однакову кількість стовпців і сумісні типи; імена стовпців результату беруться з першого запиту.

    UNION проти UNION ALL — розрізняти обов'язково.

    • UNION ALL просто зшиває рядки обох запитів як є, зокрема дублікати. Дешево.
    • UNION робить те саме, але потім прибирає дублікати (неявний DISTINCT), а це коштує сортування чи хешування всього результату.
    -- Усі email-адреси з двох джерел, з можливими повторами
SELECT email FROM customers
UNION ALL
SELECT email FROM leads;

-- Ті самі адреси, але кожна рівно раз
SELECT email FROM customers
UNION
SELECT email FROM leads;

    Правило за замовчуванням: беріть UNION ALL, доки вам справді не потрібне усунення дублікатів. Дуже часто програміст пише UNION за звичкою, платить за дедуплікацію на кожному прогоні, а дублікатів у даних і не буває. Для QA це ще й пастка звірки: якщо очікувана кількість рядків у звіті не сходиться з UNION-запитом — спершу перевірте, чи не з'їв UNION легітимні повтори, які насправді мали лишитися.

    INTERSECT повертає рядки, присутні в обох запитах; EXCEPT — рядки з першого запиту, яких немає в другому (в Oracle ця операція зветься MINUS). Обидві за замовчуванням теж прибирають дублікати; варіанти INTERSECT ALL / EXCEPT ALL їх зберігають.

    -- Користувачі, що є і в проді, і в staging (звірка синхронізації БД)
SELECT id FROM prod_users
INTERSECT
SELECT id FROM staging_users;

-- Користувачі з проду, яких немає в staging
SELECT id FROM prod_users
EXCEPT
SELECT id FROM staging_users;

    EXCEPT — зручний спосіб порівняти два набори «що загубилося при перенесенні». Тонкість, про яку варто знати: у порівнянні рядків для операцій з множинами NULL вважається рівним NULL (на відміну від звичайного =, де NULL = NULL дає UNKNOWN). Тому два рядки з NULL в одному й тому ж стовпці UNION вважатиме дублікатами.

    JOIN vs UNION vs підзапит

    Три інструменти легко переплутати назвами, але вони відповідають на різні питання. Різниця — геометрична.

    Підзапит — вглиб

    Зовнішній запит

    використовує результат
    вкладеного SELECT

    UNION — вгору

    Рядки A

    + рядки B
    ті самі стовпці

    JOIN — вшир

    Таблиця A

    + стовпці з B
    за умовою ON

    Підзапит — вглиб

    Зовнішній запит

    використовує результат
    вкладеного SELECT

    UNION — вгору

    Рядки A

    + рядки B
    ті самі стовпці

    JOIN — вшир

    Таблиця A

    + стовпці з B
    за умовою ON

    ІнструментЩо робитьКоли брати
    JOINДодає стовпці з іншої таблиці за умовою збігуТреба зіставити пов'язані дані пліч-о-пліч
    UNION / UNION ALLСкладає рядки кількох запитів у стосТреба об'єднати однорідні набори в один список
    Підзапит / CTEОбчислює проміжний результат для фільтра чи стовпцяТреба «запит усередині запиту»: агрегат, набір для IN, перевірка існування

    Практична межа: одну задачу нерідко можна розв'язати різними способами. «Клієнти із замовленнями» — це і JOIN, і IN-підзапит, і EXISTS. Обирайте за читабельністю й наміром: EXISTS чесно каже «мене цікавить факт наявності», JOIN — «мені потрібні дані з обох таблиць». Механіку самих з'єднань дивіться в главі /sql/join-typy-ziednan-tablyts.

    Класичні задачі співбесід

    Дві задачі трапляються на співбесідах з SQL найчастіше — і обидві якраз про цей розділ.

    Другий за величиною

    «Знайдіть другу за величиною зарплату». Сильна відповідь починається з уточнення: друге унікальне значення чи другий рядок? Якщо двоє отримують максимум, друга за величиною зарплата — це наступне менше значення, а не той самий максимум удруге.

    -- Надійний варіант: наступне значення після максимуму.
-- Природно ігнорує дублікати верхівки й повертає NULL, якщо другого немає.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Через DISTINCT і зсув (синтаксис PostgreSQL / MySQL).
-- DISTINCT тут критичний, інакше дублікати верхівки з'їдять зсув.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

    Перший варіант елегантно закриває крайові випадки: якщо в таблиці лише одне унікальне значення зарплати, внутрішній MAX відсіче всі рядки і зовнішній MAX поверне NULL, а не помилку. Варіант із LIMIT OFFSET у SQL Server пишеться інакше (OFFSET ... FETCH або TOP) — на це варто зважати, якщо інтерв'юер називає конкретну СУБД. Найчистіше «N-й за величиною» розв'язується віконними функціями (DENSE_RANK) — це тема глави /sql/vikonni-funktsii; згадка про них у відповіді показує кругозір.

    Записи без пари (анти-з'єднання)

    «Знайдіть клієнтів без жодного замовлення», «замовлення без платежу», «рядки-сироти після міграції». Це анти-з'єднання (anti-join) — «дай рядки з A, для яких у B немає відповідника». Три канонічні способи, і не всі однаково безпечні.

    -- 1. LEFT JOIN + IS NULL: класичне анти-з'єднання
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- 2. NOT EXISTS: читається як умова, безпечний до NULL
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 3. NOT IN: НЕБЕЗПЕЧНИЙ, якщо orders.customer_id буває NULL (див. пастку вище)
SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

    Варіанти 1 і 2 еквівалентні за результатом і обидва коректні. Варіант 3 — та сама пастка NOT IN з NULL: якщо серед customer_id у замовленнях трапиться NULL, запит поверне порожньо й ваша перевірка цілісності мовчки збреше. Для QA це найчастіший робочий сценарій розділу: пошук осиротілих записів — базова перевірка після міграцій і в тестах цілісності даних (глава про тестування БД у цьому розділі). Тому дефолт для «записів без пари» — LEFT JOIN ... IS NULL або NOT EXISTS, а не NOT IN.

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

    Виглядає як «немає порушень», а насправді запит зламався. NOT IN з NULL у підзапиті повертає 0 рядків завжди. Перевірка цілісності на NOT IN — фальшиво зелена. Ліки: NOT EXISTS або анти-з'єднання.

    Виглядає як безпечний UNION, а насправді втрата рядків. UNION тихо прибирає дублікати. Якщо ви звіряєте кількість і легітимні повтори мали лишитися — потрібен UNION ALL. І навпаки: UNION ALL там, де чекали унікальності, роздує результат.

    Виглядає як робочий підзапит у FROM, а насправді синтаксична помилка. Похідна таблиця без аліаса в більшості СУБД не виконається (PostgreSQL 16 і Oracle — винятки). Давайте кожному підзапиту в FROM ім'я — запит буде переносним.

    Виглядає як скалярний підзапит, а насправді кілька рядків. = (SELECT ...), що зненацька повертає більше одного рядка, валить запит помилкою. Часто це сигнал, що дані не унікальні там, де ви припускали унікальність.

    Виглядає як «CTE завжди повільніший», а насправді залежить від СУБД і версії. Твердження про «optimization fence» стосується старих версій PostgreSQL; від PG 12 простий CTE вбудовується. Не діагноз, а привід глянути EXPLAIN.

    Підсумок

    • Підзапит живе у WHERE (фільтр/набір), FROM (похідна таблиця, потрібен аліас) або SELECT (обчислюваний стовпець); скалярний повертає рівно одне значення.
    • Корельований підзапит посилається на зовнішній рядок і концептуально виконується для кожного з них; EXISTS — його природна пара.
    • NOT IN з NULL у наборі повертає нуль рядків завжди — для заперечень беріть NOT EXISTS чи LEFT JOIN ... IS NULL.
    • UNION прибирає дублікати (дорого), UNION ALL — ні; за замовчуванням UNION ALL, доки дедуплікація справді не потрібна. INTERSECT/EXCEPT порівнюють набори і теж дедуплікують.
    • CTE (WITH) не додає потужності — додає читабельність і повторне використання; «другий за величиною» і «записи без пари» — дві задачі, які треба вміти писати не думаючи.

    Що питають на співбесіді

    • «Чим EXISTS відрізняється від IN? Що швидше?» Інтерв'юер хоче почути, що семантично для позитивної перевірки вони близькі, EXISTS зупиняється на першому збігу, а «швидше» вирішує план виконання, а не забобон. Плюс бали за згадку NOT IN + NULL.
    • «Що поверне NOT IN з підзапитом, у якому є NULL?» Пряма перевірка на тризначну логіку. Правильна відповідь — «нуль рядків, бо x <> NULL дає UNKNOWN» — і одразу безпечна альтернатива NOT EXISTS.
    • «UNION чи UNION ALL — і чому це впливає на продуктивність?» Дивляться, чи розумієте, що UNION доплачує за дедуплікацію, і чи берете UNION ALL за замовчуванням.
    • «Знайдіть другу за величиною зарплату.» Класика. Цінують уточнення про дублікати й NULL, робочий запит і згадку віконних функцій як альтернативи.
    • «Знайдіть записи без пари» / «клієнтів без замовлень». Перевіряють, чи знаєте всі три способи анти-з'єднання і чому NOT IN тут ризикований.
    • «Навіщо CTE, якщо є підзапити?» Правильний акцент — читабельність і повторне використання, а не міфічна швидкість; бонус за нюанс матеріалізації.

    Джерела

    • PostgreSQL: WITH Queries (Common Table Expressions) — CTE, рекурсія, MATERIALIZED.
    • PostgreSQL: Combining Queries (UNION, INTERSECT, EXCEPT) — операції з множинами і правила дедуплікації.
    • PostgreSQL: Subquery Expressions — EXISTS, IN, NOT IN, ANY/ALL і їхня семантика з NULL.
    • MySQL: Subqueries — та сама механіка в діалекті MySQL, корисно для порівняння.
    • Стандарт мови — ISO/IEC 9075 (SQL); підзапити, WITH і операції з множинами описані стандартом і в основних СУБД поводяться майже однаково — відмінності діалектів дрібні (MINUS в Oracle, синтаксис LIMIT/OFFSET, необов'язковість аліаса).