Базы данных и SQL: вопросы для собеседования QA

Основные SQL-проверки: SELECT для сверки данных из API/UI с тем, что реально сохранилось в БД; COUNT(*) для проверки количества записей; сравнение NULL-полей и граничных значений.

Дополнительно использую JOIN для проверки связей между таблицами, GROUP BY для агрегатов, и прямые UPDATE/DELETE для подготовки тестовых данных.

Из практики: первым делом иду в базу после любого POST/PUT-запроса — это быстрее всего показывает, что реально сохранилось и нет ли расхождения с ответом API.

СУБД — программное обеспечение, которое управляет данными: создание структур, хранение, выборка, обновление, удаление, контроль доступа и обеспечение целостности.

Основные функции: выполнение SQL-запросов, транзакционность (ACID), контроль целостности через ограничения, управление параллельным доступом, резервное копирование.

Из практики: для QA важно понимать СУБД не только как хранилище, но и как слой валидации — ограничения на уровне схемы ловят дефекты, которые прошли через приложение.

SQL-БД — реляционные, хранят данные в таблицах с фиксированной схемой, поддерживают ACID-транзакции, используют SQL. Примеры: PostgreSQL, MySQL, Oracle.

NoSQL — нереляционные хранилища без жёсткой схемы, масштабируются горизонтально, но часто жертвуют частью ACID. Типы: документные (MongoDB), ключ-значение (Redis), колоночные (Cassandra), графовые.

Из практики: выбор между SQL и NoSQL зависит от структуры данных и требований к консистентности. В тестировании важно знать, какой тип БД используется, — это определяет, как и где проверять данные.

CRUD — четыре базовые операции с данными: Create, Read, Update, Delete.

В SQL им соответствуют операторы INSERT (создать), SELECT (прочитать), UPDATE (обновить) и DELETE (удалить).

Для тестирования важно понимать, какая операция выполняется на каждом шаге сценария и как проверить результат запросом к базе.

Из практики: такие проверки быстро показывают, где данные теряются или записываются не туда.

DDL — это команды для структуры базы: создать таблицу, изменить схему, удалить объект.

Типичные команды: CREATE, ALTER, DROP, TRUNCATE.

Из практики: перед релизом всегда проверяю DDL-изменения на копии боевых данных, чтобы не словить неожиданные блокировки.

DML — это команды для работы с данными внутри таблиц: добавить, обновить, удалить и выбрать записи.

Обычно сюда относят INSERT, UPDATE, DELETE и SELECT.

Из практики: при проверке DML-сценариев я всегда сверяю итоговое состояние данных в базе, а не только ответ интерфейса.

Агрегирующие функции вычисляют сводное значение по набору строк. Часто применяются с группировкой (GROUP BY) для получения агрегатов по категориям.

Сортировка осуществляется через ORDER BY. Джоины (JOIN) позволяют объединять строки из нескольких таблиц по связанным полям.

Из практики: ошибки чаще всего возникают в неверной группировке и условиях JOIN, поэтому их проверяю в первую очередь.

UNION объединяет результаты двух запросов вертикально — складывает строки из двух выборок друг за другом. Столбцы должны совпадать по количеству и типам. UNION ALL сохраняет дубликаты, UNION — удаляет.

JOIN соединяет таблицы горизонтально, объединяя столбцы на основе связанных ключей: результат — одна широкая таблица с полями из обеих.

Из практики: UNION использую чтобы объединить продажи двух регионов в один отчёт (одинаковая структура данных); JOIN — чтобы добавить к заказу имя клиента из другой таблицы (связь через ключ).

Для стабильной работы с тестовыми данными в БД применяю несколько подходов: subsetting (выгружаю репрезентативную часть продовых данных), синтетическую генерацию через faker, и управление состоянием через транзакции с откатом.

Перед тестом создаю нужное состояние через SQL-скрипты или API, после теста откатываю изменения — это сохраняет изоляцию между тестами.

Из практики: самая частая проблема — тесты делят общие данные и мешают друг другу. Решается уникальными ключами и teardown после каждого теста.

Проверяю, что миграция применяется на чистой базе и на базе с реальными данными без потери информации. Важно проверить оба сценария — они дают разные ошибки.

Смотрю время выполнения, блокировки таблиц и обратимость изменений — есть ли возможность rollback если что-то пойдёт не так.

Из практики: проверяю миграцию на копии продовых данных — миграция, которая работает на пустой базе, может висеть часами на миллионах строк из-за блокировок. Это критично для zero-downtime деплоя.

Проверяю, что при ошибке внутри транзакции все изменения откатываются полностью — нет частично записанных данных.

Отдельно тестирую конкурентные операции с одними данными: две параллельные транзакции должны работать согласно ожидаемому уровню изоляции без неожиданных блокировок или phantom read.

Из практики: имитирую сбой в середине операции — отключаю БД или прерываю запрос. Частичные записи после сбоя — типичный дефект, который сложно поймать иначе. Проверяю через прямые SQL-запросы к таблицам.

Разделяю фиксированные наборы данных (справочники, роли) и данные, создаваемые в ходе теста. Динамические данные делаю воспроизводимыми: понятные фабрики, очистка после теста, уникальные ключи (UUID).

Статические тестовые данные версионирую в git рядом с тестами — изменение ожидаемого ответа сразу видно в diff.

Из практики: общие тестовые данные без изоляции — главная причина флаки при параллельном запуске. Каждый тест создаёт свои данные через API с уникальным UUID в ключевых полях.

Проверяю первичные и внешние ключи, уникальные индексы, NOT NULL ограничения, CHECK-ограничения на диапазоны и enum-значения.

Смотрю, что некорректные записи не попадают в таблицы даже при прямой записи в БД в обход приложения — ограничения должны работать на уровне БД, а не только в коде.

Из практики: ограничения на уровне БД — последний рубеж обороны против грязных данных. Проверяю их через прямые INSERT/UPDATE с нарушением ограничений — каждое нарушение должно давать ошибку БД.

Сверяю расчёты отчёта с первичными данными напрямую через SQL-запросы к источнику — не доверяю только визуальному сравнению.

Проверяю граничные значения в формулах, фильтры по периоду, часовой пояс, округление чисел и поведение при отсутствующих данных (NULL vs 0).

Из практики: расхождения в отчётах чаще всего связаны с неверным часовым поясом или разной трактовкой NULL в агрегирующих функциях — эти случаи проверяю первыми.

Смотрю план выполнения запроса через EXPLAIN ANALYZE — проверяю, что используется Index Scan там, где ожидается, нет Seq Scan по большим таблицам и нет избыточных Sort-операций.

Проверяю время ответа на типичных и больших объёмах данных и слежу, что изменения в запросе не ухудшают его на реалистичных данных.

Из практики: EXPLAIN ANALYZE показывает реальный план исполнения. Seq Scan по таблице с миллионами строк, где должен быть Index Scan — первый сигнал пропущенного индекса.

Проверяю сам процесс восстановления: восстановление проходит без ошибок, занимает приемлемое время согласно RTO, данные целостны после восстановления.

После восстановления сверяю количество записей, ключевые связи, последние транзакции — система должна быть работоспособна, а не просто запускаться.

Из практики: резервное копирование без проверки восстановления — это иллюзия безопасности. Регулярно тестирую полный цикл: backup → restore → проверка работоспособности.

INNER JOIN возвращает только строки, у которых есть совпадение в обеих таблицах. LEFT JOIN — все строки левой таблицы плюс совпадения справа (или NULL, если справа нет). RIGHT JOIN — симметрично, все справа. FULL JOIN — объединение LEFT и RIGHT: все строки с обеих сторон, NULL там, где нет пары.

CROSS JOIN — декартово произведение, каждая строка слева с каждой справа. Часто нужен для генерации комбинаций.

Из практики: 80% задач решается INNER и LEFT. RIGHT почти не встречается (тот же LEFT с переставленными таблицами). FULL — для сверок и поиска расхождений между двумя списками.

WHERE фильтрует строки до группировки — работает с отдельными строками. HAVING фильтрует результаты после GROUP BY — работает с агрегатами.

Например, WHERE status = 'paid' уберёт неоплаченные заказы. HAVING SUM(amount) > 1000 оставит только тех клиентов, у кого сумма заказов больше 1000.

Из практики: если условию не нужен агрегат — пишите его в WHERE, оптимизатор быстрее обработает. HAVING — только для условий по агрегатам.

GROUP BY объединяет строки с одинаковыми значениями в указанных столбцах в одну группу. К каждой группе можно применить агрегатные функции: COUNT, SUM, AVG, MIN, MAX.

В SELECT можно класть только либо столбцы из GROUP BY, либо агрегаты. Иначе СУБД не знает, какое из значений показать.

Из практики: применяют для отчётов «сколько заказов на пользователя», «сумма по дням», «среднее по категории». GROUP BY несколькими столбцами создаёт группы по их комбинации.

Индекс — это отдельная структура данных (чаще всего B-tree), которая хранит значения столбца и ссылки на строки таблицы. По нему СУБД находит нужные строки за логарифмическое время вместо полного сканирования.

Самые частые сценарии, где помогает: WHERE col = X, JOIN по столбцу, ORDER BY, поиск диапазона, проверка UNIQUE.

Из практики: индекс ускоряет чтение, но замедляет вставку и обновление, потому что его нужно поддерживать. На большие таблицы индексы строят осторожно и только под конкретные запросы.

EXPLAIN показывает план запроса: какие операции и в каком порядке СУБД собирается выполнить, какие индексы использовать, как соединять таблицы.

EXPLAIN ANALYZE реально выполняет запрос и показывает фактическое время каждой операции — это основной инструмент диагностики медленных запросов. В Postgres и в MySQL (с 8.0.18) это EXPLAIN ANALYZE; при этом MySQL всегда выводит древовидный TREE-формат, а FORMAT=JSON доступен только для обычного (оценочного) EXPLAIN, но не для EXPLAIN ANALYZE.

Из практики: ищут Seq Scan (полное сканирование) на больших таблицах, неиспользованные индексы, неожиданные Nested Loop вместо Hash Join, а также сильное расхождение estimated и actual rows — это указывает на устаревшую статистику.

Транзакция — это группа операций, которые выполняются как единое целое: либо все, либо ни одной. В коде окружают командами BEGIN и COMMIT (или ROLLBACK для отмены).

ACID: Atomicity (атомарность — всё или ничего), Consistency (согласованность — переход из одного валидного состояния в другое), Isolation (изоляция — параллельные транзакции не мешают друг другу), Durability (долговечность — после COMMIT данные не теряются даже при сбое).

Из практики: ACID важен для финансовых операций и любых ситуаций, где нельзя оставить данные в полусохранённом состоянии (списали со счёта, не зачислили на другой).

По стандарту четыре: READ UNCOMMITTED (можно читать незакоммиченные данные — dirty read), READ COMMITTED (только подтверждённые), REPEATABLE READ (повторное чтение даст те же строки), SERIALIZABLE (как если бы транзакции выполнялись по очереди).

Феномены: dirty read, non-repeatable read (повторное чтение даст другое значение), phantom read (повторный запрос вернёт новые строки). С повышением уровня феноменов меньше, но и параллелизм ниже.

Из практики: в Postgres по умолчанию READ COMMITTED, в MySQL InnoDB — REPEATABLE READ. SERIALIZABLE используют только когда без него нельзя обеспечить корректность.

JOIN соединяет таблицы на одном уровне, возвращая комбинированные строки. Подзапрос — это вложенный SELECT, который возвращает значение, набор значений или таблицу, используемые в основном запросе.

В большинстве случаев подзапрос можно переписать через JOIN. Оптимизатор современных СУБД часто сам переписывает один в другой. Читаемость зависит от ситуации: для «отфильтровать пользователей, у которых есть оплаченный заказ» — EXISTS читается лучше, чем JOIN + DISTINCT.

Из практики: коррелированные подзапросы (зависят от внешней строки) на больших данных могут быть медленными — JOIN надёжнее. Скалярный подзапрос в SELECT — удобно, но осторожно с производительностью.

Оконная функция вычисляет значение по «окну» строк, связанных с текущей, не сворачивая их в одну. Например, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) даст номер записи в группе.

Самые частые: ROW_NUMBER (последовательный номер), RANK и DENSE_RANK (ранги с одинаковыми/без пропусков), LAG/LEAD (значение из предыдущей/следующей строки), SUM/AVG OVER (накопительные итоги).

Из практики: оконные функции заменяют сложные джойны и self-join. Типовая задача — взять последнюю запись по группе, найти разницу с предыдущим значением, посчитать накопительную сумму.

CTE (Common Table Expression) — это именованный подзапрос, который объявляется в начале запроса через WITH name AS (SELECT ...) и используется дальше как таблица.

Помогает разбить сложный запрос на читаемые шаги. Бывают рекурсивные CTE — они позволяют обходить иерархии (дерево категорий, граф) и генерировать последовательности.

Из практики: для отчётов с 4-5 уровнями вложенности CTE радикально улучшает читаемость. В Postgres до 12 версии CTE были оптимизационным барьером (материализовались), сейчас инлайнятся, если без RECURSIVE.

UNION объединяет результаты двух запросов и убирает дубликаты строк (вызывает сортировку или хеширование для дедупликации). UNION ALL объединяет без дедупликации.

UNION ALL быстрее, потому что не делает лишнюю работу. UNION используют, только когда дубликаты действительно нужно убрать.

Из практики: если вы точно знаете, что результаты не пересекаются — всегда UNION ALL. Это типичный анти-паттерн начинающих — везде ставить UNION «на всякий случай».

EXISTS проверяет, существует ли хотя бы одна строка во вложенном подзапросе для текущей строки внешней таблицы. Возвращает true/false и прекращает поиск на первой найденной.

IN сравнивает значение со списком из подзапроса. Если список большой — может быть медленнее. На NULL значения IN ведёт себя неожиданно: NOT IN с NULL в списке вернёт пусто.

Из практики: для проверки «есть ли связанная запись» — EXISTS предпочтительнее. Для NOT IN с подзапросом — почти всегда NOT EXISTS безопаснее из-за NULL-проблем.

Нормализация — это процесс разбиения данных на таблицы так, чтобы избежать дублирования и аномалий обновления.

1НФ: значения в ячейках атомарны (никаких списков в одном поле). 2НФ: 1НФ плюс все неключевые столбцы зависят от всего первичного ключа, а не от его части (актуально для составного ключа). 3НФ: 2НФ плюс нет транзитивных зависимостей (неключевые столбцы зависят только от ключа, а не друг от друга).

Из практики: 3НФ — обычный уровень для нормальных бизнес-данных. Иногда сознательно денормализуют для скорости чтения (отчёты, аналитика).

Внешний ключ (FOREIGN KEY) — это ссылка одного столбца (или нескольких) на первичный ключ другой таблицы. Он гарантирует, что в зависимой таблице не появится значение, которого нет в родительской.

Действия при удалении/обновлении родителя: CASCADE (удалить/обновить и потомков), SET NULL (выставить NULL у потомков), SET DEFAULT (значение по умолчанию), RESTRICT/NO ACTION (запретить операцию, если есть потомки).

Из практики: CASCADE удобен для тесно связанных данных (комментарии при удалении поста). Для важных бизнес-сущностей чаще выбирают RESTRICT — заставить разработчика подумать.

VIEW — это сохранённый запрос. При обращении к view СУБД каждый раз выполняет внутренний SELECT. Данные всегда свежие, но работа повторяется.

MATERIALIZED VIEW — это сохранённый результат запроса. Данные физически лежат на диске и обновляются по команде REFRESH (или по расписанию). Чтение быстрое, но данные не свежие.

Из практики: VIEW — для удобной абстракции и инкапсуляции логики. MATERIALIZED VIEW — для тяжёлых аналитических запросов, где допустима задержка обновления.

UPSERT — операция «вставить или обновить»: если строки нет — INSERT, если есть (по ключу) — UPDATE.

В PostgreSQL это INSERT ... ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col. Псевдотаблица EXCLUDED содержит значения, которые пытались вставить.

Из практики: незаменим для синхронизации с внешними источниками и для idempotent-операций, чтобы при повторе скрипта не было дубликатов и не нужно было заранее проверять существование.

SELECT * возвращает все столбцы, что увеличивает сетевой трафик и память, особенно если в таблице есть тяжёлые поля (TEXT, BLOB).

Зависимость от *: при добавлении столбца в таблицу клиент начинает получать его без изменения кода — это может сломать сериализацию или утечь чувствительные поля.

Из практики: всегда явно перечисляйте нужные столбцы. Это документирует контракт и защищает от случайных регрессий, когда модель меняется.

COALESCE возвращает первый не-NULL аргумент из списка. COALESCE(value, 'default') — частая замена NULL на значение по умолчанию.

NULLIF принимает два аргумента и возвращает NULL, если они равны, иначе первый. Часто используют, чтобы превратить пустую строку или нулевое значение в NULL: NULLIF(value, '').

Из практики: COALESCE и NULLIF — пара инструментов для аккуратной работы с NULL. Особенно полезны в импорте данных, где «пусто» и «отсутствует» нужно различать.

Для простой дедупликации по списку столбцов SELECT DISTINCT и SELECT ... GROUP BY дают одинаковый результат и часто одинаковый план запроса.

Разница появляется, если нужно агрегатить — DISTINCT не позволяет считать агрегаты в том же запросе, GROUP BY позволяет. Также DISTINCT ON (Postgres) — отдельный синтаксис для «возьми по одной строке на каждое значение».

Из практики: для просто «уникальные значения столбца» — DISTINCT читается явно. Для «уникальные пары + что-то ещё» — GROUP BY. Для «возьми лучшую строку на ключ» — DISTINCT ON или window function.

Составной индекс — это индекс по двум и более столбцам. Хранит ключи в порядке: сначала по первому столбцу, потом по второму внутри одинаковых значений первого и т.д.

Правило выбора порядка: первым ставят столбец, по которому чаще фильтруют через равенство, потом — по которому диапазон или сортировка. Иначе индекс не сможет работать в полную силу.

Из практики: индекс по (user_id, created_at) ускоряет запрос «последние заказы пользователя». Тот же индекс не поможет, если в запросе только created_at без user_id.

Covering index — это индекс, который содержит все столбцы, нужные запросу, и СУБД может ответить, не обращаясь к таблице. Это даёт максимальное ускорение чтения.

В Postgres делают через INCLUDE: CREATE INDEX ix ON orders(user_id) INCLUDE (amount, status). В MySQL — добавляя столбцы в сам индекс.

Из практики: applies для частых запросов «выбери эти поля по этому фильтру». Особенно ценно, когда таблица большая, а нужны только 2-3 поля.

B-tree — сбалансированное дерево, поддерживает поиск по равенству, диапазон, сортировку, LIKE 'foo%'. Это индекс по умолчанию в большинстве СУБД.

Hash индекс хранит хеши ключей в bucket-ах. Очень быстр для поиска по равенству, но не поддерживает диапазон и сортировку. В Postgres есть отдельный тип, но используется редко.

Из практики: 95% задач решает B-tree. Hash индекс выбирают для частых поисков по точному ключу в очень больших таблицах, где B-tree излишен.

EXPLAIN ANALYZE реально выполняет запрос и возвращает план с фактическими временами и количеством строк. План — это дерево узлов: Scan, Join, Aggregate, Sort.

Ключевые поля: cost (оценка), actual time, rows (estimated/actual), buffers (если EXPLAIN (ANALYZE, BUFFERS)). Сильное расхождение estimated/actual rows — повод обновить статистику ANALYZE table.

Из практики: читать снизу вверх. Самый «толстый» узел — главный кандидат на оптимизацию. Видишь Seq Scan по большой таблице с фильтром — нужен индекс.

N+1 — это анти-паттерн: один запрос вытащил список (1 запрос), а дальше для каждого элемента отдельно запрашиваются связанные данные (N запросов). Итого N+1 поход в БД вместо одного-двух.

Признак: на отрисовку страницы из 50 элементов выполняется 51 запрос. Видно в логах БД, в трассах APM или в инструментах ORM.

Из практики: решают eager loading в ORM (preload/include/with), JOIN-ом или подзапросом, либо отдельным batch-запросом с IN. Это типовой источник медленных страниц.

Row-level locks накладываются UPDATE/DELETE/SELECT FOR UPDATE на конкретные строки. Не блокируют чтение, но блокируют другие модификации тех же строк.

Table-level locks — на всю таблицу. Большинство ставится автоматически (Access Share при чтении, Row Exclusive при UPDATE). Иногда явно: LOCK TABLE для миграций.

Advisory locks — пользовательские мутексы на уровне БД, не привязанные к строкам. pg_advisory_lock(key). Удобно для координации задач между процессами. Из практики: понимание блокировок критично, чтобы избежать тупиков (deadlocks) — обычно встречается, когда две транзакции захватывают ресурсы в разном порядке.

MVCC (Multi-Version Concurrency Control) — это подход, при котором каждое изменение создаёт новую версию строки, а старые остаются видимыми для уже идущих транзакций. Читателям не нужны блокировки от писателей.

Каждая строка имеет xmin (транзакция, создавшая её) и xmax (транзакция, удалившая/обновившая). Транзакция видит только те версии, которые валидны на её snapshot.

Из практики: побочный эффект — нужна периодическая чистка устаревших версий (VACUUM, autovacuum). Без неё таблица «раздувается».

Партиционирование — это разделение большой таблицы на несколько физических подтаблиц по ключу (диапазон дат, список, хеш). Снаружи это всё ещё одна логическая таблица.

Преимущества: запросы по части ключа сканируют только нужные партиции, обслуживание (DROP, REINDEX) выполняется по частям, индексы внутри партиции меньше и быстрее.

Из практики: нужно для таблиц от десятков миллионов строк, особенно с time-series данными. Самая частая стратегия — по диапазону created_at (месяц/неделя).

Партиционирование — это разделение в рамках одной БД. Sharding — это распределение данных по разным БД/серверам. Каждый shard — независимая инстанция.

Sharding решает проблему, когда одна машина не справляется с объёмом или нагрузкой. Минусы: сложно делать кросс-shard JOIN, транзакции между шардами требуют отдельных протоколов (двухфазный коммит или согласованность eventual).

Из практики: до многих десятков ТБ данных и сотен тысяч TPS обычно хватает оптимизации и партиционирования. Sharding выбирают, когда другие способы не работают.

Репликация — это копирование изменений с одной БД (primary) на другие (replicas). Используется для отказоустойчивости, разгрузки чтения, географической близости.

Виды: synchronous (primary ждёт подтверждения от реплики — гарантия консистентности, но медленнее) vs asynchronous (не ждёт — быстро, но при сбое часть данных может потеряться). Также: logical vs physical (на уровне записи WAL или на уровне SQL/команд).

Из практики: в Postgres есть streaming replication (physical async по умолчанию). Чтения на реплику разгружают primary, но имейте в виду replication lag — реплика может отставать.

Триггер — это код (функция), который автоматически выполняется при определённом событии в таблице: BEFORE/AFTER INSERT/UPDATE/DELETE.

Применения: автоматическое обновление updated_at, аудит изменений (запись в audit-таблицу), валидация бизнес-правил, поддержание денормализованных счётчиков.

Из практики: триггеры удобны там, где правило должно выполняться независимо от приложения (множество клиентов, легаси-доступ). Но они «магические» — поведение БД меняется неочевидно для разработчика.

json хранит JSON в исходном тексте — быстро записывает, медленно ищет внутри. jsonb хранит в бинарном виде после парсинга — занимает место плотнее и поддерживает индексы (GIN).

Для запросов внутри JSON-структуры используют операторы ->, ->>, @>, ? и функции jsonb_path_query.

Из практики: для рабочих данных всегда jsonb. json оставляйте для редких случаев, когда важно сохранить точную форму (порядок ключей, пробелы).

Функция возвращает значение (или набор) и обычно используется внутри SELECT. У неё ограниченный набор побочных эффектов (в Postgres до 11 — без транзакционного управления).

Процедура (CALL) может управлять транзакциями (COMMIT, ROLLBACK внутри), не возвращает напрямую значение в SELECT, скорее предназначена для action-сценариев — миграции, batch-обновления.

Из практики: процедуры удобны для регламентных задач, выполняемых cron-ом или вручную. Бизнес-логику лучше держать в приложении, а не в процедурах — иначе версионирование и тестирование становится больно.

SELECT ... FOR UPDATE SKIP LOCKED — это вариант блокировки, при котором строки, уже захваченные другой транзакцией, не ждут, а пропускаются.

Главное применение — реализация очередей задач в БД. Несколько воркеров одновременно берут «следующий пакет», не блокируя друг друга и не получая одну и ту же задачу.

Из практики: для лёгких очередей до сотен задач в секунду PostgreSQL с SKIP LOCKED спокойно справляется, и нет нужды поднимать Redis/RabbitMQ.

CHECK — это ограничение на значения столбца или строки: CHECK (age >= 0), CHECK (price > 0 AND discount BETWEEN 0 AND 100). Если условие false при вставке/обновлении — операция отвергается.

Применяется для бизнес-правил, которые точно должны выполняться (возраст не отрицательный, статус из списка значений и т.п.).

Из практики: лучше иметь правило на уровне БД, чем полагаться на приложение — изменения BD могут идти из разных источников.

SAVEPOINT — это «точка сохранения» внутри транзакции. После SAVEPOINT можно сделать ROLLBACK TO SAVEPOINT и откатить часть операций, не откатывая всю транзакцию.

Удобно при импорте: внутри одной большой транзакции для каждой строки делают savepoint и при ошибке откатывают только её, продолжая остальные.

Из практики: ORM часто использует savepoint автоматически для вложенных транзакций. Слишком много savepoint увеличивает оверхед.

VACUUM очищает таблицу от устаревших версий строк, оставленных MVCC. Освобождает место внутри страниц для повторного использования, обновляет visibility map и статистику.

Autovacuum — фоновая служба, запускающая VACUUM (и ANALYZE) автоматически по достижении порогов изменений. Настраивается параметрами autovacuum_vacuum_scale_factor и т.д.

Из практики: без adequately настроенного autovacuum таблица «раздувается» — растут размер на диске и время сканирования. На больших таблицах часто настраивают пер-таблично более агрессивный autovacuum.

Полнотекстовый поиск умеет искать не подстроки, а смысл: учитывает морфологию (читал → читать), стоп-слова, ранжирование по релевантности. В Postgres есть tsvector/tsquery и GIN-индекс.

LIKE 'foo%' работает по символьному совпадению и без индекса делает sequential scan. Подходит для простых случаев («начинается с»), но не для естественного поиска по контенту.

Из практики: для поиска по статьям, описаниям товаров — полнотекстовый. Для строгих фильтров (email LIKE '@example.com') — LIKE с trigram-индексом или функциональным.

Pessimistic — это когда транзакция заранее блокирует строку (SELECT FOR UPDATE) и другие ждут. Простая и понятная модель, но снижает параллелизм.

Optimistic — транзакции работают без блокировок, а при COMMIT проверяется, что данные не изменились (через version-поле или временную метку). Если изменились — ошибка, попробуй ещё раз.

Из практики: pessimistic подходит для редких изменений с высокой ценой конфликта (платежи). Optimistic — для частых независимых правок (профиль, корзина), где конфликт редок.

TIMESTAMP хранит дату-время без часового пояса — буквально как написано. TIMESTAMPTZ хранит UTC и приводит к timezone клиента при чтении.

Главная ошибка — хранить локальное время без пояса и потом не понимать, что значит «12:00». Стандартная рекомендация: TIMESTAMPTZ + хранить в UTC. На клиенте всё конвертируется в нужный пояс.

Из практики: при работе с несколькими часовыми поясами TIMESTAMP без TZ — источник постоянных багов (двойное добавление UTC offset, поломанные интервалы вокруг DST).

CROSS JOIN даёт декартово произведение — каждая строка слева с каждой справа. На таблицах в миллион строк это триллион строк результата.

INNER JOIN использует условие ON и оптимизатор выбирает hash/merge/nested loop — на индексах работает быстро.

Из практики: CROSS JOIN иногда нужен для генерации комбинаций (даты × категории), но всегда вместе с разумным фильтром в WHERE.

FULL OUTER JOIN возвращает все строки из обеих таблиц. Совпадения соединяются, несовпадающие строки получают NULL с противоположной стороны.

Применяют для сверок: найти, какие записи есть только в A, какие только в B, какие в обеих.

Из практики: типовой пример — сверка данных между двумя источниками для миграции, ETL, аудита.

Self-join — это соединение таблицы с самой собой через алиасы. Применяют для иерархических данных (сотрудник → начальник), для сравнения строк в одной таблице (текущая со следующей).

Например, SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id.

Из практики: для иерархий лучше рекурсивный CTE — он обходит несколько уровней. Self-join хорош на один-два уровня.

HAVING фильтрует группы после GROUP BY по агрегатам. Например, SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 10 — пользователи с более 10 заказами.

В отличие от WHERE, HAVING работает с агрегатами, которые видны только после группировки.

Из практики: незаменим для отчётов «у кого больше N» или «у кого сумма выше X». Если можно отфильтровать строкой — WHERE.

LIMIT N OFFSET M возвращает следующие N строк после пропуска M. Удобно для пагинации.

Проблема: для больших OFFSET СУБД всё равно читает M+N строк и отбрасывает M. На странице 1000 это медленно.

Из практики: для больших данных используют keyset-пагинацию: WHERE id > last_id LIMIT N. Это O(log n) против O(n) для OFFSET.

Расширения GROUP BY для агрегации по нескольким наборам колонок одним запросом. ROLLUP даёт промежуточные итоги по иерархии (страна, город, итого). CUBE — все возможные комбинации. GROUPING SETS — явный список.

Намного быстрее, чем UNION нескольких GROUP BY.

Из практики: для отчётов с подытогами — классический инструмент. Поддерживается во всех серьёзных СУБД (Postgres, Oracle, SQL Server).

CASE WHEN condition THEN value [WHEN ...] ELSE other END — выражение if/else в SQL. Можно использовать в SELECT, WHERE, ORDER BY, GROUP BY.

Простая форма: CASE column WHEN val1 THEN ... WHEN val2 THEN ... END. Поисковая — с произвольными условиями.

Из практики: типовой инструмент для условной агрегации (SUM(CASE WHEN status='paid' THEN amount END)), категоризации, сортировки по приоритету.

SELECT ... FROM (SELECT ... FROM t WHERE ...) AS sub — это вложенный SELECT как источник данных. Псевдоним обязателен.

Полезен для пред-агрегации перед JOIN, для отбора с window function, для упрощения сложных запросов.

Из практики: часто переписывается в CTE для лучшей читаемости. Большинство современных СУБД оптимизирует оба варианта одинаково.

LATERAL — это разрешение подзапросу в FROM ссылаться на колонки предыдущих таблиц того же FROM. Похоже на CROSS APPLY в SQL Server.

Применяется для «для каждой строки слева выполни этот подзапрос»: top-N на группу, динамические агрегаты.

Из практики: даёт компактный способ выразить запросы, которые иначе требуют window function или коррелированных подзапросов с побочными эффектами.

NOT NULL запрещает значение NULL в столбце. При попытке вставки/обновления с NULL — ошибка.

Это самый базовый способ обеспечить наличие данных. Часто комбинируют с DEFAULT.

Из практики: ставьте NOT NULL по умолчанию там, где можно. Это убирает целый класс багов «забыли заполнить поле».

CHAR(n) — фиксированная длина n, доконечно дополняется пробелами. VARCHAR(n) — переменная до n, занимает только реальный объём + 1-2 байта на длину.

В большинстве современных СУБД VARCHAR удобнее и не медленнее. CHAR оправдан для строго фиксированной длины (код страны).

Из практики: для текста почти всегда VARCHAR. В Postgres VARCHAR без n и TEXT по производительности одинаковы.

COUNT(*) считает все строки. COUNT(column) — только строки, где column не NULL.

COUNT(DISTINCT column) — уникальные не-NULL значения.

Из практики: помнить, что COUNT(column) пропускает NULL — типовая ошибка. Если столбец NOT NULL — оба варианта дадут одно число.

DEFAULT задаёт значение, которое подставится, если при INSERT столбец не указан или передано DEFAULT.

Может быть константой, функцией (CURRENT_TIMESTAMP, gen_random_uuid()), последовательностью (NEXT VALUE FOR).

Из практики: created_at часто делают NOT NULL DEFAULT CURRENT_TIMESTAMP. Это избавляет от необходимости подставлять значение в коде каждый раз.

SEQUENCE — отдельный объект, генерирующий уникальные числа. nextval() возвращает следующее значение. В Postgres использовали SEQUENCE до появления GENERATED ALWAYS AS IDENTITY (стандарт SQL).

В отличие от MySQL AUTO_INCREMENT, SEQUENCE — отдельный объект, можно использовать в нескольких таблицах, явно управлять.

Из практики: для нового кода в Postgres лучше IDENTITY-колонки. Но SEQUENCE остаётся, когда нужны более тонкие настройки (cache, increment by, cycle).

Случайные UUID (v4) — большие (16 байт vs 8 у BIGINT), не упорядочены — индекс на них растёт не последовательно, что ухудшает кеш и фрагментацию.

Упорядоченные варианты (UUID v7, ULID) сохраняют преимущество глобальной уникальности и не убивают индекс.

Из практики: для распределённых систем и публичных id — UUID. Для внутренних таблиц с тяжёлыми JOIN'ами — int/bigint. Можно использовать оба: bigint id для join и uuid public_id для внешнего мира.

Расширение Postgres с функциями шифрования: crypt() для хеширования паролей, gen_random_uuid(), digest(text, 'sha256'), encrypt/decrypt.

Удобно для хранения чувствительных данных в зашифрованном виде на уровне БД.

Из практики: для паролей лучше использовать bcrypt/argon2 на уровне приложения. pgcrypto — для случаев, когда шифрование на стороне БД оправдано.

Расширение Postgres, собирающее статистику по выполненным запросам: количество, общее время, мин/макс/среднее, IO.

Запросы группируются по шаблону (с заменой параметров). Видно top самых медленных и самых частых запросов.

Из практики: первая утилита для диагностики медленной БД. Включают на проде и регулярно анализируют.

WAL — журнал изменений Postgres. Каждое изменение сначала пишется в WAL, потом применяется к таблицам. Это даёт durability (D из ACID) и основу для репликации.

На WAL построены: репликация (поток изменений на реплику), point-in-time recovery (PITR), логическое декодирование.

Из практики: размер WAL и checkpoint частота — важные параметры тюнинга. Слишком частый checkpoint — лишний IO.

Каждое соединение с БД — ресурс (память, процесс). Открытие — дорого (TCP, TLS, аутентификация). Pool переиспользует.

PgBouncer / pgcat — внешние pooler для Postgres. Также есть pool в драйверах (HikariCP для Java).

Из практики: чтобы много клиентов работали с малым числом серверных соединений, используют PgBouncer в transaction mode.

NULL — это «неизвестно», а не «пусто». Все операции с NULL дают NULL: NULL = NULL → NULL (не TRUE), NULL + 1 → NULL.

Для проверки — IS NULL / IS NOT NULL. Тернарная логика (TRUE/FALSE/NULL) усложняет условия.

Из практики: типовая ошибка — фильтр WHERE col != 'x' и удивление, что не вернулись строки с NULL. Нужно WHERE col != 'x' OR col IS NULL.

EXPLAIN (ANALYZE, BUFFERS) показывает, сколько страниц в буферном кеше прочитано (hit) и сколько с диска (read).

Высокий read — запрос упирается в IO, кеш не помогает. Высокий hit — данные в памяти, ускорение CPU-bound оптимизациями.

Из практики: BUFFERS помогает понять, стоит ли увеличивать shared_buffers, какие запросы холодные.

В стандарте SQL repeatable read предотвращает non-repeatable read и dirty read, но допускает phantom read. В Postgres repeatable read = snapshot isolation, и phantom тоже предотвращён (почти серилазуемая).

Использует MVCC: транзакция видит snapshot БД на момент старта.

Из практики: для большинства задач default READ COMMITTED достаточен. REPEATABLE READ — когда внутри транзакции нужно несколько раз читать одни данные и видеть одно и то же.

В Postgres MVCC создаёт новую версию строки при UPDATE. Обычно нужно обновить и индексы (новая строка — новое местоположение).

HOT — оптимизация: если обновляется только не-индексированный столбец, и в той же странице есть место, индексы остаются прежними.

Из практики: HOT-update сильно ускоряет UPDATE на колонках вне индексов. Это причина, почему лишние индексы замедляют запись.

-> возвращает поле как JSON. ->> возвращает как text. @> проверяет включение (contains). ? проверяет существование ключа. #>> идёт по пути.

Пример: SELECT data->>'name' FROM users WHERE data @> '{"active": true}'.

Из практики: для эффективного поиска по JSONB ставят GIN-индекс. Это даёт быстрый @>, ?, ?&, ?|.

GENERATED ALWAYS AS (expression) — столбец, значение которого вычисляется автоматически из других. STORED (хранится физически) или VIRTUAL (вычисляется при чтении, не во всех СУБД).

Удобно для производных значений, индексов на сложных выражениях, нормализации.

Из практики: классический пример — full_name AS (first_name || ' ' || last_name). Можно индексировать generated column для поиска.

Название идёт от математического термина «отношение» (relation). В реляционной модели Кодда отношение — это таблица: множество кортежей (строк) с одинаковым набором атрибутов (колонок).

Связи между сущностями выражаются не указателями, а значениями ключей в других таблицах — поэтому модель «реляционная»: всё описывается через отношения и операции над ними (выборка, проекция, соединение).

Из практики: понимание реляционной модели помогает писать корректные SQL — становится ясно, почему JOIN, GROUP BY и подзапросы устроены именно так.

Нормализация — приведение схемы к виду, минимизирующему избыточность и аномалии. Шаги: 1NF — атомарные значения, без повторяющихся групп. 2NF — 1NF + все неключевые атрибуты полностью зависят от первичного ключа (а не от его части). 3NF — 2NF + неключевые атрибуты не зависят транзитивно через другие неключевые.

Дальше — BCNF, 4NF, 5NF — для редких краевых случаев. На практике обычно достигают 3NF/BCNF.

Из практики: нормализация — это не «всегда хорошо». В аналитических хранилищах часто специально денормализуют для скорости чтения.

Денормализация — намеренное введение избыточности данных (дублирование, материализованные агрегаты, плоские таблицы) ради ускорения чтения, упрощения запросов или повышения доступности.

Применяю в OLAP-хранилищах (звезда/снежинка), в reporting-таблицах, в read-replica для тяжёлых запросов. В OLTP — точечно: счётчики, кэшированные агрегаты с фоновой синхронизацией.

Из практики: денормализация всегда обмен. Получаем скорость чтения, платим консистентностью и сложностью записи. Поэтому только при подтверждённой бизнес-потребности.

1:1 — одна строка слева соответствует не более чем одной справа (пользователь и его профиль). 1:N — одной строке слева соответствует много справа (клиент и его заказы). N:M — обе стороны имеют много (студенты и курсы).

1:1 редко самостоятельна — обычно следствие разделения таблицы по причинам производительности или безопасности. N:M реализуется через промежуточную таблицу-связку.

Из практики: при описании предметной области сначала рисую сущности и связи на бумаге. Потом превращаю в физическую схему — это снимает половину будущих изменений.

Через промежуточную таблицу-связку (junction table). Например, для students и courses создаётся student_courses с парой внешних ключей (student_id, course_id) — первичным ключом может быть их комбинация.

В связке часто появляются дополнительные атрибуты связи: дата записи, оценка, роль участника. Это превращает её в полноценную сущность.

Из практики: как только в связке появляются атрибуты — переименуйте её в осмысленную сущность (Enrollment, Membership). Дальше будет проще обсуждать.

Первичный ключ (PK) — атрибут или набор атрибутов, однозначно идентифицирующий каждую строку таблицы. Свойства: уникальность, NOT NULL, неизменяемость (по согласию команды).

Бывает естественный (значение из домена — ИНН, email) и суррогатный (искусственный UUID/serial). На практике чаще используют суррогатный — он стабильнее и не зависит от изменений бизнеса.

Из практики: естественный ключ кажется «честным», но почти всегда подводит — ИНН меняется, email перевыпускается. Суррогатный безопаснее по умолчанию.

Внешний ключ (FK) — атрибут или набор атрибутов в одной таблице, ссылающийся на PK другой (или той же) таблицы. Гарантирует ссылочную целостность: нельзя вставить orders.client_id, которого нет в clients.id.

Поведение при удалении/обновлении родителя задаётся ON DELETE / ON UPDATE: CASCADE (удалить детей), SET NULL, SET DEFAULT, RESTRICT, NO ACTION.

Из практики: отказ от FK на уровне БД «ради производительности» — частая ошибка. Сэкономленные миллисекунды оборачиваются битыми данными, которые потом чинят руками.

Индекс — структура данных (чаще B-tree), ускоряющая поиск и сортировку по выбранным колонкам ценой накладных расходов на запись. Типы: B-tree (универсальный), Hash (равенство), GIN/GiST (полнотекст, JSON, гео), BRIN (большие таблицы с порядком), частичный, функциональный, составной.

Индекс вредит, когда: таблица маленькая (полный скан быстрее), много операций записи (каждый индекс — лишняя запись), низкая селективность (мало уникальных значений), запросы не покрываются индексом, дублирующиеся индексы.

Из практики: «больше индексов — быстрее» — миф. Хороший индексный план — это 3–5 продуманных индексов под реальные запросы, а не 20 «на всякий случай».

Транзакция — логически целостная последовательность операций, которая выполняется как единое целое: либо вся целиком (commit), либо никак (rollback).

Гарантии транзакции описывают свойства ACID. Типичный пример: списать со счёта A и зачислить на B — оба действия должны произойти вместе.

Из практики: не любая «логическая операция» — это БД-транзакция. Распределённые операции через очереди и микросервисы требуют отдельных подходов (Saga, outbox), а не одной транзакции на всё.

Atomicity — атомарность: всё или ничего. Consistency — согласованность: после commit БД остаётся в валидном состоянии (соблюдены constraints). Isolation — изолированность: параллельные транзакции не видят промежуточных состояний друг друга. Durability — устойчивость: после commit изменения переживают сбой.

ACID обычно полностью реализуют реляционные БД (PostgreSQL, Oracle, MS SQL). В NoSQL и распределённых системах часто жертвуют Consistency ради Availability/Performance.

Из практики: чаще всего «теряется» Isolation — её уровень в коде явно не указывают, и в продакшене ловят странные баги.

Стандарт SQL: Read Uncommitted (видит чужие незакоммиченные изменения — dirty read), Read Committed (видит только закоммиченные), Repeatable Read (повторное чтение даёт тот же результат), Serializable (полная изоляция, как при последовательном выполнении).

Чем выше уровень — тем меньше аномалий, но дороже по блокировкам и параллелизму. Реальное поведение зависит от СУБД: например, PostgreSQL Read Committed — это снимок (snapshot), а не блокировки.

Из практики: для большинства приложений Read Committed достаточно. Serializable использую точечно — там, где деньги/учёт.

Dirty read — транзакция читает изменения другой ещё незакоммиченной транзакции. Non-repeatable read — повторное чтение той же строки даёт другое значение, потому что другая транзакция её обновила и закоммитила. Phantom read — повторный запрос с тем же условием возвращает другой набор строк (другая транзакция вставила/удалила).

Защита: Read Committed запрещает dirty read; Repeatable Read добавляет защиту от non-repeatable; Serializable защищает и от phantom.

Из практики: phantom-чтения чаще всего вылавливают в отчётных запросах — суммы расходятся между двумя select'ами в одной транзакции.

Теорема Брюера: распределённая система не может одновременно гарантировать все три свойства — Consistency (все узлы видят одинаковые данные), Availability (каждый запрос получает ответ), Partition tolerance (система работает при разрывах сети).

При сетевом разрыве (P случается всегда в распределёнке) приходится выбирать между C и A: CP-системы предпочитают согласованность (отказывают в ответе), AP — доступность (могут вернуть устаревшие данные).

Из практики: CAP — упрощение. Реально выбор не «или/или», а тонкая настройка уровней согласованности (quorum-чтение/запись).

Партиционирование — разбиение большой таблицы на части (партиции) для ускорения запросов и упрощения обслуживания. Партиции хранятся отдельно, но логически — одна таблица.

Принципы: по диапазону (range — по дате), по списку значений (list — по региону), по хэшу (hash — равномерное распределение), композитные. В Postgres есть декларативное партиционирование, в MySQL — встроенное.

Из практики: больше всего пользы — на временных рядах (логи, события). DROP старой партиции занимает секунды, DELETE миллионов строк — часы.

Репликация — поддержание копий данных на нескольких узлах для отказоустойчивости и масштабирования чтения. Виды: master-slave (записи в один, чтения с многих), master-master (записи в любой), синхронная/асинхронная, потоковая/логическая.

Trade-off: синхронная даёт сильную согласованность, но снижает скорость записи и доступность. Асинхронная — быстрая, но реплики отстают (replication lag).

Из практики: read-replica для аналитических запросов экономит ресурс master'а. Но нужно помнить про lag — отчёт за «сегодня» может не увидеть данные последних секунд.

Шардирование — горизонтальное разбиение данных на независимые узлы (шарды) по ключу шардирования. Каждый шард содержит часть данных, не дублирующую другие.

Отличие от репликации: репликация делает копии данных (для отказоустойчивости/чтения), шардирование делит данные (для масштаба записи и объёма). Часто используются вместе: шарды реплицируются.

Из практики: выбор ключа шардирования — самое сложное. Плохой ключ ведёт к hotspots, дисбалансу, тяжёлым кросс-шард-запросам.

Уровни: 1) запросы — переписать, избавиться от SELECT *, лишних JOIN, неэффективных подзапросов; 2) схема — индексы, нормализация/денормализация, разделение на partitions; 3) настройки СУБД — кэши, параллелизм, vacuum/analyze; 4) архитектура — кэширование (Redis), read-replica, шардирование.

Любая оптимизация начинается с измерения. Сначала EXPLAIN/EXPLAIN ANALYZE, потом изменения, потом снова замер.

Из практики: 80% выигрыша даёт работа с запросами и индексами. Менять «настройки сервера» — почти всегда последний шаг.

DDL (Data Definition) — структура: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation) — данные: SELECT, INSERT, UPDATE, DELETE, MERGE. DCL (Data Control) — права: GRANT, REVOKE. TCL (Transaction Control) — транзакции: BEGIN/COMMIT/ROLLBACK/SAVEPOINT.

Иногда выделяют отдельно DQL (Data Query) для SELECT. Это условное деление — в разных учебниках границы немного разные.

Из практики: понимание групп помогает в коде и в код-ревью. DDL внутри миграции — отдельно; DML — в приложении; DCL — у админов.

JOIN — операция соединения строк двух таблиц по условию. INNER JOIN — только совпадающие пары. LEFT JOIN — все строки слева, совпадения справа (несовпавшие — NULL). RIGHT JOIN — зеркально. FULL OUTER JOIN — все строки с обеих сторон. CROSS JOIN — декартово произведение.

Дополнительно: SELF JOIN (таблица сама с собой), NATURAL JOIN (по одноимённым колонкам — лучше избегать), USING (короткая запись ON).

Из практики: LEFT JOIN с проверкой `WHERE right.id IS NULL` — типовой способ найти «строки в одной таблице, которых нет в другой».

WHERE фильтрует строки ДО группировки, HAVING — группы ПОСЛЕ агрегации. В WHERE нельзя использовать агрегатные функции (SUM, COUNT), в HAVING — можно.

Пример: `SELECT region, SUM(amount) FROM orders WHERE status='paid' GROUP BY region HAVING SUM(amount) > 1000`. WHERE отсекает строки с другим статусом, HAVING — регионы с маленькой суммой.

Из практики: ставить условие в HAVING вместо WHERE — типовой косяк по производительности. Если можно в WHERE — кладите туда: меньше строк дойдёт до агрегации.

DELETE — DML, удаляет строки по условию (можно с WHERE), пишет в журнал, срабатывают триггеры, обратимо в транзакции. TRUNCATE — DDL, удаляет все строки одной операцией без построчного журнала, обычно сбрасывает счётчики, может быть необратим. DROP — DDL, удаляет саму таблицу со схемой и данными.

По скорости: TRUNCATE > DELETE для больших таблиц. По безопасности: DELETE контролируем условием, DROP/TRUNCATE — массовые операции.

Из практики: в проде команды TRUNCATE и DROP — это всегда чек-лист и резервная копия. DELETE без WHERE опасен не меньше.

UNION объединяет результаты двух SELECT и убирает дубликаты строк (внутренне делает SORT/HASH). UNION ALL объединяет без проверки на дубли — поэтому быстрее.

Используйте UNION ALL, если знаете, что дубликатов не будет или они не мешают. UNION — только когда явно нужна дедупликация.

Из практики: UNION без необходимости — частая причина медленных отчётов. На больших объёмах разница может быть в разы.

Четыре основных способа: LEFT JOIN + IS NULL (`SELECT a.* FROM a LEFT JOIN b ON a.id=b.a_id WHERE b.a_id IS NULL`), NOT EXISTS (`SELECT a.* FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id=a.id)`), NOT IN (опасен при NULL), EXCEPT/MINUS (в зависимости от СУБД).

Рекомендуется NOT EXISTS — он корректно обрабатывает NULL и обычно выбирает хороший план. LEFT JOIN + IS NULL — классика, тоже работает.

Из практики: NOT IN с подзапросом, в котором может быть NULL, — типовой источник «странных» результатов (возвращает пустоту).

Подзапрос — SELECT внутри другого SELECT/INSERT/UPDATE/DELETE. Бывает скалярный (одно значение), in-list, EXISTS, в FROM (derived table), коррелированный (зависит от внешнего запроса).

CTE (Common Table Expression, WITH) — именованный временный результат, на который можно ссылаться по имени. Улучшает читаемость многошаговых запросов и поддерживает рекурсию (WITH RECURSIVE).

Из практики: длинные вложенные подзапросы превращаю в CTE — резко падает порог входа для коллеги, читающего запрос завтра.

Оконные функции выполняют агрегацию по «окну» строк, не схлопывая их в одну. Синтаксис: `func() OVER (PARTITION BY ... ORDER BY ...)`. Примеры: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER (..).

Пример — топ-3 заказа по клиенту: `SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY amount DESC) AS rn FROM orders) WHERE rn <= 3`.

Из практики: оконные функции упрощают аналитические запросы, которые на «обычном SQL» требуют тяжёлых self-join или коррелированных подзапросов.

Классика: `SELECT col1, col2, COUNT(*) FROM t GROUP BY col1, col2 HAVING COUNT(*) > 1`. Покажет, какие комбинации полей повторяются.

Для просмотра самих дублирующихся строк удобнее ROW_NUMBER: `SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn FROM t) WHERE rn > 1`.

Из практики: перед DELETE дублей обязательно нужен SELECT с теми же условиями — иначе можно случайно снести лишнее.