SQL Ready | Базы Данных

NULL и NOT IN — тонкость SQL, приводящая к логическим ошибкам!

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

Представим таблицы:

customers(id)
orders(id, customer_id)

Нужно найти клиентов, у которых нет заказов.

Интуитивный вариант:

SELECT id
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM orders
);

Если подзапрос возвращает хотя бы одно значение NULL, результат этого запроса будет пустым, даже если клиенты без заказов существуют.

Это происходит потому, что NOT IN сводится к серии сравнений, а любое сравнение с NULL возвращает неопределённый результат.

Попытка исправить ситуацию фильтрацией:

SELECT id
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

Формально запрос корректен, но требует постоянного контроля и легко ломается при изменении подзапроса.

Надёжный вариант — использовать NOT EXISTS:

SELECT c.id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

NOT EXISTS корректно обрабатывает NULL и предназначен именно для проверок отсутствия связанных строк.

🔥 Используй NOT EXISTS для анти-джойнов и проверок отсутствия данных, а NOT IN — только при полном контроле результата подзапроса.

➡️ SQL Ready | #практика

Условный UPSERT: как не обновлять строки без изменений?

Обычный UPSERT обновляет строку всегда, даже если данные не изменились — это лишние блокировки, WAL и autovacuum.

PostgreSQL позволяет сделать условный UPDATE прямо в ON CONFLICT:

ON CONFLICT (id) DO UPDATE
...
WHERE users.email IS DISTINCT FROM EXCLUDED.email
   OR users.name  IS DISTINCT FROM EXCLUDED.name;

Если данные совпадают — UPDATE не выполняется вообще.

EXCLUDED — это “новая” версия строки, users.* — текущая версия в таблице.

users.col IS DISTINCT FROM EXCLUDED.col

🔥 Корректно работает даже с NULL и не попадает в ловушки трёхзначной логики.

➡️ SQL Ready | #совет

📂 Напоминалка по масштабированию баз данных!

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

На картинке — 10 техник масштабирования БД, которые стоит держать под рукой при работе с высокими нагрузками.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

FILTER в агрегатных функциях PostgreSQL!

В аналитических запросах часто нужно посчитать несколько показателей из одной таблицы. В PostgreSQL для этого есть FILTER, позволяющий задавать условия отдельно для каждой агрегатной функции, не влияя на весь запрос.

Представим таблицу заказов:

orders(id, customer_id, amount, status)

Посчитаем общее количество заказов и количество завершённых:

SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders;

FILTER применяется непосредственно к агрегатной функции и ограничивает только те строки, которые участвуют в её расчёте.

Добавим несколько метрик в одном запросе:

SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'canceled')  AS canceled_orders,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_amount
FROM orders;

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

FILTER можно использовать с любыми агрегатами:

AVG(amount) FILTER (WHERE status = 'completed')
MAX(amount) FILTER (WHERE status = 'completed')
MIN(amount) FILTER (WHERE status = 'completed')

🔥 Важно помнить: FILTER работает только с агрегатными функциями и применяется внутри SELECT, дополняя, а не заменяя WHERE и GROUP BY.

➡️ SQL Ready | #практика

😎 SQL Style Guide — супер полезный репозиторий для освоения языка!

Практичный ресурс по написанию SQL: как оформлять SELECT, JOIN, CTE, подзапросы и имена таблиц, чтобы запросы были понятными, поддерживаемыми и удобными для работы в команде. Подходит для любых СУБД и реально упрощает работу и учебу.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

🖥 GROUPING SETS — несколько отчётов в одном запросе!

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

Сегодня в гайде:

• Как считать несколько уровней агрегации за один проход по данным;

• Как отличать строки-итоги от обычных данных;

• Почему такой подход проще поддерживать и масштабировать.

Приём, который делает отчёты чище, быстрее и предсказуемее при росте требований.

➡️ SQL Ready | #гайд

Как корректно сравнивать значения с NULL?

Обычное сравнение может сломаться, когда в данных появляется NULL. В SQL выражение:

email <> 'admin@example.com'

не вернёт строки с email IS NULL — результат будет UNKNOWN.

Для корректного сравнения используйте IS DISTINCT FROM:

email IS DISTINCT FROM 'admin@example.com'

Оно работает так, как ожидаешь:
NULL ≠ любое значение
NULL = NULL

Результат всегда TRUE или FALSE (без UNKNOWN).

То же самое для проверки изменений:

old_value IS DISTINCT FROM new_value

🔥 Это инструмент не для синтаксиса, а для корректности данных.

➡️ SQL Ready | #совет

DISTINCT vs GROUP BY — выбираем правильный инструмент для удаления дублей!

В SQL часто нужно избавиться от повторяющихся строк: уникальные пользователи, товары, категории. Для этого используют DISTINCT и GROUP BY. Результат может выглядеть одинаково, но назначение и смысл у этих конструкций разные.

Представим таблицу заказов:

orders(id, customer_id, product_id)

Найдём всех уникальных клиентов, которые делали заказы:

SELECT DISTINCT customer_id
FROM orders;

DISTINCT удаляет дубликаты по всему набору выбранных колонок в результирующем наборе — без группировок и агрегаций.

Сделаем то же самое через GROUP BY:

SELECT customer_id
FROM orders
GROUP BY customer_id;

Результат будет тем же, но семантически запрос другой: явно группируем строки по customer_id. В простых случаях оптимизатор часто строит одинаковый план, но логика запроса уже «про группы».

GROUP BY становится необходимым, когда появляются агрегаты.

Посчитаем количество заказов на каждого клиента:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id;

В этом запросе GROUP BY обязателен, потому что мы одновременно выбираем агрегат (COUNT(*)) и неагрегированное поле (customer_id).

Частая ошибка — смешивать DISTINCT и агрегаты без GROUP BY:

SELECT DISTINCT customer_id, COUNT(*)
FROM orders;

Такой запрос в стандартном SQL некорректен: неагрегированные поля должны присутствовать в GROUP BY. В зависимости от СУБД и режима он либо не выполнится, либо вернёт неопределённый результат.

Корректный вариант:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id;

🔥 Используй DISTINCT для простого удаления дублей, а GROUP BY — когда нужна агрегация, расчёты по группам или HAVING.

➡️ SQL Ready | #практика

📂 Напоминалка по структурам данных для экономии памяти и работы с большими данными!

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

На картинке — 6 структур данных, которые стоит держать под рукой при проектировании backend-систем, аналитики и highload-сервисов.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

Как избежать блокировок таблиц с помощью advisory locks в PostgreSQL!

Иногда нужно гарантировать, что только один процесс выполняет критическую секцию, но при этом не хочется блокировать таблицы и строки.

Для этого PostgreSQL предоставляет advisory locks — логические блокировки, не привязанные к таблицам или строкам.

SELECT pg_advisory_xact_lock(42);

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

Ключ — это просто число. Можно использовать user_id, order_id, хеш или tenant_id.

SELECT pg_advisory_xact_lock(user_id);

🔥 Это превращает PostgreSQL в механизм распределённой синхронизации. После COMMIT или ROLLBACK блокировка снимается автоматически.

➡️ SQL Ready | #совет

Префиксные индексы в MySQL — ускоряем поиск по длинным строкам!

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

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

CREATE TABLE documents (
    id INT PRIMARY KEY,
    doc_key VARCHAR(500) NOT NULL
);

Добавим префиксный индекс. Индексируются только первые 20 символов:

CREATE INDEX idx_doc_key_prefix
ON documents (doc_key(20));

Если фильтровать данные по фиксированному началу строки, MySQL использует префиксный индекс:

SELECT id
FROM documents
WHERE doc_key LIKE 'INV-2024-%';

Важно: индекс применяется только если шаблон начинается без ведущего %. Например, LIKE '%2024%' уже не сможет его использовать.

Пример с email — если полная индексация не нужна:

CREATE INDEX idx_email_prefix
ON users (email(16));

🔥 Ограничения: префикс должен быть достаточно селективным, иначе польза минимальна. Такие индексы практически не подходят для сортировки или группировки по полному полю, так как индекс содержит лишь его часть.

➡️ SQL Ready | #практика

❤️ SQL-101 — руководство, помогающее освоить язык и укрепить базу тем, кто уже работает с БД.

В этом ресурсе собраны ключевые темы, которые нужны в работе: базовые запросы, фильтрация, JOIN, группировки, подзапросы, индексы, транзакции и основы оптимизации. Всё объяснено простым языком и дополнено примерами с упражнениями.

Оставляю ссылочку: Github 📱

➡️ SQL Ready | #репозиторий

Почему Index Only Scan в PostgreSQL не всегда работает?

Если PostgreSQL не использует Index Only Scan, проблема часто не в запросе и не в самом индексе.

Index Only Scan работает только если страницы помечены как видимые в visibility map. Если этого нет PostgreSQL всё равно идёт в таблицу.

Проверьте план выполнения:

EXPLAIN ANALYZE
SELECT id FROM orders WHERE status = 'pending';

Если видите Index Scan, а не Index Only Scan, одна из частых причин в том, что visibility map не заполнена (при наличии подходящего covering index).

Исправляется простой командой:

VACUUM (ANALYZE) orders;

🔥 VACUUM помечает страницы как all-visible, и PostgreSQL может перестать читать таблицу.

➡️ SQL Ready | #совет

📂 Напоминалка для работы с индексами в базах данных!

Например, B+ Tree Index используется для быстрого поиска и сортировки, а Hash Index подходит для точных совпадений по ключу.

На картинке — 5 основных структур данных, на которых строятся индексы в современных СУБД.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

Как понять, какие индексы только тратят место?

Ненужные индексы замедляют вставки, обновления и VACUUM. PostgreSQL умеет показать, какие индексы ни разу не использовались.

Посмотрим статистику использования:

SELECT relname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

idx_scan = 0 — индекс ни разу не участвовал в плане.
size покажет, сколько места он занимает на диске.

Нужно увидеть “почти бесполезные” индексы? С сортировкой по минимальному использованию:

SELECT relname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

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

➡️ SQL Ready | #совет