Підзапити, CTE, множини

Що таке підзапит і де його можна використовувати?

Підзапит — запит усередині іншого запиту, у дужках.

• Скалярний — повертає одне значення, можна порівнювати: WHERE amount > (SELECT AVG(amount) …).
• У WHERE з IN / EXISTS — перевірка входження чи існування.
• У FROM — тимчасова таблиця, обов'язково з аліасом.
• Корельований — посилається на зовнішній запит і логічно виконується для кожного його рядка.

-- транзакції, більші за середню
SELECT id, amount FROM transactions
WHERE amount > (SELECT AVG(amount) FROM transactions);

-- корельований: найбільша транзакція кожного рахунку
SELECT t.* FROM transactions t
WHERE t.amount = (SELECT MAX(t2.amount)
                  FROM transactions t2
                  WHERE t2.account_id = t.account_id);

Чим IN відрізняється від EXISTS?

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

Для позитивних перевірок сучасні оптимізатори часто виконують обидва варіанти однаково. Реальна різниця — у роботі з NULL у заперечній формі: NOT EXISTS поводиться передбачувано, а NOT IN — ні (див. наступне питання).

-- користувачі, що мають хоч одну невдалу транзакцію
SELECT u.email FROM users u
WHERE EXISTS (
  SELECT 1 FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  WHERE a.user_id = u.id AND t.status = 'failed'
);

Чому NOT IN може повернути нуль рядків?

Якщо підзапит у NOT IN повертає хоча б один NULL, умова для кожного рядка стає UNKNOWN, і результат — порожній. Без жодної помилки.

Логіка: 5 NOT IN (1, 2, NULL) означає 5 <> 1 AND 5 <> 2 AND 5 <> NULL. Останнє — UNKNOWN, отже вся умова не TRUE.

Рішення: NOT EXISTS, anti-join через LEFT JOIN або явно відфільтрувати NULL у підзапиті.

-- якщо хоч один account.user_id = NULL — поверне 0 рядків
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM accounts);

-- безпечно
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM accounts a WHERE a.user_id = u.id);

Що таке CTE (WITH) і навіщо він потрібен?

CTE (Common Table Expression) — іменований тимчасовий результат, оголошений через WITH перед основним запитом. Те саме, що підзапит у FROM, але з назвою.

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

WITH monthly AS (
  SELECT a.user_id, SUM(t.amount) AS deposits
  FROM transactions t
  JOIN accounts a ON a.id = t.account_id
  WHERE t.type = 'deposit' AND t.status = 'completed'
    AND t.created_at >= '2026-09-01' AND t.created_at < '2026-10-01'
  GROUP BY a.user_id
)
SELECT u.email, m.deposits
FROM monthly m
JOIN users u ON u.id = m.user_id
WHERE m.deposits > 10000
ORDER BY m.deposits DESC;

Чим UNION відрізняється від UNION ALL? Що таке INTERSECT і EXCEPT?

• UNION об'єднує результати двох запитів і прибирає дублікати — тому повільніший.
• UNION ALL об'єднує все як є, з дублікатами. Якщо дублікатів бути не може або вони потрібні — бери його.
• INTERSECT — рядки, що є в обох результатах.
• EXCEPT — рядки з першого, яких немає в другому (в Oracle — MINUS).

Умова: однакова кількість стовпців і сумісні типи.

-- email-и, що є в старій системі, але відсутні в новій після міграції
SELECT email FROM legacy_users
EXCEPT
SELECT email FROM users;