SQL for QA Interview Guide

Що це. Базовий запит читається як речення: SELECT — які колонки, FROM — з якої таблиці, WHERE — які рядки, ORDER BY — у якому порядку, LIMIT — скільки. Аліас через AS дає колонці чи таблиці тимчасове ім'я лише в межах цього запиту: total AS amount перейменує колонку у виводі, а FROM orders o дозволяє писати o.status.

What it is. A basic query reads like a sentence: SELECT — which columns, FROM — which table, WHERE — which rows, ORDER BY — in what order, LIMIT — how many. An alias with AS gives a column or table a temporary name inside this query only: total AS amount renames the output column, and FROM orders o lets you write o.status.

Сортування і сторінки. ORDER BY total DESC сортує від більшого до меншого, ASC — за замовчуванням. LIMIT 10 OFFSET 20 пропускає 20 рядків і повертає наступні 10, тобто рядки 21–30. ORDER BY 2 означає «за другою колонкою зі списку SELECT» — коротко, але крихко: хтось додасть колонку на початок, і сортування тихо зміниться.

Sorting and paging. ORDER BY total DESC sorts from largest to smallest; ASC is the default. LIMIT 10 OFFSET 20 skips 20 rows and returns the next 10, i.e. rows 21–30. ORDER BY 2 means «by the second column of the SELECT list» — short but brittle: someone adds a column at the front and the sort silently changes.

Підводні камені. Без ORDER BY порядок рядків не гарантований, навіть якщо сьогодні він «завжди однаковий». Тест, який бере «перший рядок» без сортування, стає нестабільним після VACUUM, оновлення PostgreSQL чи паралельного плану. Пагінація з OFFSET по неунікальному ключу (наприклад, лише за status) може показати один рядок на двох сторінках і загубити інший — додавай унікальний ключ останнім у ORDER BY.

Pitfalls. Without ORDER BY the row order is not guaranteed, even if today it is «always the same». A test that takes «the first row» without sorting turns flaky after a VACUUM, a PostgreSQL upgrade or a parallel plan. OFFSET paging on a non-unique key (say, only status) can show one row on two pages and lose another — put a unique key last in ORDER BY.

Порядок. Пишемо ми SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, а логічно база обробляє так: FROM і JOIN → WHERE → GROUP BY → HAVING → SELECT (разом з віконними функціями і DISTINCT) → ORDER BY → LIMIT/OFFSET. Оптимізатор може фізично робити інакше, але результат мусить бути таким, ніби кроки йшли саме так.

The order. We write SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but logically the database processes: FROM and JOINs → WHERE → GROUP BY → HAVING → SELECT (with window functions and DISTINCT) → ORDER BY → LIMIT/OFFSET. The optimizer may physically do something else, but the result has to look as if the steps ran in this order.

Навіщо це знати. З порядку випливає половина «дивних» помилок. Аліас із SELECT не видно у WHERE, бо WHERE виконується раніше: WHERE amount > 100 дасть column "amount" does not exist. Агрегат не можна поставити у WHERE, бо груп ще немає, — для цього існує HAVING. Віконну функцію не можна фільтрувати в тому ж запиті — її загортають у підзапит або CTE. А в ORDER BY аліас уже доступний.

Why you need it. Half of the «weird» errors follow from this order. A SELECT alias is invisible in WHERE because WHERE runs earlier: WHERE amount > 100 gives column "amount" does not exist. An aggregate cannot go into WHERE because the groups do not exist yet — that is what HAVING is for. A window function cannot be filtered in the same query — wrap it in a subquery or CTE. In ORDER BY, though, the alias is already visible.

Нюанс діалектів. PostgreSQL дозволяє аліаси з SELECT у GROUP BY (як розширення), MySQL — навіть у HAVING. Стандарт цього не вимагає, тому переносний запит повторює вираз. У тестових перевірках я обираю переносний варіант, бо той самий SQL часто запускають на різних СУБД.

Dialect nuance. PostgreSQL accepts SELECT aliases in GROUP BY (as an extension), and MySQL even in HAVING. The standard does not require it, so a portable query repeats the expression. In test checks I pick the portable form, because the same SQL often runs against different databases.

LIKE. Шаблон 'An%' знаходить рядки, що починаються з «An»: % — будь-яка кількість символів, _ — рівно один. У PostgreSQL LIKE чутливий до регістру, тому 'an%' не знайде «Anna»; для цього є ILIKE (розширення PostgreSQL) або LOWER(name) LIKE 'an%'. У MySQL звичайні колації регістронезалежні, і той самий запит поводиться інакше.

LIKE. The pattern 'An%' matches rows starting with «An»: % is any number of characters, _ is exactly one. In PostgreSQL LIKE is case-sensitive, so 'an%' will not find «Anna»; use ILIKE (a PostgreSQL extension) or LOWER(name) LIKE 'an%'. In MySQL the usual collations are case-insensitive, and the same query behaves differently.

BETWEEN. price BETWEEN 10 AND 20 — це price >= 10 AND price <= 20, обидві межі включно. З датами це підступно: created_at BETWEEN '2026-03-01' AND '2026-03-31' для timestamp зупиниться на 31 березня 00:00 і загубить увесь останній день. Для часу надійніше напіввідкритий інтервал >= '2026-03-01' AND < '2026-04-01'.

BETWEEN. price BETWEEN 10 AND 20 is price >= 10 AND price <= 20, both bounds included. With dates this is sneaky: created_at BETWEEN '2026-03-01' AND '2026-03-31' on a timestamp stops at March 31 00:00 and loses the whole last day. For time a half-open range >= '2026-03-01' AND < '2026-04-01' is safer.

IN. status IN ('paid', 'shipped') — коротший запис кількох OR, і він приймає також підзапит. Для тестів IN зручний, щоб перевірити одразу кілька очікуваних станів: WHERE id IN (101, 102) AND status NOT IN ('paid', 'shipped') має повернути порожній результат. Пастка з NULL живе в NOT IN — про неї окрема картка в розділі підзапитів.

IN. status IN ('paid', 'shipped') is a shorter form of several ORs and also accepts a subquery. In tests IN is handy for checking several expected states at once: WHERE id IN (101, 102) AND status NOT IN ('paid', 'shipped') should return an empty result. The NULL trap lives in NOT IN — it gets its own card in the subqueries section.

Що це. DISTINCT прибирає з результату повністю однакові рядки — порівнюються всі колонки SELECT разом, а не лише перша. SELECT DISTINCT city FROM users дасть кожне місто один раз, причому NULL для DISTINCT вважаються однаковими й залишаться одним рядком. COUNT(DISTINCT city) рахує унікальні значення без NULL.

What it is. DISTINCT removes rows that are identical across all SELECT columns together, not just the first one. SELECT DISTINCT city FROM users returns each city once, and for DISTINCT NULLs count as equal, so they stay as a single row. COUNT(DISTINCT city) counts unique values excluding NULL.

Коли доречно. Для довідників («які статуси взагалі є в таблиці»), для перевірки, що колонка містить лише дозволені значення, і для підрахунку унікальних покупців. У PostgreSQL є ще DISTINCT ON (col) — «перший рядок на кожне значення col» за ORDER BY, це розширення, якого немає в MySQL.

When it fits. For lookups («which statuses exist in the table at all»), for checking that a column holds only allowed values, and for counting unique buyers. PostgreSQL also has DISTINCT ON (col) — «the first row per value of col» by ORDER BY; it is an extension MySQL does not have.

Підводний камінь. Якщо після JOIN з'явилися дублікати і хтось «вилікував» їх DISTINCT, це майже завжди маскування: JOIN множить рядки, бо ключ з'єднання неунікальний, а DISTINCT ховає симптом і дорого сортує. Якщо в такому запиті є SUM, він усе одно порахований по розмножених рядках. На рев'ю тестових запитів DISTINCT — привід спитати «звідки дублікати?».

Pitfall. If a JOIN produced duplicates and someone «fixed» them with DISTINCT, it is almost always masking: the JOIN multiplies rows because the join key is not unique, and DISTINCT hides the symptom while paying for an expensive sort. If the query has a SUM, it is still computed over the multiplied rows. When reviewing test queries, a DISTINCT is a cue to ask «where do the duplicates come from?».

Що таке NULL. NULL — це не нуль і не порожній рядок, а «значення невідоме». Будь-яке порівняння з NULL через =, <>, > дає не TRUE і не FALSE, а третій стан — UNKNOWN. Навіть NULL = NULL — це UNKNOWN. WHERE пропускає лише рядки з TRUE, тому WHERE city = NULL завжди повертає порожній результат без жодної помилки.

What NULL is. NULL is not zero and not an empty string but «value unknown». Any comparison with NULL through =, <>, > yields neither TRUE nor FALSE but a third state, UNKNOWN. Even NULL = NULL is UNKNOWN. WHERE lets through only rows where the condition is TRUE, so WHERE city = NULL always returns an empty result with no error.

Як правильно. Для перевірки є окремі оператори IS NULL та IS NOT NULL. Коли треба порівняти дві колонки, які обидві можуть бути NULL, у PostgreSQL є IS DISTINCT FROM / IS NOT DISTINCT FROM: вони трактують два NULL як рівні. У MySQL аналог — оператор <=>.

The right way. Checking needs the dedicated operators IS NULL and IS NOT NULL. To compare two columns that may both be NULL, PostgreSQL has IS DISTINCT FROM / IS NOT DISTINCT FROM, which treat two NULLs as equal. MySQL's counterpart is the <=> operator.

Пастка для QA. Умова city <> 'Kyiv' не поверне рядки, де city = NULL. Тест «усі користувачі не з Києва отримали розсилку» тоді мовчки пропустить людей без міста. Коли пишеш заперечну умову по колонці, яка допускає NULL, одразу вирішуй, куди мають потрапити NULL-рядки. Те саме з JOIN: рядки з NULL у ключі з'єднання не з'єднаються ні з чим, навіть з іншим NULL.

A QA trap. The condition city <> 'Kyiv' will not return rows where city is NULL. A test like «every user outside Kyiv got the newsletter» then silently skips people with no city. Whenever you write a negative condition on a nullable column, decide right away where the NULL rows should go. The same applies to JOINs: rows with NULL in the join key match nothing, not even another NULL.

COALESCE. COALESCE(a, b, c) повертає перший аргумент, який не NULL. Типове застосування — показати 0 замість NULL: COALESCE(SUM(amount), 0) для покупця без оплат або COALESCE(city, 'unknown') у звіті. Це стандартна функція; IFNULL (MySQL) та NVL (Oracle) роблять те саме, але лише з двома аргументами й не переносяться між базами. Усі аргументи мають бути сумісного типу: COALESCE(total, 'n/a') для числової колонки впаде.

COALESCE. COALESCE(a, b, c) returns the first argument that is not NULL. The typical use is showing 0 instead of NULL: COALESCE(SUM(amount), 0) for a buyer with no payments, or COALESCE(city, 'unknown') in a report. It is a standard function; IFNULL (MySQL) and NVL (Oracle) do the same with only two arguments and do not port between databases. All arguments must have compatible types: COALESCE(total, 'n/a') on a numeric column fails.

CASE WHEN. Це умовна логіка всередині запиту: CASE WHEN умова THEN значення … ELSE інше END. Гілки перевіряються зверху вниз, спрацьовує перша з TRUE; без ELSE результат — NULL. Зручно для категорій («дороге/дешеве»), для людських назв статусів і для умовних підрахунків SUM(CASE WHEN … THEN 1 ELSE 0 END).

CASE WHEN. This is conditional logic inside a query: CASE WHEN condition THEN value … ELSE other END. Branches are checked top to bottom and the first TRUE wins; without ELSE the result is NULL. It is handy for buckets («expensive/cheap»), for human-readable status names and for conditional counts SUM(CASE WHEN … THEN 1 ELSE 0 END).

Пастка. COALESCE у звіті змінює зміст: 0 означає «оплат нуль», а NULL — «даних немає». Для QA-перевірки інколи чесніше залишити NULL і окремо рахувати, скільки таких рядків. І CASE WHEN city = NULL не спрацює ніколи — у CASE теж пишуть IS NULL.

Trap. COALESCE in a report changes the meaning: 0 says «zero payments», NULL says «no data». For a QA check it is sometimes more honest to keep NULL and count such rows separately. And CASE WHEN city = NULL never fires — CASE needs IS NULL too.

Склеювання. Стандартний оператор — ||: name || ' <' || email || '>'. Якщо хоч один операнд NULL, весь результат NULL. Функція CONCAT(a, b) пропускає NULL як порожній рядок. У MySQL || за замовчуванням означає логічне OR, тому там пишуть CONCAT — класична причина, чому запит «працював у Postgres і зламався в MySQL».

Concatenation. The standard operator is ||: name || ' <' || email || '>'. If any operand is NULL, the whole result is NULL. The CONCAT(a, b) function treats NULL as an empty string. In MySQL || means logical OR by default, so people write CONCAT there — the classic reason a query «worked in Postgres and broke in MySQL».

Приведення типів. CAST(total AS integer) — стандартний запис, total::integer — скорочення PostgreSQL. Числа при переведенні в integer округлюються (у PostgreSQL 90.50 → 91), тобто не просто обрізаються. Рядок, який не є числом, дає помилку invalid input syntax — це корисно в перевірках імпорту: знайти рядки, що не парсяться.

Type conversion. CAST(total AS integer) is the standard form, total::integer is PostgreSQL shorthand. Casting numbers to integer rounds them (in PostgreSQL 90.50 → 91) rather than simply truncating. A string that is not a number raises invalid input syntax — useful in import checks for finding rows that do not parse.

CHAR проти VARCHAR. CHAR(n) має фіксовану довжину і доповнює значення пробілами, VARCHAR(n) зберігає рівно введене в межах ліміту. У PostgreSQL text і varchar працюють однаково швидко, а CHAR майже ніколи не потрібен. Для тестів важливо: у CHAR хвостові пробіли ігноруються при порівнянні, а довжина ліміту — це межа для тест-кейсу на граничні значення (n символів проходить, n+1 дає помилку).

CHAR versus VARCHAR. CHAR(n) has a fixed length and pads values with spaces; VARCHAR(n) stores exactly what was entered, within the limit. In PostgreSQL text and varchar are equally fast, and CHAR is almost never needed. For tests this matters: CHAR ignores trailing spaces in comparisons, and the length limit is a boundary for a test case (n characters pass, n+1 fails).

INNER JOIN. Повертає лише пари рядків, для яких умова в ON істинна. Покупець без замовлень не має пари — його у виводі немає. Замовлення без покупця теж зникло б, але в нашій схемі це заборонено зовнішнім ключем. Слово INNER можна не писати: просто JOIN означає те саме.

INNER JOIN. Returns only the row pairs for which the ON condition is true. A buyer with no orders has no pair and is absent from the output. An order without a buyer would vanish too, but our schema forbids that with a foreign key. The word INNER is optional: plain JOIN means the same.

LEFT JOIN. Повертає всі рядки лівої таблиці; де пари справа немає, колонки правої таблиці заповнюються NULL. Тому users LEFT JOIN orders покаже і Anna K., яка нічого не купувала, — з NULL у колонках замовлення. Порядок таблиць важливий: orders LEFT JOIN users — вже зовсім інший запит.

LEFT JOIN. Returns every row of the left table; where there is no match on the right, the right-hand columns are filled with NULL. So users LEFT JOIN orders also shows Anna K., who bought nothing, with NULL in the order columns. Table order matters: orders LEFT JOIN users is a completely different query.

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

How to choose. The question is «do I need the rows that have no match?». A report of «how many orders each buyer has» needs LEFT, otherwise buyers with zero simply disappear, and a test comparing the report row count with the user count fails. A check that «every order has a buyer» looks for unmatched rows instead. One more thing for QA: a JOIN does not shrink or grow the row count «by itself» — it yields one row per matching pair.

RIGHT JOIN. Дзеркало LEFT JOIN: зберігає всі рядки правої таблиці. a RIGHT JOIN b дає те саме, що b LEFT JOIN a, тому в коді його пишуть рідко — більшості людей легше читати запит, де «головна» таблиця стоїть першою. На співбесіді достатньо сказати, що це еквівалент із переставленими таблицями.

RIGHT JOIN. The mirror of LEFT JOIN: it keeps every row of the right table. a RIGHT JOIN b gives the same as b LEFT JOIN a, so it is rare in code — most people find a query easier to read when the «main» table comes first. In an interview it is enough to say it is the equivalent with the tables swapped.

FULL OUTER JOIN. Повертає всі рядки обох таблиць: пари з'єднуються, а рядки без пари з будь-якого боку додаються з NULL на іншому боці. Це природний інструмент звірки двох джерел: замовлення проти платежів, стара таблиця проти нової після міграції, наш облік проти вивантаження провайдера. В одному запиті видно і «є в нас, немає в них», і «є в них, немає в нас».

FULL OUTER JOIN. Returns every row of both tables: matching rows are paired, and unmatched rows from either side are added with NULL on the other side. It is the natural tool for reconciling two sources: orders against payments, the old table against the new one after a migration, our ledger against a provider export. One query shows both «we have it, they don't» and «they have it, we don't».

Нюанси. MySQL не підтримує FULL OUTER JOIN — його емулюють через LEFT JOIN … UNION … RIGHT JOIN. Умову фільтра на таблицю з «опціонального» боку треба ставити в ON або перевіряти разом з IS NULL, інакше FULL JOIN мовчки перетвориться на звичайний. Щоб результат читався як звіт, додають колонку-діагноз через CASE: missing_payment, orphan_payment, ok.

Nuances. MySQL does not support FULL OUTER JOIN — it is emulated with LEFT JOIN … UNION … RIGHT JOIN. A filter on the «optional» side belongs in ON or has to allow IS NULL, otherwise the FULL JOIN silently becomes an ordinary one. To make the output read like a report, add a diagnosis column via CASE: missing_payment, orphan_payment, ok.

CROSS JOIN. Декартів добуток: кожен рядок першої таблиці з кожним рядком другої, без умови. 5 покупців × 4 товари = 20 рядків. Випадковий CROSS JOIN — класичний баг: забута умова в старому синтаксисі FROM a, b дає мільйони рядків і «вбиває» базу. Навмисно його використовують, щоб згенерувати всі комбінації: матрицю «кожен статус × кожен день» для звіту без пропусків чи набір тестових даних «кожен тип користувача × кожен тариф».

CROSS JOIN. The Cartesian product: every row of the first table with every row of the second, with no condition. 5 buyers × 4 products = 20 rows. An accidental CROSS JOIN is a classic bug: a missing condition in the old FROM a, b syntax produces millions of rows and «kills» the database. It is used on purpose to generate every combination: a «each status × each day» matrix for a report with no gaps, or test data like «each user type × each plan».

Множення рядків. Будь-який JOIN — це відфільтрований CROSS JOIN. Якщо зліва 3 рядки з key = 1, а справа 2 рядки з key = 1, INNER JOIN за key дасть 3 × 2 = 6 рядків: кожен лівий зустрінеться з кожним правим. Питання з квізу саме про це, і правильна відповідь — 6, а не 2 чи 3.

Row multiplication. Every JOIN is a filtered CROSS JOIN. If the left side has 3 rows with key = 1 and the right side has 2 rows with key = 1, an INNER JOIN on key yields 3 × 2 = 6 rows: each left row meets each right row. The quiz question is exactly about this, and the right answer is 6, not 2 or 3.

Чому це важливо для QA. Коли в звіті сума раптом удвічі більша, першою гіпотезою має бути неунікальний ключ з'єднання. Перевірка проста: до і після JOIN порахувати count(*) і перевірити унікальність ключа з кожного боку через GROUP BY key HAVING count(*) > 1.

Why QA cares. When a report total is suddenly doubled, the first hypothesis should be a non-unique join key. The check is simple: count count(*) before and after the JOIN and verify the key is unique on each side with GROUP BY key HAVING count(*) > 1.

Що це. Self join — з'єднання таблиці самої з собою. Окремого ключового слова немає: це звичайний JOIN, де та сама таблиця з'являється двічі під різними аліасами. Аліаси тут обов'язкові, інакше база не зрозуміє, яка з двох копій має на увазі id.

What it is. A self join joins a table to itself. There is no special keyword: it is an ordinary JOIN where the same table appears twice under different aliases. The aliases are mandatory here, otherwise the database cannot tell which of the two copies id refers to.

Коли потрібен. Для ієрархій і зв'язків усередині однієї таблиці: співробітник і його менеджер, категорія і батьківська категорія, у нашій схемі — покупець і той, хто його запросив (referred_by). А ще для порівняння рядків між собою: знайти двох користувачів з однаковим email без урахування регістру, замовлення одного покупця, створені з різницею менше хвилини (підозра на подвійний клік), чи пари товарів з однаковою ціною.

When you need it. For hierarchies and links inside one table: an employee and their manager, a category and its parent, and in our schema a buyer and whoever referred them (referred_by). Also for comparing rows with each other: finding two users with the same email ignoring case, orders by one buyer created less than a minute apart (a double-click suspect), or pairs of products with the same price.

Підводні камені. Для запрошень потрібен LEFT JOIN, інакше покупці без реферера зникнуть. При пошуку пар умова a.id < b.id прибирає і пару «рядок сам із собою», і дзеркальні дублікати (1–4 та 4–1). Для глибокої ієрархії (запрошений запрошеним запрошеного) self join на кожен рівень не масштабується — там потрібен рекурсивний CTE.

Pitfalls. Referrals need a LEFT JOIN, otherwise buyers without a referrer disappear. When looking for pairs, the condition a.id < b.id removes both the «row with itself» pair and the mirrored duplicates (1–4 and 4–1). For a deep hierarchy (the referral of a referral of a referral) one self join per level does not scale — that needs a recursive CTE.

Що це. Anti-join — «рядки з A, для яких немає пари в B». Для QA це основний запит перевірки цілісності: товари, яких ніхто не купував, замовлення без позицій, платежі на неіснуючі замовлення, користувачі без профілю після реєстрації.

What it is. An anti-join is «rows of A with no match in B». For QA it is the core integrity-check query: products nobody bought, orders without items, payments for orders that do not exist, users without a profile after sign-up.

Три способи. (1) LEFT JOIN b … WHERE b.key IS NULL — перевіряти треба колонку, яка в B не може бути NULL (краще первинний ключ), інакше справжні пари з NULL у цій колонці теж потраплять у вивід. (2) WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.key = a.key) — найчитабельніше і безпечне щодо NULL; PostgreSQL виконує його як той самий anti-join. (3) SELECT key FROM a EXCEPT SELECT key FROM b — порівнює набори значень і прибирає дублікати, але повертає тільки ключі, без інших колонок. У MySQL EXCEPT з'явився лише у 8.0.31.

Three ways. (1) LEFT JOIN b … WHERE b.key IS NULL — you must test a column that cannot be NULL in B (the primary key is best), otherwise genuine matches with NULL in that column show up too. (2) WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.key = a.key) — the most readable and NULL-safe; PostgreSQL runs it as the same anti-join. (3) SELECT key FROM a EXCEPT SELECT key FROM b — compares sets of values and removes duplicates, but returns only the keys, no other columns. MySQL got EXCEPT only in 8.0.31.

Чого уникати. NOT IN (SELECT key FROM b) ламається, якщо в підзапиті є хоч один NULL, — тоді результат порожній. Детально про це окрема картка. Практична порада: називай такі запити за тим, що вони шукають (orphan_payments, orders_without_items), і збирай їх в один набір перевірок.

What to avoid. NOT IN (SELECT key FROM b) breaks if the subquery has even one NULL — the result is then empty. That gets a card of its own. A practical tip: name such queries after what they look for (orphan_payments, orders_without_items) and collect them into one set of checks.

Суть. Для INNER JOIN різниці немає — результат той самий. Для LEFT JOIN вона принципова. Умова в ON вирішує, які рядки справа вважаються парою; лівий рядок залишається в будь-якому разі, просто з NULL, якщо пари не знайшлося. Умова в WHERE виконується після з'єднання і фільтрує вже готові рядки — а в рядках без пари праві колонки NULL, тож WHERE o.status = 'paid' їх відкидає. LEFT JOIN мовчки стає INNER.

The point. For an INNER JOIN there is no difference — the result is the same. For a LEFT JOIN it is fundamental. A condition in ON decides which right-hand rows count as a match; the left row stays regardless, just with NULLs if nothing matched. A condition in WHERE runs after the join and filters finished rows — and unmatched rows have NULL in the right-hand columns, so WHERE o.status = 'paid' throws them away. The LEFT JOIN silently becomes an INNER one.

Приклад з проєкту. Звіт «кількість оплачених замовлень на кожного покупця» має показати всіх п'ятьох, навіть з нулем. Якщо фільтр статусу поставити у WHERE, зникнуть Olena (лише скасоване замовлення) та Anna K. (без замовлень), і тест, що перевіряє «рядків у звіті стільки ж, скільки користувачів», впаде — або, гірше, хтось «підправить» очікування.

A project example. A report of «number of paid orders per buyer» must show all five buyers, even with zero. Put the status filter in WHERE and Olena (only a cancelled order) and Anna K. (no orders) disappear, and a test checking «the report has as many rows as there are users» fails — or worse, someone «adjusts» the expectation.

Правило. Умови на праву (опціональну) таблицю — в ON; умови на ліву таблицю — у WHERE. Виняток — навмисний WHERE right.key IS NULL для anti-join. На рев'ю запитів це одна з найчастіших знахідок: LEFT JOIN плюс WHERE по правій таблиці. Поставити фільтр лівої таблиці в ON — теж помилка навпаки: лівий рядок усе одно лишиться у виводі, лише втратить пару, тож фільтр нічого не відфільтрує.

The rule. Conditions on the right (optional) table go into ON; conditions on the left table go into WHERE. The exception is a deliberate WHERE right.key IS NULL for an anti-join. In query reviews this is one of the most frequent findings: a LEFT JOIN plus WHERE on the right table. Putting a left-table filter into ON is the opposite mistake: the left row still stays in the output, it just loses its match, so the filter filters nothing.

USING. JOIN payments USING (order_id) — скорочення для ON a.order_id = b.order_id, коли колонки з обох боків називаються однаково. Бонус: у результаті буде одна колонка order_id, а не дві, і її можна писати без префікса таблиці. Працює в PostgreSQL, MySQL, Oracle; зручно у схемах, де ключі послідовно названі.

USING. JOIN payments USING (order_id) is shorthand for ON a.order_id = b.order_id when the columns are named the same on both sides. Bonus: the result has a single order_id column instead of two, and you can refer to it without a table prefix. It works in PostgreSQL, MySQL and Oracle and is handy in schemas with consistently named keys.

NATURAL JOIN. З'єднує за всіма колонками з однаковими іменами, які знайде. Умова неявна: її не видно в запиті, і вона змінюється, коли хтось додає колонку. У нашій схемі orders NATURAL JOIN payments з'єднає за id і status — бо обидві таблиці мають такі колонки, хоча orders.id і payments.id означають зовсім різне. Результат — тихо неправильний.

NATURAL JOIN. Joins on all columns with matching names it can find. The condition is implicit: you cannot see it in the query, and it changes when someone adds a column. In our schema orders NATURAL JOIN payments joins on id and status, because both tables have those columns, even though orders.id and payments.id mean completely different things. The result is silently wrong.

Чому це важливо. Типова історія: додали в обидві таблиці колонку updated_at, і NATURAL JOIN почав повертати майже нуль рядків — без жодної помилки. Тому в продакшн-коді й у тестових перевірках пишуть явний ON (або USING для однойменних ключів), а NATURAL лишають для швидких експериментів у консолі.

Why it matters. A typical story: someone adds an updated_at column to both tables, and the NATURAL JOIN starts returning almost no rows — without any error. That is why production code and test checks spell out ON (or USING for same-named keys), and NATURAL stays for quick experiments in the console.

Агрегати. Стандартні агрегатні функції — COUNT, SUM, AVG, MIN, MAX. Вони згортають багато рядків в одне значення. Без GROUP BY агрегат рахується по всій таблиці (після WHERE) і повертає рівно один рядок, навіть якщо таблиця порожня: count(*) тоді дасть 0, а sum — NULL.

Aggregates. The standard aggregate functions are COUNT, SUM, AVG, MIN, MAX. They fold many rows into one value. Without GROUP BY an aggregate runs over the whole table (after WHERE) and returns exactly one row, even when the table is empty: count(*) then gives 0 and sum gives NULL.

GROUP BY. Розбиває рядки на групи з однаковими значеннями вказаних колонок і рахує агрегат для кожної групи окремо. SELECT user_id, count(*) FROM orders GROUP BY user_id — класична відповідь на питання «скільки замовлень у кожного покупця». Групувати можна за кількома колонками (user_id, status) і за виразом, наприклад за місяцем date_trunc('month', created_at).

GROUP BY. Splits rows into groups with equal values in the listed columns and computes the aggregate for each group separately. SELECT user_id, count(*) FROM orders GROUP BY user_id is the classic answer to «how many orders does each buyer have». You can group by several columns (user_id, status) and by an expression such as the month, date_trunc('month', created_at).

Нюанси для перевірок. Групування по orders не покаже покупців без замовлень — для них потрібен LEFT JOIN від users. NULL у колонці групування утворює окрему групу. А порожня таблиця з GROUP BY поверне нуль рядків, а не рядок з нулем, — тест, що чекає «0» у першому рядку, отримає порожній результат і впаде з незрозумілою помилкою.

Nuances for checks. Grouping over orders will not show buyers with no orders — that needs a LEFT JOIN from users. NULL in a grouping column forms a group of its own. And an empty table with GROUP BY returns zero rows, not a row with a zero — a test expecting «0» in the first row gets an empty result and fails with a confusing error.

Різниця. COUNT(*) рахує рядки, незалежно від вмісту. COUNT(col) рахує рядки, де col не NULL. COUNT(DISTINCT col) рахує унікальні значення без NULL. На звичайній таблиці різниця видна лише для колонок з NULL, а після LEFT JOIN вона стає критичною.

The difference. COUNT(*) counts rows regardless of content. COUNT(col) counts rows where col is not NULL. COUNT(DISTINCT col) counts unique values without NULL. On a plain table the difference only shows for nullable columns; after a LEFT JOIN it becomes critical.

Пастка з LEFT JOIN. Покупець без замовлень після users LEFT JOIN orders все одно дає один рядок — з NULL в колонках замовлення. COUNT(*) порахує його як 1, і звіт покаже, що в Anna K. одне замовлення. Правильно — COUNT(o.id): NULL не рахується, і виходить 0. Це одна з найпоширеніших помилок у звітах, яку QA ловить, порівнюючи звіт з даними одного конкретного покупця.

The LEFT JOIN trap. A buyer with no orders still yields one row after users LEFT JOIN orders — with NULL in the order columns. COUNT(*) counts it as 1, and the report says Anna K. has one order. The right form is COUNT(o.id): NULL is not counted, and you get 0. It is one of the most common report bugs, and QA catches it by comparing the report with the data of one specific buyer.

DISTINCT. Якщо в запиті ще й приєднано позиції замовлень, рядків стає більше, ніж замовлень, і лише COUNT(DISTINCT o.id) дасть кількість замовлень. Але DISTINCT тут — ознака, що запит варто переписати: рахувати в підзапиті до JOIN, бо з SUM такий трюк не спрацює. Щодо швидкодії: COUNT(1) не швидший за COUNT(*) — результат однаковий, а в PostgreSQL COUNT(*) навіть трохи дешевший, бо не перевіряє аргумент на NULL.

DISTINCT. If order items are joined in as well, there are more rows than orders, and only COUNT(DISTINCT o.id) gives the number of orders. But a DISTINCT here signals the query should be rewritten to count in a subquery before the JOIN, because this trick does not work for SUM. On performance: COUNT(1) is not faster than COUNT(*) — the result is the same, and in PostgreSQL COUNT(*) is even slightly cheaper because it does not check an argument for NULL.

Різниця. WHERE фільтрує окремі рядки до групування, HAVING — готові групи після GROUP BY. Тому агрегат у WHERE заборонений (груп ще немає): WHERE count(*) > 1 дає помилку aggregate functions are not allowed in WHERE. А в HAVING можна і агрегати, і колонки з GROUP BY.

The difference. WHERE filters individual rows before grouping, HAVING filters finished groups after GROUP BY. That is why an aggregate is forbidden in WHERE (there are no groups yet): WHERE count(*) > 1 raises aggregate functions are not allowed in WHERE. HAVING accepts both aggregates and GROUP BY columns.

Як писати. «Покупці з більш ніж N замовленнями»: GROUP BY user_id HAVING count(*) > N. Якщо треба рахувати лише неcкасовані замовлення, умова на статус іде у WHERE — вона відкидає рядки до підрахунку. Якщо поставити її в HAVING, запит або не скомпілюється, або рахуватиме не те. Умови, які можна перевірити на окремому рядку, завжди краще тримати у WHERE: менше рядків потрапляє в групування, і планувальник може використати індекс.

How to write it. «Buyers with more than N orders»: GROUP BY user_id HAVING count(*) > N. If only non-cancelled orders should count, the status condition goes into WHERE — it drops rows before counting. Put it in HAVING and the query either fails to compile or counts the wrong thing. Conditions you can check on a single row are always better in WHERE: fewer rows go into grouping, and the planner can use an index.

Типова помилка. Сплутати «замовлень більше N» з «сума більше N»: перше — count(*), друге — sum(total). На співбесіді часто просять одразу обидва фільтри — показуй, що WHERE і HAVING можуть бути в одному запиті й роблять різну роботу.

A common mistake. Confusing «more than N orders» with «total above N»: the first is count(*), the second is sum(total). Interviews often ask for both filters at once — show that WHERE and HAVING can sit in one query and do different jobs.

Правило стандарту. Ні: кожна колонка в SELECT має бути або в GROUP BY, або всередині агрегатної функції. Інакше незрозуміло, яке значення показати для групи з кількох рядків. PostgreSQL відповідає помилкою column "o.status" must appear in the GROUP BY clause or be used in an aggregate function.

The standard rule. No: every SELECT column must be either in GROUP BY or inside an aggregate function. Otherwise it is unclear which value to show for a group of several rows. PostgreSQL answers with column "o.status" must appear in the GROUP BY clause or be used in an aggregate function.

Виняток — функціональна залежність. Якщо групуєш за первинним ключем таблиці, PostgreSQL дозволяє вибирати будь-які інші колонки цієї таблиці: при відомому u.id ім'я та місто однозначні. Тому GROUP BY u.id з u.name у SELECT працює, а GROUP BY u.name з u.city — ні.

The exception is functional dependency. If you group by a table's primary key, PostgreSQL lets you select any other column of that table: once u.id is known, the name and city are unambiguous. So GROUP BY u.id with u.name in SELECT works, while GROUP BY u.name with u.city does not.

Різниця діалектів. Старі версії MySQL (до 5.7) або MySQL з вимкненим режимом ONLY_FULL_GROUP_BY приймають такий запит і повертають довільне значення з групи. Це джерело «плаваючих» звітів: сьогодні показує один статус, завтра інший. Якщо запит переноситься з MySQL у PostgreSQL і падає з цією помилкою, це не баг PostgreSQL, а прихований баг у запиті. Виправлення — явно обрати значення: max(status), string_agg або «останній рядок групи» через віконну функцію.

Dialect difference. Old MySQL versions (before 5.7), or MySQL with the ONLY_FULL_GROUP_BY mode switched off, accept such a query and return an arbitrary value from the group. That is a source of «flaky» reports: one status today, another tomorrow. If a query moved from MySQL to PostgreSQL fails with this error, it is not a PostgreSQL bug but a hidden bug in the query. The fix is to choose the value explicitly: max(status), string_agg, or «the latest row of the group» with a window function.

Правило. Усі агрегати, крім COUNT(*), ігнорують NULL. AVG(col) — це SUM(col) / COUNT(col): сума ненульових значень ділиться на їхню кількість, а не на кількість рядків. Якщо всі значення NULL, AVG повертає NULL.

The rule. Every aggregate except COUNT(*) ignores NULL. AVG(col) is SUM(col) / COUNT(col): the sum of non-NULL values is divided by how many of them there are, not by the number of rows. If every value is NULL, AVG returns NULL.

Чому це пастка. Питання, на яке відповідає AVG, залежить від того, що означає NULL. Якщо NULL — «покупець нічого не оплатив», то середня оплата на покупця має враховувати його як 0, і правильно писати AVG(COALESCE(paid, 0)). Якщо NULL — «даних ще немає» (наприклад, оцінка, яку не поставили), ігнорувати його чесно. Бізнес-вимога мусить це визначати, і QA варто спитати про це до того, як писати очікуване значення в тест.

Why it is a trap. The question AVG answers depends on what NULL means. If NULL means «the buyer paid nothing», then the average payment per buyer must count them as 0, and the right form is AVG(COALESCE(paid, 0)). If NULL means «no data yet» (say, a rating nobody has given), ignoring it is honest. The business requirement has to decide, and QA should ask before writing the expected value into a test.

Ще нюанси. AVG від цілих чисел у PostgreSQL повертає numeric, тому дробова частина не губиться, а от ділення двох integer через / — ціле: 7 / 2 дає 3. У MySQL AVG теж повертає десяткове значення, але округлення у виводі може відрізнятися — порівнюй з допуском або через round().

More nuances. AVG of integers in PostgreSQL returns numeric, so the fraction is kept, whereas dividing two integers with / is integer division: 7 / 2 gives 3. MySQL AVG also returns a decimal, but rounding in the output may differ — compare with a tolerance or through round().

STRING_AGG. Склеює значення групи в один рядок з роздільником: string_agg(sku, ', ' ORDER BY sku). Це функція PostgreSQL (у SQL Server теж є; у стандарті SQL:2016 і Oracle вона називається LISTAGG); у MySQL аналог — GROUP_CONCAT(sku ORDER BY sku SEPARATOR ', '). Без ORDER BY усередині порядок елементів не гарантований, і тест, що порівнює рядок «SKU-001, SKU-002», стане нестабільним. У MySQL GROUP_CONCAT ще й мовчки обрізає результат до group_concat_max_len (1024 байти за замовчуванням).

STRING_AGG. Glues a group's values into one string with a separator: string_agg(sku, ', ' ORDER BY sku). It is a PostgreSQL function (SQL Server has it too; the SQL:2016 standard and Oracle call it LISTAGG); MySQL's counterpart is GROUP_CONCAT(sku ORDER BY sku SEPARATOR ', '). Without ORDER BY inside, element order is not guaranteed, and a test comparing the string «SKU-001, SKU-002» becomes flaky. MySQL's GROUP_CONCAT also silently truncates the result to group_concat_max_len (1024 bytes by default).

Умовна агрегація. Щоб в одному рядку порахувати кілька різних речей — скільки замовлень оплачено, скільки скасовано, на яку суму, — не потрібні окремі запити. Стандартний запис count(*) FILTER (WHERE status = 'paid') підтримує PostgreSQL; переносний варіант — sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), він працює і в MySQL.

Conditional aggregation. To count several different things in one row — how many orders are paid, how many cancelled, for what amount — you do not need separate queries. The standard form count(*) FILTER (WHERE status = 'paid') is supported by PostgreSQL; the portable version is sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), which works in MySQL too.

Навіщо QA. Такий «зведений» запит — зручний знімок стану до й після тесту: один рядок з лічильниками за статусами, який легко порівняти з очікуванням або з дашбордом. А STRING_AGG робить вивід перевірки людським: замість десяти рядків — один з переліком проблемних id.

Why QA cares. Such a «summary» query is a handy snapshot of state before and after a test: one row of per-status counters that is easy to compare with an expectation or a dashboard. And STRING_AGG makes a check's output human-friendly: one row listing the problem ids instead of ten rows.

UNION проти UNION ALL. Обидва склеюють результати двох запитів «один під одним». UNION прибирає повні дублікати рядків (як DISTINCT, тобто з сортуванням або хешуванням), UNION ALL залишає все й працює швидше. Кількість і типи колонок в обох частинах мають збігатися, а імена колонок береться з першого запиту. ORDER BY застосовується до всього результату і пишеться один раз наприкінці.

UNION versus UNION ALL. Both stack the results of two queries «one under the other». UNION removes exact duplicate rows (like DISTINCT, so with a sort or a hash), UNION ALL keeps everything and is faster. The number and types of columns must match in both parts, and the column names come from the first query. ORDER BY applies to the whole result and is written once at the end.

Коли що. Для підрахунків і звірок майже завжди потрібен UNION ALL: якщо два платежі мають однакову суму й статус, UNION «злипне» їх в один, і сума в перевірці зменшиться. UNION доречний, коли потрібен саме унікальний перелік, наприклад усі email з двох джерел.

Which one when. Counts and reconciliations almost always need UNION ALL: if two payments have the same amount and status, UNION «glues» them into one and the total in your check shrinks. UNION fits when you really want a unique list, say every email from two sources.

INTERSECT і EXCEPT. INTERSECT повертає рядки, що є в обох результатах, EXCEPT — рядки першого, яких немає в другому. Для QA це готові запити звірки: «id, які є в старій таблиці, але зникли в новій». Варіанти з ALL (EXCEPT ALL) враховують кількість повторів. У MySQL INTERSECT і EXCEPT з'явилися тільки у 8.0.31, в Oracle EXCEPT називається MINUS.

INTERSECT and EXCEPT. INTERSECT returns rows present in both results, EXCEPT returns rows of the first that are not in the second. For QA these are ready-made reconciliation queries: «ids that exist in the old table but vanished from the new one». The ALL variants (EXCEPT ALL) respect how many times a row repeats. MySQL got INTERSECT and EXCEPT only in 8.0.31, and Oracle calls EXCEPT MINUS.

Проблема. Коли до замовлень одночасно приєднують дві «дочірні» таблиці — позиції і платежі, — кожна позиція з'єднується з кожним платежем того самого замовлення. Замовлення з двома позиціями і двома платежами дає 4 рядки, і sum(p.amount) подвоюється, а sum(i.qty * i.price) — теж. Помилки немає, числа виглядають правдоподібно, тому баг доживає до продакшн-дашборду. Це називають fan-out або «вибухом» JOIN.

The problem. When two «child» tables — items and payments — are joined to orders at once, each item is paired with each payment of the same order. An order with two items and two payments yields 4 rows, and sum(p.amount) doubles, as does sum(i.qty * i.price). There is no error and the numbers look plausible, so the bug survives all the way to a production dashboard. This is called fan-out or a JOIN «explosion».

Як розпізнати. Тест на звіт має звіряти суму з незалежно порахованим значенням: суму платежів з таблиці payments напряму. Якщо звіт більший у ціле число разів — майже напевно fan-out. Діагностика: порахувати рядки на ключ після JOIN (count(*) на order_id) і порівняти з кількістю позицій та платежів.

How to spot it. A report test should reconcile the total with an independently computed value: the sum of payments straight from the payments table. If the report is larger by a whole multiple, it is almost certainly fan-out. To diagnose, count rows per key after the JOIN (count(*) per order_id) and compare with the number of items and payments.

Виправлення. Агрегувати кожну дочірню таблицю окремо до рівня ключа (у CTE чи підзапиті), а вже потім з'єднувати агрегати 1:1. Альтернатива — корельовані підзапити в SELECT. DISTINCT усередині SUM (sum(DISTINCT amount)) — не виправлення: він склеїть два різні платежі з однаковою сумою, як на замовленні 106. На рев'ю звітних запитів я перевіряю, що кожен JOIN іде по ключу, унікальному з одного боку.

The fix. Aggregate each child table separately to the key level (in a CTE or subquery), then join the aggregates 1:1. The alternative is correlated subqueries in SELECT. DISTINCT inside SUM (sum(DISTINCT amount)) is not a fix: it merges two different payments with the same amount, like on order 106. When reviewing report queries I check that every JOIN goes over a key that is unique on one side.

Підзапит. Це запит усередині іншого запиту, у дужках. Залежно від місця і того, що він повертає, розрізняють кілька видів. Скалярний повертає рівно одне значення (1 рядок, 1 колонка) і може стояти там, де очікується значення: у SELECT чи в порівнянні total > (SELECT avg(total) FROM orders). Табличний повертає набір рядків і використовується з IN, EXISTS, ANY.

A subquery. It is a query inside another query, in parentheses. Depending on where it sits and what it returns, there are several kinds. A scalar one returns exactly one value (1 row, 1 column) and can stand wherever a value is expected: in SELECT or in a comparison like total > (SELECT avg(total) FROM orders). A table subquery returns a set of rows and is used with IN, EXISTS, ANY.

Derived table. Підзапит у FROM з обов'язковим аліасом (у PostgreSQL до версії 16 аліас був обов'язковим, у 16+ його можна пропустити, але з ним читабельніше). Він поводиться як тимчасова таблиця, з якою можна робити JOIN, фільтр і групування. Типовий приклад — спочатку порахувати суму на покупця, а потім відфільтрувати або з'єднати результат.

Derived table. A subquery in FROM with an alias (PostgreSQL required the alias before version 16; from 16 on it may be omitted, but it reads better with one). It behaves like a temporary table you can JOIN, filter and group. The typical example is computing the total per buyer first, then filtering or joining the result.

Пастки. Скалярний підзапит, який несподівано повернув два рядки, дає помилку more than one row returned by a subquery used as an expression — це реальний баг у тестових хелперах, коли дані «забруднилися» дублікатами. Якщо підзапит не повернув жодного рядка, результат — NULL, і порівняння з ним тихо відкине всі рядки.

Pitfalls. A scalar subquery that unexpectedly returns two rows raises more than one row returned by a subquery used as an expression — a real bug in test helpers when the data got «polluted» with duplicates. If the subquery returns no rows, the result is NULL, and a comparison with it silently drops every row.

Що це. Корельований підзапит посилається на колонки зовнішнього запиту, тому логічно виконується для кожного рядка зовнішнього запиту окремо. (SELECT sum(amount) FROM payments p WHERE p.order_id = o.id) у SELECT по orders o рахує оплату саме для поточного замовлення. Некорельований підзапит, навпаки, не залежить від зовнішнього і виконується один раз.

What it is. A correlated subquery references columns of the outer query, so logically it runs separately for every outer row. (SELECT sum(amount) FROM payments p WHERE p.order_id = o.id) in the SELECT over orders o computes the payment for exactly the current order. A non-correlated subquery, by contrast, does not depend on the outer query and runs once.

Навіщо. Він природно виражає «для кожного рядка знайди пов'язане значення»: останній платіж замовлення, кількість позицій, чи є хоч одна успішна оплата (EXISTS). І, на відміну від JOIN двох дочірніх таблиць, не розмножує рядки — кожен підзапит повертає одне значення на замовлення.

Why use it. It naturally expresses «for each row find the related value»: an order's latest payment, its item count, whether there is at least one successful payment (EXISTS). And unlike joining two child tables, it does not multiply rows — each subquery returns one value per order.

Швидкодія. «Для кожного рядка» на мільйоні рядків означає мільйон виконань, якщо планувальник не перепише запит. PostgreSQL вміє перетворювати EXISTS/IN на semi-join, але скалярний корельований підзапит у SELECT зазвичай виконується як SubPlan на кожен рядок. З індексом по payments.order_id це прийнятно, без нього — повний перегляд таблиці на кожен рядок. У EXPLAIN це видно як SubPlan з великим loops. Альтернатива для великих обсягів — агрегат у CTE і JOIN.

Performance. «For each row» over a million rows means a million executions unless the planner rewrites the query. PostgreSQL can turn EXISTS/IN into a semi-join, but a scalar correlated subquery in SELECT usually runs as a SubPlan per row. With an index on payments.order_id that is acceptable; without one it is a full table scan per row. In EXPLAIN it shows up as a SubPlan with a large loops. The alternative for big volumes is an aggregate in a CTE plus a JOIN.

Механіка. x NOT IN (1, 2, NULL) розгортається в x <> 1 AND x <> 2 AND x <> NULL. Останнє порівняння — UNKNOWN, а TRUE AND UNKNOWN дає UNKNOWN. Умова ніколи не стає TRUE, і запит повертає порожній результат без жодної помилки. Для IN проблеми немає: x IN (1, NULL) для x = 1 дає TRUE.

The mechanics. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last comparison is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN. The condition never becomes TRUE, and the query returns an empty result without any error. IN has no such problem: x IN (1, NULL) is TRUE for x = 1.

Чому це небезпечно. Запит «користувачі, які нікого не запросили» через id NOT IN (SELECT referred_by FROM users) повертає нуль рядків, бо в referred_by є NULL. Тест перевірки даних, який очікує «порожній результат = все добре», тут пройде, хоча логіка зламана. Сьогодні NULL у колонці немає — запит працює; завтра з'явиться один — і перевірка мовчки перестає щось знаходити.

Why it is dangerous. The query «users who have not referred anyone» written as id NOT IN (SELECT referred_by FROM users) returns zero rows because referred_by contains NULL. A data check that expects «empty result = all good» passes here even though the logic is broken. Today the column has no NULLs and the query works; tomorrow one appears and the check silently stops finding anything.

EXISTS. NOT EXISTS (SELECT 1 FROM … WHERE r.referred_by = u.id) перевіряє лише наявність рядка, і NULL у referred_by просто не дає збігу. До того ж EXISTS зупиняється на першому знайденому рядку. PostgreSQL виконує NOT EXISTS як ефективний anti-join, а NOT IN з підзапитом — ні, бо зобов'язаний врахувати NULL-семантику. Тому правило: для заперечення з підзапитом — NOT EXISTS. Якщо NOT IN таки потрібен, фільтруй WHERE col IS NOT NULL у підзапиті.

EXISTS. NOT EXISTS (SELECT 1 FROM … WHERE r.referred_by = u.id) only checks whether a row exists, and a NULL in referred_by simply does not match. EXISTS also stops at the first row it finds. PostgreSQL runs NOT EXISTS as an efficient anti-join, but not NOT IN with a subquery, because it has to honour NULL semantics. Hence the rule: for a negation with a subquery, use NOT EXISTS. If you really need NOT IN, filter WHERE col IS NOT NULL in the subquery.

Що це. CTE (Common Table Expression) — іменований підзапит, оголошений перед основним запитом: WITH paid AS (SELECT …) SELECT … FROM paid. Кілька CTE пишуться через кому, і кожен наступний може посилатися на попередні. Це той самий підзапит, але з ім'ям, тож запит читається зверху вниз як послідовність кроків, а не зсередини назовні.

What it is. A CTE (Common Table Expression) is a named subquery declared before the main query: WITH paid AS (SELECT …) SELECT … FROM paid. Several CTEs are separated by commas, and each can refer to the ones before it. It is the same subquery, but named, so the query reads top to bottom as a sequence of steps rather than inside out.

Навіщо QA. Складні перевірки даних зручно будувати кроками: expected — що має бути, actual — що є, фінальний SELECT — де вони розходяться. Кожен крок можна виконати окремо (замінивши фінальний SELECT на SELECT * FROM expected) і перевірити проміжний результат — так налагоджують запит, а не вгадують.

Why QA cares. Complex data checks are easy to build in steps: expected — what should be there, actual — what is there, and a final SELECT — where they differ. Each step can be run on its own (replace the final SELECT with SELECT * FROM expected) and its intermediate result checked — that is how you debug a query rather than guess.

Нюанси. У PostgreSQL до версії 12 кожен CTE завжди матеріалізувався і був «бар'єром оптимізації». З 12-ї версії CTE, на який посилаються один раз, вбудовується в запит, а поведінку можна задати явно: AS MATERIALIZED / AS NOT MATERIALIZED. CTE існує лише в межах одного запиту — це не тимчасова таблиця. У MySQL CTE з'явилися тільки у 8.0.

Nuances. Before PostgreSQL 12 every CTE was always materialized and acted as an «optimization fence». Since 12, a CTE referenced once is inlined into the query, and you can set the behaviour explicitly with AS MATERIALIZED / AS NOT MATERIALIZED. A CTE lives only within one query — it is not a temporary table. MySQL got CTEs only in 8.0.

Що це. WITH RECURSIVE складається з двох частин, об'єднаних UNION ALL: якірна частина дає стартові рядки, рекурсивна посилається на сам CTE і додає наступний рівень. База повторює рекурсивну частину, доки вона повертає нові рядки. Так обходять ієрархії довільної глибини: дерево категорій, ланцюжок менеджерів, граф запрошень, залежності задач.

What it is. WITH RECURSIVE has two parts joined by UNION ALL: the anchor part produces the starting rows, and the recursive part refers to the CTE itself and adds the next level. The database repeats the recursive part while it keeps returning new rows. That is how you traverse hierarchies of any depth: a category tree, a management chain, a referral graph, task dependencies.

Приклад. Від Anna (id 1) вниз: вона запросила Bohdan і Olena, Bohdan — Taras. Якір — Anna з рівнем 0, рекурсивний крок — «користувачі, чий referred_by є серед уже знайдених», з рівнем +1. Корисно накопичувати шлях (path || name), щоб бачити, як ми дійшли до рядка.

Example. Downward from Anna (id 1): she referred Bohdan and Olena, and Bohdan referred Taras. The anchor is Anna at level 0; the recursive step is «users whose referred_by is among those already found», at level +1. It helps to accumulate the path (path || name) to see how we reached each row.

Ризики і тестування. Якщо дані містять цикл (A запросив B, B — A), рекурсія стає нескінченною. Захист — лічильник глибини з WHERE depth < 20 або масив відвіданих id і умова NOT id = ANY(path); у PostgreSQL 14+ є ще конструкція CYCLE. Для QA рекурсивний CTE — і інструмент (побудувати дерево для перевірки), і об'єкт тестування: цикли, самопосилання, дуже глибокі гілки й «осиротілі» вузли, чий батько видалений.

Risks and testing. If the data contains a cycle (A referred B, B referred A), the recursion never ends. Protect against it with a depth counter and WHERE depth < 20, or an array of visited ids with NOT id = ANY(path); PostgreSQL 14+ also has the CYCLE clause. For QA a recursive CTE is both a tool (building a tree to verify) and a test target: cycles, self-references, very deep branches and «orphaned» nodes whose parent was deleted.

Віконні функції. На відміну від GROUP BY, вони не згортають рядки: кожен рядок лишається, а поруч з'являється значення, пораховане по «вікну» пов'язаних рядків. OVER (PARTITION BY user_id ORDER BY created_at DESC) означає «окремо для кожного покупця, упорядковано від нових до старих».

Window functions. Unlike GROUP BY, they do not fold rows: every row stays, and next to it appears a value computed over a «window» of related rows. OVER (PARTITION BY user_id ORDER BY created_at DESC) means «separately for each buyer, ordered newest to oldest».

Три нумерації. ROW_NUMBER() дає унікальні 1, 2, 3 навіть для рівних значень (між рівними порядок довільний, якщо не додати ключ). RANK() дає рівним однаковий ранг і пропускає наступні: 1, 1, 3. DENSE_RANK() — без пропусків: 1, 1, 2. Вираз ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) нумерує співробітників усередині відділу за зарплатою — це питання з квізу.

Three numberings. ROW_NUMBER() gives unique 1, 2, 3 even for equal values (the order among equals is arbitrary unless you add a key). RANK() gives equals the same rank and skips the next ones: 1, 1, 3. DENSE_RANK() has no gaps: 1, 1, 2. The expression ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) numbers employees inside a department by salary — that is the quiz question.

Top-N на групу. «Останнє замовлення кожного покупця» — це ROW_NUMBER() … = 1. Фільтрувати віконну функцію у WHERE того ж запиту не можна (вона рахується на етапі SELECT), тому її загортають у CTE або підзапит. Для QA це основа перевірок на кшталт «поточний статус — це останній запис історії» і пошуку дублікатів, де треба залишити один рядок і видалити решту.

Top-N per group. «Each buyer's latest order» is ROW_NUMBER() … = 1. You cannot filter a window function in the WHERE of the same query (it is computed at the SELECT step), so it gets wrapped in a CTE or subquery. For QA this underpins checks like «the current status equals the last history record» and duplicate hunting, where one row is kept and the rest are deleted.

LAG і LEAD. LAG(col) повертає значення з попереднього рядка вікна, LEAD(col) — з наступного. Так порівнюють сусідні записи без self join: інтервал між платежами, зміну статусу, стрибок ціни. Для першого рядка LAG дає NULL (або значення за замовчуванням третім аргументом).

LAG and LEAD. LAG(col) returns the value from the previous row of the window, LEAD(col) from the next one. That lets you compare neighbouring records without a self join: the gap between payments, a status change, a price jump. For the first row LAG yields NULL (or a default given as the third argument).

Наростаючий підсумок. sum(total) OVER (ORDER BY created_at) додає кожен рядок до суми попередніх. Важливий нюанс: за замовчуванням рамка вікна з ORDER BY — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, і рядки з однаковим ключем сортування потрапляють у підсумок разом. Якщо потрібен суворо «рядок за рядком», пишуть ROWS BETWEEN … і додають унікальний ключ.

Running total. sum(total) OVER (ORDER BY created_at) adds each row to the sum of the previous ones. An important nuance: with ORDER BY the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so rows with the same sort key enter the total together. If you need strictly «row by row», write ROWS BETWEEN … and add a unique key.

QA-перевірки. Подвійне списання: два успішні платежі одного замовлення з інтервалом у секунди — paid_at - LAG(paid_at) менше п'яти хвилин. Діри в нумерації документів: id - LAG(id) > 1. Баланс рахунку, який ніде не має стати від'ємним: наростаюча сума руху коштів і пошук першого рядка, де вона менша за нуль. Такі запити знаходять баги, яких не видно в UI.

QA checks. A double charge: two successful payments for one order seconds apart — paid_at - LAG(paid_at) under five minutes. Gaps in document numbering: id - LAG(id) > 1. An account balance that must never go negative: a running sum of movements and a search for the first row where it drops below zero. Such queries find bugs you cannot see in the UI.

VIEW. Збережений іменований запит — віртуальна таблиця без власних даних. Кожне звернення до view заново виконує його запит, тож дані завжди актуальні. View спрощує складні запити, приховує колонки (наприклад, без email для аналітиків) і дає стабільний інтерфейс, коли таблиці під ним змінюються.

VIEW. A stored named query — a virtual table with no data of its own. Each access re-runs the view's query, so the data is always current. A view simplifies complex queries, hides columns (for example, no email for analysts) and gives a stable interface when the tables underneath change.

Materialized view. Результат запиту фізично зберігається, тому читання швидке, але дані застигають на момент останнього REFRESH MATERIALIZED VIEW. Оновлення запускають за розкладом або після завантаження даних; варіант CONCURRENTLY не блокує читачів, але потребує унікального індексу. У MySQL materialized view немає — їх емулюють таблицею і подією.

Materialized view. The query result is physically stored, so reads are fast, but the data freezes at the last REFRESH MATERIALIZED VIEW. Refreshes run on a schedule or after a data load; the CONCURRENTLY variant does not block readers but needs a unique index. MySQL has no materialized views — they are emulated with a table and an event.

Як тестувати. Для звичайного view — порівняти його вивід з незалежно написаним запитом на базових таблицях. Для materialized view головний ризик — застарілість: тест створює замовлення, звіт його не бачить, і це не баг, якщо так задумано, але баг, якщо REFRESH мав спрацювати. Тому треба знати, хто і коли запускає оновлення, і перевіряти звіт після нього. Ще варто перевірити, що view не «ламається» після міграції схеми, яка перейменувала колонку.

How to test. For a plain view, compare its output with an independently written query on the base tables. For a materialized view the main risk is staleness: a test creates an order, the report does not show it, and that is not a bug if intended but a bug if the REFRESH should have fired. So you need to know who triggers the refresh and when, and check the report after it. It is also worth checking that the view does not «break» after a schema migration renames a column.

Правильна форма. INSERT INTO t (col1, col2) VALUES (v1, v2) — завжди зі списком колонок. Форма без нього (INSERT INTO t VALUES (…)) залежить від порядку колонок у таблиці і ламається, щойно хтось додасть колонку. В одному INSERT можна вставити кілька рядків через кому — це швидше, ніж окремі запити, і зручно для тестових даних.

The right form. INSERT INTO t (col1, col2) VALUES (v1, v2) — always with a column list. The form without one (INSERT INTO t VALUES (…)) depends on the table's column order and breaks as soon as someone adds a column. One INSERT can add several rows separated by commas — faster than separate statements and handy for test data.

INSERT … SELECT. Копіює дані одним запитом: INSERT INTO t2 (cols) SELECT cols FROM t1 WHERE …. Так роблять резервну копію перед ризикованою зміною, переносять дані між таблицями, розмножують тестові дані. Для «копії таблиці разом зі структурою» в PostgreSQL є CREATE TABLE t2 AS SELECT …, але вона не переносить обмеження та індекси.

INSERT … SELECT. Copies data in one query: INSERT INTO t2 (cols) SELECT cols FROM t1 WHERE …. It is how you back up rows before a risky change, move data between tables or multiply test data. For «a copy of a table with its structure» PostgreSQL has CREATE TABLE t2 AS SELECT …, but it does not carry over constraints and indexes.

RETURNING. PostgreSQL повертає значення вставлених, змінених чи видалених рядків прямо з INSERT/UPDATE/DELETE: RETURNING id, created_at. У тестах це прибирає зайвий SELECT і гонки на кшталт «взяти max(id) після вставки», які ламаються при паралельному запуску. У MySQL RETURNING немає (є лише в MariaDB), там використовують LAST_INSERT_ID().

RETURNING. PostgreSQL returns the values of inserted, updated or deleted rows straight from INSERT/UPDATE/DELETE: RETURNING id, created_at. In tests this removes an extra SELECT and races like «take max(id) after the insert», which break under parallel runs. MySQL has no RETURNING (only MariaDB does); there you use LAST_INSERT_ID().

Без WHERE. UPDATE users SET status = 0 змінить усі рядки таблиці, DELETE FROM t видалить усі рядки, залишивши порожню таблицю зі структурою. Жодного попередження база не дасть — у режимі autocommit зміна одразу зафіксована. У спільному тестовому середовищі це зламає тести й дані колег, а відновлення з бекапу забере години.

Without WHERE. UPDATE users SET status = 0 changes every row of the table, and DELETE FROM t deletes every row, leaving an empty table with its structure. The database gives no warning — in autocommit mode the change is committed immediately. In a shared test environment that breaks your colleagues' tests and data, and restoring from a backup takes hours.

Безпечна процедура. (1) Спочатку напиши SELECT з тією самою умовою і подивись, які й скільки рядків він повертає. (2) Відкрий транзакцію BEGIN. (3) Виконай UPDATE/DELETE і звір кількість змінених рядків (psql пише UPDATE 1) з очікуваною. (4) Перевір результат SELECT-ом. (5) Лише тоді COMMIT, інакше ROLLBACK. RETURNING * дає побачити змінені рядки прямо в тій же команді.

A safe procedure. (1) First write a SELECT with the same condition and look at which rows and how many it returns. (2) Open a transaction with BEGIN. (3) Run the UPDATE/DELETE and compare the changed-row count (psql prints UPDATE 1) with the expected one. (4) Check the result with a SELECT. (5) Only then COMMIT, otherwise ROLLBACK. RETURNING * shows the changed rows in the same command.

Додаткові запобіжники. У MySQL-клієнті є режим --safe-updates, який забороняє UPDATE/DELETE без ключа в WHERE. У DBeaver та інших IDE варто вимкнути autocommit для спільних баз. І найпростіше — обмеж умову ключем тестових даних (префікс email, id тест-рану), а не «статусом», який можуть мати й чужі рядки.

Extra safety nets. The MySQL client has a --safe-updates mode that forbids UPDATE/DELETE without a key in WHERE. In DBeaver and other IDEs, turn autocommit off for shared databases. And simplest of all, scope the condition by a test-data key (an email prefix, a test-run id) rather than by a «status» that other people's rows may share.

DELETE. Видаляє рядки за умовою (або всі без WHERE), по рядку: спрацьовують тригери, перевіряються зовнішні ключі, кожен рядок пишеться в журнал. Повільно на великих таблицях, але точково — можна видалити лише свої тестові дані. Лічильники (sequence/identity) не скидаються.

DELETE. Removes rows by condition (or all of them without WHERE), row by row: triggers fire, foreign keys are checked, each row is logged. Slow on big tables but precise — you can delete only your own test data. Counters (sequence/identity) are not reset.

TRUNCATE. Миттєво спорожнює всю таблицю, без WHERE і без рядкових тригерів. RESTART IDENTITY скидає лічильники, CASCADE очищує і таблиці, що посилаються на неї. Без CASCADE TRUNCATE таблиці, на яку є FK, дає помилку. У PostgreSQL TRUNCATE транзакційний і відкочується через ROLLBACK; у MySQL він неявно фіксує транзакцію і відкотити його не можна.

TRUNCATE. Empties the whole table instantly, with no WHERE and no row-level triggers. RESTART IDENTITY resets the counters, and CASCADE also clears the tables that reference it. Without CASCADE, truncating a table referenced by an FK raises an error. In PostgreSQL TRUNCATE is transactional and rolls back with ROLLBACK; in MySQL it implicitly commits and cannot be rolled back.

DROP. Видаляє саму таблицю — структуру, індекси, обмеження, права. Після DROP таблиці немає, а TRUNCATE залишає порожню таблицю. Для очищення тестових даних між тестами у виділеній базі зручний TRUNCATE усіх таблиць однією командою; у спільній базі — лише DELETE з умовою на свої дані; DROP — для тимчасових таблиць, які тест сам створив.

DROP. Removes the table itself — structure, indexes, constraints, grants. After DROP the table is gone, whereas TRUNCATE leaves an empty table. To clean test data between tests in a dedicated database, TRUNCATE of all tables in one command is handy; in a shared database only DELETE scoped to your own data; DROP is for temporary tables the test created itself.

Що це. UPSERT — «вставити, а якщо такий ключ уже є, оновити». Це атомарна операція, і вона розв'язує гонку «SELECT, чи є рядок → INSERT або UPDATE», коли два паралельні запити обидва бачать «немає» і другий падає на унікальному ключі.

What it is. An UPSERT means «insert, and if that key already exists, update». It is atomic and solves the «SELECT whether the row exists → INSERT or UPDATE» race, where two parallel requests both see «not there» and the second fails on the unique key.

PostgreSQL. INSERT … ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name. EXCLUDED — рядок, який намагалися вставити. Треба вказати унікальний індекс чи обмеження, за яким визначається конфлікт. DO NOTHING просто пропускає дублікат — зручно для ідемпотентних сид-скриптів тестових даних. З версії 15 є ще стандартний MERGE.

PostgreSQL. INSERT … ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name. EXCLUDED is the row you tried to insert. You must name the unique index or constraint that defines the conflict. DO NOTHING just skips the duplicate — handy for idempotent test-data seed scripts. Since version 15 there is also the standard MERGE.

MySQL. INSERT … ON DUPLICATE KEY UPDATE name = VALUES(name) (у 8.0.20+ замість VALUES() рекомендують аліас рядка). Спрацьовує на будь-якому унікальному ключі, не можна обрати, на якому саме, — якщо в таблиці їх кілька, результат може здивувати. REPLACE INTO — не UPSERT: він видаляє старий рядок і вставляє новий, тобто спрацюють DELETE-каскади й зміниться автоінкремент.

MySQL. INSERT … ON DUPLICATE KEY UPDATE name = VALUES(name) (from 8.0.20 a row alias is recommended instead of VALUES()). It fires on any unique key and you cannot pick which one — with several of them in a table the result can surprise you. REPLACE INTO is not an UPSERT: it deletes the old row and inserts a new one, so DELETE cascades fire and the auto-increment changes.

Що тестувати. Повторний виклик API з тими самими даними не створює дубль; оновлюються лише потрібні поля (created_at не перезаписується); регістр і пробіли в ключі (email) нормалізовані однаково, інакше UPSERT створить «майже дубль».

What to test. Calling the API again with the same data does not create a duplicate; only the intended fields are updated (created_at is not overwritten); case and spaces in the key (email) are normalized the same way, otherwise the UPSERT creates a «near duplicate».

PRIMARY KEY. Унікальний ідентифікатор рядка, який не може бути NULL. У таблиці він один (але може складатися з кількох колонок, як (order_id, product_id) в order_items). База автоматично створює для нього унікальний індекс.

PRIMARY KEY. A unique row identifier that cannot be NULL. A table has one (though it may span several columns, like (order_id, product_id) in order_items). The database creates a unique index for it automatically.

Сурогатний ключ. Штучне число без бізнес-сенсу, яке генерує база: GENERATED ALWAYS AS IDENTITY (стандарт, PostgreSQL 10+), старіший serial, AUTO_INCREMENT у MySQL, або UUID. Природний ключ (email, номер паспорта) змінюється в реальному житті, тому його роблять UNIQUE, а PK — сурогатним.

Surrogate key. An artificial number with no business meaning, generated by the database: GENERATED ALWAYS AS IDENTITY (standard, PostgreSQL 10+), the older serial, AUTO_INCREMENT in MySQL, or a UUID. A natural key (email, passport number) changes in real life, so it is made UNIQUE while the PK stays surrogate.

UNIQUE проти PK. UNIQUE-обмежень може бути кілька, і вони зазвичай допускають NULL — причому в PostgreSQL кілька NULL не вважаються дублікатами (з 15-ї версії це можна змінити через NULLS NOT DISTINCT). PK — один і завжди NOT NULL.

UNIQUE versus PK. There can be several UNIQUE constraints, and they usually allow NULL — and in PostgreSQL several NULLs are not treated as duplicates (since version 15 you can change that with NULLS NOT DISTINCT). The PK is single and always NOT NULL.

Для тестів. Не хардкодь id у перевірках — бери їх з RETURNING. Послідовності не відкочуються з транзакцією, тому після ROLLBACK у нумерації з'являються «дірки», і це не баг. Класична проблема сид-скриптів: вставили рядки з явними id, а sequence лишився на 1 — наступна вставка без id падає з duplicate key. Лікується setval.

For tests. Do not hard-code ids in checks — take them from RETURNING. Sequences do not roll back with a transaction, so after a ROLLBACK the numbering has «holes», and that is not a bug. A classic seed-script problem: rows inserted with explicit ids while the sequence stayed at 1 — the next insert without an id fails with a duplicate key. The cure is setval.

Що це. Зовнішній ключ — посилання на ключ іншої таблиці, яке база гарантує: не можна вставити orders.user_id, якого немає в users, і не можна видалити користувача, на якого посилаються замовлення. Це і є referential integrity — цілісність зв'язків.

What it is. A foreign key is a reference to another table's key that the database enforces: you cannot insert an orders.user_id that does not exist in users, and you cannot delete a user that orders point to. That is referential integrity.

Дії при видаленні. За замовчуванням (NO ACTION/RESTRICT) видалення батька, на якого є посилання, дає помилку. ON DELETE CASCADE автоматично видаляє дочірні рядки — у нашій схемі видалення замовлення прибирає його позиції. SET NULL обнуляє посилання, SET DEFAULT ставить значення за замовчуванням.

Actions on delete. By default (NO ACTION/RESTRICT), deleting a referenced parent raises an error. ON DELETE CASCADE deletes the child rows automatically — in our schema, deleting an order removes its items. SET NULL clears the reference, SET DEFAULT sets the default value.

Що тестувати. Каскад — потужна і небезпечна річ: видалення одного користувача може тихо прибрати тисячі рядків через ланцюжок каскадів. Тест-кейси: видалення батька з дітьми (помилка чи каскад — що передбачено вимогою?), вставка з неіснуючим посиланням, API-відповідь на порушення FK (має бути 409/422, а не 500 з текстом SQL-помилки). Якщо FK немає (як у нашій payments), цілісність доводиться перевіряти запитами на сироти — база сама нічого не гарантує. У MySQL FK працюють лише в InnoDB; MyISAM їх мовчки ігнорує.

What to test. A cascade is powerful and dangerous: deleting one user can silently remove thousands of rows through a chain of cascades. Test cases: deleting a parent that has children (error or cascade — which does the requirement specify?), inserting with a non-existent reference, the API response to an FK violation (should be 409/422, not a 500 carrying the SQL error text). Without an FK (as in our payments), integrity has to be verified with orphan queries — the database guarantees nothing. In MySQL, FKs work only in InnoDB; MyISAM silently ignores them.

CHECK і NOT NULL. CHECK (price > 0) перевіряє умову на кожному рядку при вставці та оновленні; NOT NULL забороняє порожнє значення. Важливий нюанс: CHECK пропускає рядок, якщо умова дає UNKNOWN, тому CHECK (price > 0) без NOT NULL дозволяє NULL. У MySQL CHECK реально перевіряється лише з версії 8.0.16 — раніше його синтаксис приймався і мовчки ігнорувався.

CHECK and NOT NULL. CHECK (price > 0) validates a condition on every row on insert and update; NOT NULL forbids an empty value. An important nuance: CHECK lets a row through when the condition is UNKNOWN, so CHECK (price > 0) without NOT NULL allows NULL. In MySQL, CHECK is actually enforced only since 8.0.16 — before that its syntax was accepted and silently ignored.

ALTER TABLE. ALTER TABLE t ADD COLUMN col type додає колонку; для наявних рядків вона буде NULL або значенням DEFAULT. Додати колонку NOT NULL без DEFAULT у заповнену таблицю не вийде. Обмеження додають так само: ALTER TABLE … ADD CONSTRAINT … CHECK (…), і база одразу перевірить наявні рядки.

ALTER TABLE. ALTER TABLE t ADD COLUMN col type adds a column; existing rows get NULL or the DEFAULT value. You cannot add a NOT NULL column without a DEFAULT to a populated table. Constraints are added the same way: ALTER TABLE … ADD CONSTRAINT … CHECK (…), and the database checks the existing rows right away.

Навіщо QA. Обмеження — остання лінія захисту, коли валідація в API пропустила погане значення. Тест-кейси на межі (0, −1, NULL, порожній рядок) варто проганяти і через API, і прямим INSERT: якщо API пропускає 0, а база відхиляє, користувач побачить 500 замість зрозумілої помилки. Текст помилки PostgreSQL містить ім'я обмеження — за ним легко знайти правило в схемі.

Why QA cares. Constraints are the last line of defence when API validation let a bad value through. Boundary cases (0, −1, NULL, empty string) are worth running both through the API and with a direct INSERT: if the API accepts 0 and the database rejects it, the user sees a 500 instead of a clear error. The PostgreSQL error text contains the constraint name, which makes the rule easy to find in the schema.

Мета нормалізації. Кожен факт зберігається в одному місці. Це прибирає надлишковість і аномалії: оновили адресу в одному рядку, а в іншому лишилась стара (аномалія оновлення); не можна додати товар, поки його ніхто не замовив (аномалія вставки); видалили останнє замовлення — загубили дані про товар (аномалія видалення).

The goal of normalization. Each fact is stored in one place. That removes redundancy and anomalies: you update an address in one row while another keeps the old one (update anomaly); you cannot add a product until someone orders it (insert anomaly); you delete the last order and lose the product data (delete anomaly).

Нормальні форми. 1NF — атомарні значення: жодних списків у клітинці ('SKU-001,SKU-002' у колонці замовлення порушує 1NF — для цього існує order_items). 2NF — неключові колонки залежать від усього складеного ключа, а не від його частини. 3NF — неключові колонки не залежать від інших неключових (немає транзитивних залежностей): місто покупця не повинно зберігатися в orders, бо воно залежить від user_id, а не від замовлення.

Normal forms. 1NF — atomic values: no lists in a cell ('SKU-001,SKU-002' in an order column breaks 1NF — that is what order_items is for). 2NF — non-key columns depend on the whole composite key, not on part of it. 3NF — non-key columns do not depend on other non-key columns (no transitive dependencies): the buyer's city should not live in orders, because it depends on user_id, not on the order.

Денормалізація. Навмисне дублювання заради швидкого читання: orders.total замість підрахунку з позицій щоразу, ціна в order_items на момент покупки (це навіть не денормалізація, а історичний факт). Ціна цього — ризик розсинхронізації, і саме тут QA пише перевірки узгодженості: orders.total проти суми позицій. У нашій схемі така перевірка знаходить замовлення 104.

Denormalization. Deliberate duplication for fast reads: orders.total instead of summing the items every time, the price in order_items at purchase time (strictly a historical fact rather than denormalization). The cost is the risk of drift, and that is exactly where QA writes consistency checks: orders.total against the sum of items. In our schema that check finds order 104.

Процедура і функція. Збережена процедура — програма в базі, яку викликають за іменем: CALL close_order(101). У PostgreSQL є функції (CREATE FUNCTION, викликаються в SELECT і повертають значення) і, з версії 11, процедури (CREATE PROCEDURE, викликаються через CALL і можуть керувати транзакціями). Логіка поруч з даними працює швидко, але її важче версіонувати, рев'юїти й покривати тестами.

Procedure and function. A stored procedure is a program in the database called by name: CALL close_order(101). PostgreSQL has functions (CREATE FUNCTION, called in SELECT and returning a value) and, since version 11, procedures (CREATE PROCEDURE, invoked with CALL and able to control transactions). Logic next to the data runs fast, but it is harder to version, review and cover with tests.

Тригер. Код, який база запускає автоматично на INSERT/UPDATE/DELETE: заповнити updated_at, записати історію змін в audit-таблицю, перерахувати агрегат. У PostgreSQL тригер — це окрема функція RETURNS trigger плюс CREATE TRIGGER, що прив'язує її до таблиці. Тригер BEFORE може змінити рядок, AFTER — побачити фінальний стан.

Trigger. Code the database runs automatically on INSERT/UPDATE/DELETE: filling updated_at, writing change history to an audit table, recomputing an aggregate. In PostgreSQL a trigger is a separate function RETURNS trigger plus a CREATE TRIGGER that attaches it to a table. A BEFORE trigger can change the row, an AFTER trigger sees the final state.

Як тестувати. Тригер — «невидима» логіка: розробник у коді застосунку не бачить, що UPDATE статусу ще й пише в audit. Тест-кейси: тригер спрацьовує на кожну операцію, на яку розрахований (і не спрацьовує на інші), коректно обробляє масові оновлення, не ламає TRUNCATE (рядкові тригери на нього не реагують) і не створює каскад тригерів. Перевірка — через SELECT з таблиці, куди тригер пише.

How to test. A trigger is «invisible» logic: a developer reading application code does not see that a status UPDATE also writes to an audit table. Test cases: the trigger fires on every operation it is meant for (and not on others), handles bulk updates correctly, is not bypassed by TRUNCATE (row triggers ignore it) and does not start a cascade of triggers. Verify through a SELECT from the table the trigger writes to.

Що це. Транзакція — група операцій, яка виконується атомарно: або всі зміни фіксуються, або жодна. Класичний приклад — переказ: списати з одного рахунку і зарахувати на інший. Якщо друга операція впала, перша не має залишитися. У нашій схемі це «створити замовлення + позиції + платіж»: замовлення без позицій (як 106) — саме те, що буває, коли кроки йдуть без транзакції.

What it is. A transaction is a group of operations executed atomically: either every change is committed or none is. The classic example is a transfer: debit one account and credit another. If the second operation fails, the first must not remain. In our schema it is «create an order + items + payment»: an order with no items (like 106) is exactly what happens when the steps run without a transaction.

ACID. Atomicity — все або нічого. Consistency — після транзакції дані задовольняють усі обмеження. Isolation — паралельні транзакції не бачать проміжних станів одна одної (наскільки суворо — визначає рівень ізоляції). Durability — зафіксоване переживе падіння сервера.

ACID. Atomicity — all or nothing. Consistency — after the transaction the data satisfies every constraint. Isolation — concurrent transactions do not see each other's intermediate states (how strictly is set by the isolation level). Durability — committed data survives a server crash.

Команди. BEGIN (або стандартне START TRANSACTION) відкриває транзакцію, COMMIT фіксує, ROLLBACK скасовує. SAVEPOINT name і ROLLBACK TO SAVEPOINT name дозволяють відкотити частину. Без BEGIN кожна команда — окрема транзакція (autocommit). У PostgreSQL помилка всередині транзакції «отруює» її: наступні команди падають з current transaction is aborted, доки не буде ROLLBACK. У MySQL DDL (CREATE, ALTER, TRUNCATE) неявно фіксує транзакцію, тому відкотити міграцію там не вийде.

Commands. BEGIN (or the standard START TRANSACTION) opens a transaction, COMMIT commits, ROLLBACK cancels. SAVEPOINT name and ROLLBACK TO SAVEPOINT name let you undo part of it. Without BEGIN every statement is its own transaction (autocommit). In PostgreSQL an error inside a transaction «poisons» it: later statements fail with current transaction is aborted until ROLLBACK. In MySQL, DDL (CREATE, ALTER, TRUNCATE) implicitly commits, so you cannot roll a migration back there.

Рівні за стандартом (від слабшого до суворішого): READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE. Кожен наступний забороняє більше аномалій: dirty read, non-repeatable read, phantom read, а SERIALIZABLE гарантує, що результат такий, ніби транзакції йшли по черзі.

The standard levels (weakest to strictest): READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE. Each level forbids more anomalies: dirty read, non-repeatable read, phantom read, and SERIALIZABLE guarantees the outcome is as if the transactions ran one after another.

Dirty read. Читання незафіксованих змін іншої транзакції. Сесія A змінила ціну, але ще не зробила COMMIT; сесія B бачить нову ціну; A робить ROLLBACK — і B прийняла рішення на даних, яких ніколи не існувало. Стандарт дозволяє це лише на READ UNCOMMITTED.

Dirty read. Reading another transaction's uncommitted changes. Session A changes a price but has not committed; session B sees the new price; A rolls back — and B made a decision on data that never existed. The standard allows this only at READ UNCOMMITTED.

Реальні бази. PostgreSQL взагалі не має dirty read: READ UNCOMMITTED поводиться як READ COMMITTED, і це рівень за замовчуванням. У MySQL InnoDB за замовчуванням REPEATABLE READ, а READ UNCOMMITTED справді дозволяє брудне читання. Тому відповідь «на якому рівні працює наша база» — перше, що варто з'ясувати, коли тест на паралельні операції поводиться по-різному на різних середовищах. Поточний рівень показує SHOW transaction_isolation, змінюють його для транзакції через BEGIN ISOLATION LEVEL ….

Real databases. PostgreSQL has no dirty reads at all: READ UNCOMMITTED behaves as READ COMMITTED, which is the default level. MySQL InnoDB defaults to REPEATABLE READ, and its READ UNCOMMITTED really does allow dirty reads. So «which level does our database run at» is the first thing to find out when a concurrency test behaves differently across environments. SHOW transaction_isolation shows the current level, and BEGIN ISOLATION LEVEL … changes it for one transaction.

Non-repeatable read. Транзакція двічі читає той самий рядок і отримує різні значення, бо між читаннями інша транзакція змінила його і зафіксувала. Приклад: звіт читає ціну товару на початку й наприкінці розрахунку — і суми не сходяться.

Non-repeatable read. A transaction reads the same row twice and gets different values, because between the reads another transaction changed it and committed. Example: a report reads a product price at the start and at the end of a calculation — and the totals do not match.

Phantom read. Транзакція двічі виконує той самий запит з умовою і бачить нові рядки, що з'явилися між читаннями: count(*) оплачених замовлень був 3, став 4. Різниця: non-repeatable — змінилися наявні рядки, phantom — змінився сам набір рядків.

Phantom read. A transaction runs the same filtered query twice and sees new rows that appeared in between: the count(*) of paid orders was 3 and becomes 4. The difference: non-repeatable means existing rows changed; phantom means the set of rows itself changed.

Хто що забороняє. За стандартом READ COMMITTED допускає обидві аномалії, REPEATABLE READ забороняє non-repeatable, але допускає фантоми, SERIALIZABLE — жодної. PostgreSQL суворіший: його REPEATABLE READ працює на знімку (snapshot isolation) і не показує фантомів. MySQL InnoDB на REPEATABLE READ теж читає зі знімка, але SELECT … FOR UPDATE там читає актуальні дані — ще одна причина, чому поведінка між базами відрізняється.

What forbids what. By the standard, READ COMMITTED allows both anomalies, REPEATABLE READ forbids non-repeatable reads but allows phantoms, and SERIALIZABLE allows neither. PostgreSQL is stricter: its REPEATABLE READ works on a snapshot (snapshot isolation) and shows no phantoms. MySQL InnoDB also reads from a snapshot at REPEATABLE READ, but SELECT … FOR UPDATE there reads current data — one more reason behaviour differs between databases.

Навіщо QA. Звіти й фонові задачі, що читають дані кілька разів, мають працювати на REPEATABLE READ, інакше дають неузгоджений результат під навантаженням. Такий баг не відтворюється в одиночному тесті — його ловлять навмисно паралельними сесіями, як у таблиці нижче.

Why QA cares. Reports and background jobs that read data several times should run at REPEATABLE READ, otherwise they produce inconsistent results under load. Such a bug does not reproduce in a single-user test — you catch it deliberately with concurrent sessions, as in the table below.

Що це. Дві транзакції читають те саме значення, кожна рахує нове у коді застосунку і записує його. Друга запис перетирає першу, і одна зміна зникає без жодної помилки. Приклад: склад 10 шт., два замовлення одночасно читають 10, кожне пише 10 − 1 = 9, хоча має бути 8. READ COMMITTED цього не запобігає — кожен UPDATE сам по собі коректний.

What it is. Two transactions read the same value, each computes a new one in application code and writes it back. The second write overwrites the first, and one change vanishes without any error. Example: stock is 10, two orders read 10 at the same time, each writes 10 − 1 = 9, though it should be 8. READ COMMITTED does not prevent this — each UPDATE is correct on its own.

Способи захисту. (1) Атомарний UPDATE: SET stock = stock - 1 — база рахує від актуального значення під блокуванням рядка. Найпростіше і найкраще, коли логіку можна виразити в SQL. (2) Песимістичне блокування: SELECT … FOR UPDATE блокує рядок до кінця транзакції, друга сесія чекає. (3) Оптимістичне блокування: колонка version, і UPDATE … WHERE id = ? AND version = ?; якщо змінено 0 рядків — хтось встиг раніше, треба перечитати або повернути 409. (4) REPEATABLE READ/SERIALIZABLE у PostgreSQL: друга транзакція отримує could not serialize access due to concurrent update, і застосунок має повторити спробу.

Ways to protect. (1) An atomic UPDATE: SET stock = stock - 1 — the database computes from the current value under a row lock. The simplest and best option when the logic can be expressed in SQL. (2) Pessimistic locking: SELECT … FOR UPDATE locks the row until the end of the transaction, and the second session waits. (3) Optimistic locking: a version column and UPDATE … WHERE id = ? AND version = ?; if 0 rows changed, someone got there first — reread or return 409. (4) REPEATABLE READ/SERIALIZABLE in PostgreSQL: the second transaction gets could not serialize access due to concurrent update, and the application must retry.

Як тестувати. Паралельні запити на одну сутність (два PATCH, дві покупки останнього товару) і перевірка фінального стану в базі: сума змін має збігатися з кількістю успішних відповідей API. Такий тест ловить баг, якого не видно в послідовному прогоні.

How to test. Concurrent requests against one entity (two PATCHes, two purchases of the last item) and a check of the final state in the database: the sum of changes must match the number of successful API responses. Such a test catches a bug that never shows in a sequential run.

Що це. Дві транзакції тримають блокування і чекають одна на одну: A заблокувала рядок 101 і хоче 102, B заблокувала 102 і хоче 101. Жодна не може продовжити. База виявляє цикл очікування (у PostgreSQL перевірка запускається через deadlock_timeout, за замовчуванням 1 секунда) і перериває одну з транзакцій з помилкою deadlock detected; друга продовжує роботу.

What it is. Two transactions hold locks and wait for each other: A locked row 101 and wants 102, B locked 102 and wants 101. Neither can proceed. The database detects the wait cycle (in PostgreSQL the check runs after deadlock_timeout, 1 second by default) and aborts one transaction with deadlock detected; the other carries on.

Звідки береться. Найчастіше — різний порядок оновлення тих самих рядків у різних сценаріях: один ендпоінт оновлює спочатку замовлення, потім платіж, інший — навпаки. Також — масові UPDATE без ORDER BY, які блокують рядки в непередбачуваному порядку, і зовнішні ключі, що беруть блокування на батьківський рядок.

Where it comes from. Most often from updating the same rows in different orders in different flows: one endpoint updates the order first and then the payment, another does the reverse. Also from bulk UPDATEs without ORDER BY, which lock rows in an unpredictable order, and from foreign keys that take a lock on the parent row.

Як уникати і тестувати. Блокувати рядки в однаковому порядку (наприклад, за зростанням id), робити транзакції короткими, не тримати транзакцію відкритою під час HTTP-виклику. Застосунок має бути готовий повторити транзакцію, яку база обрала «жертвою». Для QA ознаки дедлоку — рідкісні 500 під навантаженням і deadlock detected у логах PostgreSQL; відтворюють двома сесіями або паралельним навантажувальним тестом на ті самі сутності.

Avoiding and testing it. Lock rows in a consistent order (say, ascending id), keep transactions short, and never hold a transaction open across an HTTP call. The application must be ready to retry a transaction the database picked as the «victim». For QA the signs of a deadlock are rare 500s under load and deadlock detected in the PostgreSQL log; you reproduce it with two sessions or a parallel load test on the same entities.

Компроміс. Індекс (у PostgreSQL за замовчуванням B-tree) пришвидшує пошук, сортування і JOIN, але займає місце і сповільнює кожен INSERT, UPDATE індексованих колонок і DELETE — усі індекси таблиці треба оновити. Таблиця з десятком індексів швидко читається і повільно пишеться. Тому індекс створюють під конкретні запити, а невикористані знаходять через pg_stat_user_indexes (idx_scan = 0) і видаляють.

The trade-off. An index (B-tree by default in PostgreSQL) speeds up lookups, sorting and JOINs, but it takes space and slows down every INSERT, every UPDATE of indexed columns and every DELETE — all the table's indexes must be maintained. A table with a dozen indexes reads fast and writes slowly. So indexes are built for specific queries, and unused ones are found through pg_stat_user_indexes (idx_scan = 0) and dropped.

Складений індекс. Він упорядкований спочатку за col1, а всередині — за col2, як телефонний довідник за прізвищем, потім ім'ям. Пошук за прізвищем чи за прізвищем та ім'ям — швидкий; лише за ім'ям — ні, бо однакові імена розкидані по всьому довіднику. Це правило лівого префікса: індекс (user_id, total) допомагає для WHERE user_id = 42 і WHERE user_id = 42 AND total = 42, але зазвичай не для WHERE total = 5.

A composite index. It is ordered by col1 first and by col2 within it, like a phone book sorted by surname, then first name. Looking up a surname, or surname plus first name, is fast; only a first name is not, because equal first names are scattered across the whole book. That is the leftmost-prefix rule: an index on (user_id, total) helps WHERE user_id = 42 and WHERE user_id = 42 AND total = 42, but usually not WHERE total = 5.

Нюанс версій. PostgreSQL 18 додав skip scan для B-tree: якщо в першій колонці мало різних значень, планувальник може «перестрибувати» по них і все ж використати індекс. Тому точна відповідь — «зазвичай ні, перевір в EXPLAIN». Порядок колонок обирають так: спершу ті, що стоять у рівностях найчастіших запитів, потім діапазони. І не забувай: індекс не допоможе, якщо колонку загорнуто у функцію (lower(email)) — для цього є індекс за виразом.

A version nuance. PostgreSQL 18 added B-tree skip scan: if the first column has few distinct values, the planner may «hop» over them and still use the index. So the precise answer is «usually not — check EXPLAIN». Choose the column order like this: equality columns of the most frequent queries first, then ranges. And remember an index will not help if the column is wrapped in a function (lower(email)) — that needs an expression index.

EXPLAIN. Показує план виконання: як база читатиме таблиці (Seq Scan, Index Scan, Bitmap Heap Scan, Index Only Scan), у якому порядку й яким алгоритмом з'єднуватиме (Nested Loop, Hash Join, Merge Join), і оцінку кількості рядків. EXPLAIN ANALYZE ще й реально виконує запит і показує фактичні рядки та час — тому на UPDATE/DELETE його запускають у транзакції з ROLLBACK. Головне, на що дивитися: де розходяться rows оцінені й фактичні і який вузол забирає найбільше часу.

EXPLAIN. Shows the execution plan: how the database will read tables (Seq Scan, Index Scan, Bitmap Heap Scan, Index Only Scan), in what order and with which algorithm it joins them (Nested Loop, Hash Join, Merge Join), and the estimated row counts. EXPLAIN ANALYZE actually runs the query and shows real rows and timings — so for UPDATE/DELETE it is run inside a transaction with ROLLBACK. What to look at first: where estimated and actual rows diverge, and which node takes the most time.

Full table scan. У PostgreSQL це Seq Scan — читання всієї таблиці рядок за рядком. Це не завжди погано: для маленької таблиці чи запиту, що повертає більшу частину рядків, послідовне читання швидше за індекс. Погано, коли Seq Scan по мільйонній таблиці заради кількох рядків.

Full table scan. In PostgreSQL that is a Seq Scan — reading the whole table row by row. It is not always bad: for a small table or a query returning most rows, sequential reading beats an index. It is bad when a Seq Scan crawls a million-row table for a handful of rows.

Covering index. Індекс, що містить усі колонки, потрібні запиту. Тоді база відповідає лише з індексу, не звертаючись до таблиці, — у плані це Index Only Scan. У PostgreSQL 11+ додаткові колонки додають через INCLUDE. Нюанс: Index Only Scan ефективний лише коли visibility map свіжа (після VACUUM), інакше база однаково перевіряє рядки в таблиці. І саме тому SELECT * руйнує covering-індекси: зайві колонки змушують читати таблицю.

Covering index. An index that holds every column the query needs. The database then answers from the index alone, without touching the table — Index Only Scan in the plan. In PostgreSQL 11+ extra columns are added with INCLUDE. The nuance: an Index Only Scan is efficient only when the visibility map is fresh (after VACUUM), otherwise the database still checks rows in the table. And that is exactly why SELECT * defeats covering indexes: the extra columns force a table read.

Підхід. Після дії (оформили замовлення в UI) знайти запис за унікальними критеріями, які тест сам задав або отримав: id з відповіді API чи URL, унікальний email тестового користувача, номер замовлення. Шукати «останній запис у таблиці» не можна — паралельні тести й інші люди теж створюють рядки. Далі порівняти поле за полем з очікуванням: статус, суми, зв'язки, службові поля.

The approach. After the action (an order placed in the UI), find the record by unique criteria the test itself set or received: an id from the API response or URL, the test user's unique email, the order number. Never search for «the latest row in the table» — parallel tests and other people create rows too. Then compare field by field with the expectation: status, amounts, links, technical fields.

Що перевіряти, крім очевидного. (1) Кількість: створено рівно одне замовлення, а не два через подвійний клік. (2) Зв'язки: позиції належать цьому замовленню, user_id — тому користувачу, що увійшов. (3) Узгодженість: total дорівнює сумі позицій. (4) Службові поля: created_at у UTC і в межах часу тесту, updated_by, статус за замовчуванням. (5) Що не змінилося: сусідні записи, залишки інших товарів.

What to check beyond the obvious. (1) Count: exactly one order was created, not two because of a double click. (2) Links: the items belong to this order and user_id is the logged-in user. (3) Consistency: total equals the sum of the items. (4) Technical fields: created_at in UTC and within the test's time window, updated_by, the default status. (5) What did not change: neighbouring records, stock of other products.

Нюанси. Якщо запис створюється асинхронно (черга, фоновий обробник), потрібне очікування з тайм-аутом — повторювати SELECT, доки рядок не з'явиться, а не sleep(5). Тестова база має бути доступна тесту з обліковим записом лише на читання для перевірок — так перевірка не зіпсує дані випадково.

Nuances. If the record is created asynchronously (a queue, a background worker), you need a wait with a timeout — repeat the SELECT until the row appears, not sleep(5). The test database should be reachable from the test with a read-only account for checks, so a check cannot damage data by accident.

Пошук. Класика — SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1. Але в реальних даних дублікати рідко ідентичні: Anna@Mail.com і anna@mail.com — для людини одна адреса, а для UNIQUE-обмеження — дві різні. Тому групують за нормалізованим значенням: lower(trim(email)). Так само з телефонами (прибрати пробіли і дужки) та іменами.

Finding them. The classic is SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1. But duplicates in real data are rarely identical: Anna@Mail.com and anna@mail.com are one address to a human and two different values to a UNIQUE constraint. So group by the normalized value: lower(trim(email)). The same goes for phone numbers (strip spaces and brackets) and names.

Подробиці. Щоб побачити, які саме рядки дублюються, додай string_agg(id::text, ',') або використай віконну функцію count(*) OVER (PARTITION BY lower(email)) — вона залишає всі рядки і позначає кожен дубль. Для складених дублікатів групують за кількома колонками: два успішні платежі з тим самим order_id — теж дублікат.

Details. To see exactly which rows are duplicated, add string_agg(id::text, ',') or use the window function count(*) OVER (PARTITION BY lower(email)), which keeps every row and marks each duplicate. For composite duplicates, group by several columns: two successful payments with the same order_id are a duplicate too.

Очищення. Залишити один рядок з групи (найстаріший, за ROW_NUMBER) і видалити решту — тільки в транзакції і після SELECT тих самих рядків. Але спершу з'ясуй, чи на дублікати не посилаються інші таблиці: видалення користувача з замовленнями або впаде на FK, або каскадом знищить історію. Найкраще виправлення — не видалення, а обмеження, яке не дасть дублікатам з'явитися знову: CREATE UNIQUE INDEX … ON users (lower(email)). Баг-репорт має містити і запит, і кількість знайдених дублікатів.

Cleaning up. Keep one row per group (the oldest, via ROW_NUMBER) and delete the rest — only inside a transaction and after a SELECT of the same rows. But first check whether other tables reference the duplicates: deleting a user who has orders either fails on the FK or wipes history through a cascade. The best fix is not deletion but a constraint that keeps duplicates from coming back: CREATE UNIQUE INDEX … ON users (lower(email)). The bug report should include both the query and the number of duplicates found.

Набір перевірок. Кожна перевірка — запит, що повертає порожній результат, коли все гаразд, і конкретні проблемні рядки, коли ні. Типовий набір: сироти (платежі без замовлень, позиції без товарів), обов'язкові зв'язки (оплачене замовлення має успішний платіж, замовлення має хоч одну позицію), узгодженість сум (orders.total = сума позицій = сума успішних платежів), допустимі значення статусів і відсутність дублікатів.

A set of checks. Each check is a query that returns nothing when all is well and the specific bad rows when it is not. The typical set: orphans (payments without orders, items without products), mandatory links (a paid order has a successful payment, an order has at least one item), total consistency (orders.total = sum of items = sum of successful payments), allowed status values and no duplicates.

Звірка сум. Порахуй кожну величину окремо на рівні ключа — позиції в одному CTE, платежі в іншому — і лише потім з'єднай, інакше JOIN розмножить рядки й суми. Порівнюй через IS DISTINCT FROM, щоб NULL (немає позицій чи платежів) теж потрапив у звіт. Для грошей — точний тип numeric, без допуску; для float — з допуском.

Reconciling totals. Compute each quantity separately at the key level — items in one CTE, payments in another — and only then join, otherwise the JOIN multiplies rows and totals. Compare with IS DISTINCT FROM so that NULL (no items or no payments) makes it into the report too. For money use the exact numeric type with no tolerance; for floats, use a tolerance.

Як використовувати. Такі запити запускають після регресу, після міграції, у нічній задачі на staging — і будь-який непорожній результат стає баг-репортом з готовим запитом для відтворення. Корисно об'єднати перевірки через UNION ALL в одну «панель» з колонками check_name і entity_id, щоб тест падав з переліком усіх порушень одразу.

How to use them. Run such queries after regression, after a migration, in a nightly job on staging — and any non-empty result becomes a bug report with a ready query to reproduce it. It helps to merge the checks with UNION ALL into one «panel» with check_name and entity_id columns, so the test fails listing every violation at once.

Seed і фікстури. Seed-скрипт приводить базу до відомого стану перед тестами: довідники, базові користувачі, товари. Фікстура — набір даних під конкретний тест. Гарний seed ідемпотентний (ON CONFLICT DO NOTHING або TRUNCATE перед вставкою), версіонується разом з кодом і не залежить від порядку запуску тестів. Кожен тест створює свої дані з унікальним маркером (префікс email qa+run42_…, окремий tenant), щоб не конфліктувати з паралельними тестами.

Seeds and fixtures. A seed script brings the database to a known state before tests: lookups, base users, products. A fixture is a data set for a specific test. A good seed is idempotent (ON CONFLICT DO NOTHING or a TRUNCATE before inserting), versioned with the code and independent of test order. Each test creates its own data with a unique marker (an email prefix qa+run42_…, a separate tenant), so it does not clash with parallel tests.

Прибирання. Варіанти від найшвидшого: (1) тест усередині транзакції з ROLLBACK наприкінці — нічого не лишається, але не працює, якщо застосунок ходить у базу окремим з'єднанням; (2) DELETE за маркером тест-рану у зворотному порядку залежностей (платежі → позиції → замовлення → користувачі); (3) TRUNCATE усіх таблиць з RESTART IDENTITY CASCADE у виділеній базі; (4) свіжа база з контейнера (Testcontainers) на кожен набір тестів.

Cleaning up. Options from fastest: (1) the test runs inside a transaction with a ROLLBACK at the end — nothing is left behind, but it does not work when the application uses its own connection; (2) DELETE by the test-run marker in reverse dependency order (payments → items → orders → users); (3) TRUNCATE of all tables with RESTART IDENTITY CASCADE in a dedicated database; (4) a fresh database from a container (Testcontainers) per test suite.

Чому не продакшн. Ризик змінити чи видалити дані реальних користувачів, списати реальні гроші чи відправити листи, створити навантаження, що вповільнить сервіс, і порушити вимоги до персональних даних. Тести працюють на окремій базі; якщо потрібні «реалістичні» дані — беруть знеособлену копію, а не прод.

Why not production. The risk of changing or deleting real users' data, charging real money or sending emails, creating load that slows the service, and breaching personal-data rules. Tests run on a separate database; when «realistic» data is needed, take an anonymized copy, not production itself.

SQL-ін'єкція. Якщо запит склеюють з рядком від користувача, ввід стає частиною SQL. Email ' OR '1'='1, підставлений у "… WHERE email = '" + email + "'", перетворює умову на завжди істинну — і запит повертає всіх користувачів. Так крадуть дані, обходять логін і видаляють таблиці. Це OWASP Top 10 (категорія Injection).

SQL injection. When a query is glued together with user input, the input becomes part of the SQL. The email ' OR '1'='1, inserted into "… WHERE email = '" + email + "'", turns the condition into an always-true one — and the query returns every user. That is how data is stolen, logins are bypassed and tables dropped. It is in the OWASP Top 10 (the Injection category).

Захист. Параметризовані запити (prepared statements): текст запиту і значення передаються в базу окремо, і значення ніколи не розбирається як SQL. У JDBC це PreparedStatement з ? і setString, у Python psycopg — %s плюс кортеж значень окремим аргументом (не f-рядок!). Параметром можна передати лише значення, не ім'я таблиці чи колонки — їх беруть з білого списку.

Protection. Parameterized queries (prepared statements): the query text and the values travel to the database separately, and a value is never parsed as SQL. In JDBC that is a PreparedStatement with ? and setString; in Python psycopg it is %s plus a tuple of values as a separate argument (not an f-string!). Only values can be parameters, not table or column names — those come from an allow-list.

Що це означає для тестів. Власні тестові хелпери теж пиши з параметрами — інакше тест з email o'brien@mail.com впаде на синтаксичній помилці. А для перевірки застосунку на ін'єкції подавай у поля типові рядки (', ' OR '1'='1, '; DROP TABLE users; --) і дивись: помилка SQL у відповіді чи 500 — вже знахідка, навіть якщо дані не витекли.

What it means for tests. Write your own test helpers with parameters too — otherwise a test with the email o'brien@mail.com fails with a syntax error. And to probe the application for injection, feed typical strings into fields (', ' OR '1'='1, '; DROP TABLE users; --) and watch: an SQL error in the response or a 500 is already a finding, even if no data leaked.

Два типи. У PostgreSQL timestamptz зберігає момент часу (усередині — UTC) і показує його в часовому поясі сесії; timestamp без зони — просто «дата і час на годиннику», без прив'язки до поясу. Той самий рядок timestamptz у сесії з TimeZone = UTC виглядає як 23:30 31 березня, а в Europe/Kyiv — як 02:30 1 квітня. Для подій (створення, оплата) правильний тип — timestamptz.

Two types. In PostgreSQL timestamptz stores a moment in time (UTC inside) and displays it in the session time zone; timestamp without a zone is just «the date and time on a clock», tied to no zone. The same timestamptz value looks like 23:30 on March 31 in a session with TimeZone = UTC and like 02:30 on April 1 in Europe/Kyiv. For events (creation, payment) the right type is timestamptz.

Типові баги. (1) Місячний звіт групує за date_trunc('month', created_at) у UTC, а бізнес рахує в Києві — замовлення з ночі 31-го «переїжджає» в інший місяць, і звіт не сходиться з бухгалтерією. (2) Перехід на літній час: доба має 23 або 25 годин, а «+ 24 години» — не те саме, що «+ 1 день». (3) BETWEEN з датою обрізає останній день. (4) now() повертає час початку транзакції, тож два виклики в одній транзакції дають однакове значення.

Typical bugs. (1) A monthly report groups by date_trunc('month', created_at) in UTC while the business counts in Kyiv time — an order from the night of the 31st «moves» to another month, and the report disagrees with accounting. (2) Daylight saving: a day has 23 or 25 hours, and «+ 24 hours» is not the same as «+ 1 day». (3) BETWEEN with a date cuts off the last day. (4) now() returns the transaction start time, so two calls in one transaction give the same value.

Як тестувати. Явно задавати пояс у перевірці (AT TIME ZONE 'Europe/Kyiv'), а не покладатися на налаштування сервера CI. Мати тестові дані на межах: 23:30 UTC останнього дня місяця, ніч переходу на літній час, 29 лютого. У MySQL TIMESTAMP конвертується за поясом сесії, а DATETIME — ні, і обмежений 2038 роком для TIMESTAMP.

How to test. Set the zone explicitly in the check (AT TIME ZONE 'Europe/Kyiv') instead of relying on the CI server's settings. Keep boundary test data: 23:30 UTC on the last day of a month, the night of the DST switch, February 29. In MySQL TIMESTAMP is converted using the session zone while DATETIME is not, and TIMESTAMP is limited to the year 2038.

Порівняння рядків. У PostgreSQL 'Anna' = 'anna' — false, у MySQL зі стандартною колацією utf8mb4_0900_ai_ci — true (і ще й без урахування наголосів). Тест, що шукає користувача за email, може знайти один рядок у MySQL і нуль у PostgreSQL. Унікальність email теж працює по-різному.

String comparison. In PostgreSQL 'Anna' = 'anna' is false; in MySQL with the default utf8mb4_0900_ai_ci collation it is true (and accent-insensitive as well). A test looking up a user by email may find one row in MySQL and zero in PostgreSQL. Email uniqueness behaves differently too.

Синтаксис і семантика. Ідентифікатори: у PostgreSQL подвійні лапки, у MySQL — зворотні лапки. Склеювання: || проти CONCAT. Ділення цілих: 7 / 2 у PostgreSQL дає 3, у MySQL — 3.5000. UPSERT: ON CONFLICT проти ON DUPLICATE KEY UPDATE; RETURNING є лише в PostgreSQL. FULL OUTER JOIN немає в MySQL. GROUP BY без ONLY_FULL_GROUP_BY у MySQL мовчки повертає довільне значення.

Syntax and semantics. Identifiers: double quotes in PostgreSQL, backquotes in MySQL. Concatenation: || versus CONCAT. Integer division: 7 / 2 gives 3 in PostgreSQL and 3.5000 in MySQL. UPSERT: ON CONFLICT versus ON DUPLICATE KEY UPDATE; RETURNING exists only in PostgreSQL. MySQL has no FULL OUTER JOIN. GROUP BY without ONLY_FULL_GROUP_BY in MySQL silently returns an arbitrary value.

Транзакції і типи. Рівень ізоляції за замовчуванням: READ COMMITTED проти REPEATABLE READ. DDL у PostgreSQL транзакційний, у MySQL — неявний COMMIT. Тип boolean у MySQL — це tinyint(1), тож тест отримає 1 замість true. Лічильники: identity/sequence проти AUTO_INCREMENT. Тому інтеграційні тести мають бігти на тій самій СУБД і версії, що й продакшн; H2 чи SQLite «замість» справжньої бази ховають саме ці розбіжності.

Transactions and types. The default isolation level: READ COMMITTED versus REPEATABLE READ. DDL is transactional in PostgreSQL and an implicit COMMIT in MySQL. MySQL's boolean is really tinyint(1), so a test gets 1 instead of true. Counters: identity/sequence versus AUTO_INCREMENT. That is why integration tests should run on the same DBMS and version as production; H2 or SQLite «standing in» for the real database hide exactly these differences.

Види. Документні (MongoDB) зберігають JSON-подібні документи з вкладеними об'єктами; key-value (Redis) — значення за ключем, часто з TTL; wide-column (Cassandra) — рядки з гнучким набором колонок, розраховані на величезні обсяги запису; графові (Neo4j) — вузли і зв'язки. Загальне: немає жорсткої схеми, JOIN або немає, або вони обмежені, дані часто денормалізовані «під запит».

Kinds. Document stores (MongoDB) keep JSON-like documents with nested objects; key-value stores (Redis) keep a value per key, often with a TTL; wide-column stores (Cassandra) keep rows with a flexible set of columns, built for huge write volumes; graph databases (Neo4j) keep nodes and relationships. What they share: no rigid schema, JOINs either missing or limited, and data often denormalized «for the query».

Що змінюється для QA. (1) Немає FK і схеми — база не захистить від сиріт і від документа без обов'язкового поля; перевірки форми даних лягають на тести (JSON Schema, валідатори). (2) «Поле відсутнє» і «поле null» — різні стани, і фільтр {field: null} у MongoDB знаходить обидва. (3) Багато NoSQL-систем за замовчуванням eventually consistent: запис з'явиться на репліці не одразу, тож перевірка після дії має вміти чекати. (4) Дубльовані дані (ім'я покупця всередині замовлення) розходяться після оновлення джерела.

What changes for QA. (1) No FKs and no schema — the database will not protect you from orphans or from a document missing a required field; data-shape checks move into tests (JSON Schema, validators). (2) «Field missing» and «field is null» are different states, and the filter {field: null} in MongoDB matches both. (3) Many NoSQL systems are eventually consistent by default: a write shows up on a replica with a delay, so a check after an action must be able to wait. (4) Duplicated data (the buyer's name inside an order) drifts after the source is updated.

Спільне. Питання ті самі: чи є дублікати, чи узгоджені суми, чи не лишилося сиріт. PostgreSQL сам уміє зберігати документи в jsonb з індексами, тож перевірку «документного» підходу можна потренувати й у SQL.

What stays the same. The questions are the same: are there duplicates, do totals agree, are there orphans. PostgreSQL itself can store documents in jsonb with indexes, so you can practise checking a «document» approach in SQL as well.

Рівні перевірки. (1) Кількість: count(*) по кожній таблиці в джерелі й цілі, плюс кількість у розрізах (за статусом, за місяцем) — загальний лічильник може збігтися випадково. (2) Набори ключів: EXCEPT в обидва боки показує, які id загубилися і які з'явилися зайві. (3) Вміст: контрольні суми по колонках — sum для грошей, md5(string_agg(… ORDER BY id)) для рядка чи всієї таблиці; якщо хеші різні, звужуєш пошук до діапазону id. (4) Вибіркова звірка кількох записів поле за полем, включно з крайніми випадками.

Levels of checking. (1) Counts: count(*) per table in the source and the target, plus counts by slice (by status, by month) — an overall counter can match by coincidence. (2) Key sets: EXCEPT in both directions shows which ids were lost and which extra ones appeared. (3) Content: checksums per column — sum for money, md5(string_agg(… ORDER BY id)) for a row or a whole table; if the hashes differ, narrow down by id range. (4) A sample of records compared field by field, edge cases included.

Що ламається найчастіше. NULL перетворився на порожній рядок чи 0; обрізалися довгі рядки (varchar меншої довжини); загубилась точність numeric чи мілісекунди; часовий пояс зсунув timestamp; кодування зіпсувало кирилицю; дублікати в джерелі впали на новому UNIQUE і «тихо» відфільтрувались; послідовності не перевстановлено, і перша ж вставка після міграції падає на duplicate key.

What breaks most often. NULL turned into an empty string or 0; long strings got truncated (a shorter varchar); numeric precision or milliseconds were lost; a time zone shifted a timestamp; an encoding mangled Cyrillic text; duplicates in the source failed a new UNIQUE and were «quietly» filtered out; sequences were not reset, so the first insert after the migration fails on a duplicate key.

Процес. Запиши очікування до міграції (знімок лічильників і хешів), автоматизуй звірку скриптом, який повертає порожній результат при успіху, і прогони її на копії продакшн-даних, а не лише на тестовому seed. Після міграції перевір і застосунок: основні сценарії читання й запису на нових даних. Ще варто перевірити час міграції на реальному обсязі і що вона ідемпотентна — повторний запуск не дублює дані.

The process. Record expectations before the migration (a snapshot of counters and hashes), automate the reconciliation with a script that returns nothing on success, and run it on a copy of production data, not just the test seed. After the migration check the application too: the main read and write flows on the new data. It is also worth checking the migration time on a real volume, and that it is idempotent — a second run does not duplicate data.

Спершу визначення. Розбіжність звітів — найчастіше не баг обчислень, а різні визначення. Треба з'ясувати для кожного звіту: з якої таблиці береться «виручка» (сума замовлень чи сума платежів), які статуси враховано (paid, shipped, чи й new), за якою датою (створення замовлення чи оплати), у якому часовому поясі і чи відраховано повернення.

Definitions first. A report mismatch is most often not a calculation bug but different definitions. For each report, find out: which table «revenue» comes from (order totals or payment amounts), which statuses count (paid, shipped, or new too), which date is used (order creation or payment), in which time zone, and whether refunds are deducted.

Потім — міст між числами. Не порівнюй дві суми «в лоб». Порахуй обидва варіанти на рівні окремих записів і з'єднай їх FULL JOIN за id, щоб кожна розбіжність мала причину: «замовлення 104 у березні за UTC, але у квітні за Києвом», «платіж не збігається з сумою замовлення», «замовлення оплачене двічі». Сума стовпця «різниця» має точно дорівнювати розриву між звітами — тоді розслідування закінчене.

Then a bridge between the numbers. Do not compare two totals «head on». Compute both versions at the level of individual records and FULL JOIN them by id, so every difference has a reason: «order 104 is in March by UTC but in April by Kyiv time», «the payment does not match the order total», «the order was paid twice». The sum of the «difference» column must exactly equal the gap between the reports — then the investigation is complete.

Типові винуватці. Часовий пояс на межі місяця; fan-out від JOIN з позиціями чи платежами; дублікати платежів; платежі-сироти, які враховує одна сторона; різні правила статусів; кешовані чи materialized дані, оновлені в різний час. Результат розслідування — не лише баг-репорт, а й узгоджене визначення метрики, записане як запит, який стає автотестом для обох звітів.

The usual suspects. The time zone at a month boundary; fan-out from a JOIN with items or payments; duplicate payments; orphan payments that one side counts; different status rules; cached or materialized data refreshed at different times. The outcome is not just a bug report but an agreed metric definition written as a query, which becomes an automated test for both reports.

Відділи запит від середовища. Спочатку перевір, чи повільний саме запит, чи він чекає: pg_stat_activity покаже wait_event і блокування (pg_blocking_pids) — довга транзакція чи міграція, що тримає lock, робить повільним будь-який запит. Якщо очікувань немає, дивись план: EXPLAIN (ANALYZE, BUFFERS) на тих самих параметрах, що й у продакшн-виклику.

Separate the query from its environment. First check whether the query itself is slow or whether it is waiting: pg_stat_activity shows the wait_event and locks (pg_blocking_pids) — a long transaction or a migration holding a lock makes any query slow. If there is no waiting, look at the plan: EXPLAIN (ANALYZE, BUFFERS) with the same parameters as the production call.

Що шукати в плані. (1) Seq Scan по великій таблиці там, де очікувався індекс: індекс видалили, або колонку загорнули у функцію (lower(email), created_at::date), або типи не збігаються і відбувається неявне приведення, або шаблон LIKE '%…' з початковим відсотком. (2) Велика розбіжність між оціненими і фактичними rows — застаріла статистика після масового завантаження, лікується ANALYZE. (3) Nested Loop з мільйонами loops, сортування чи хеш, що вилились на диск. (4) SELECT *, що тягне широкі колонки і вбиває Index Only Scan.

What to look for in the plan. (1) A Seq Scan on a big table where an index was expected: the index was dropped, or the column got wrapped in a function (lower(email), created_at::date), or the types differ and an implicit cast happens, or a LIKE '%…' pattern starts with a percent sign. (2) A big gap between estimated and actual rows — stale statistics after a bulk load, cured by ANALYZE. (3) A Nested Loop with millions of loops, a sort or hash spilling to disk. (4) A SELECT * that drags in wide columns and kills an Index Only Scan.

Поза планом. «Сьогодні» часто означає: виросли дані, з'явився новий фільтр в ORM, змінився параметр, для якого план гірший, або код почав викликати запит у циклі (N+1). pg_stat_statements покаже, який запит і скільки разів з'їв час. Після виправлення — повтори EXPLAIN ANALYZE і зафіксуй бюджет часу в навантажувальному тесті, щоб регресія не повернулась непоміченою.

Beyond the plan. «Today» often means the data grew, the ORM added a new filter, a parameter changed to one with a worse plan, or code started calling the query in a loop (N+1). pg_stat_statements shows which query ate the time and how many times it ran. After the fix, rerun EXPLAIN ANALYZE and pin a time budget in a load test so the regression cannot come back unnoticed.