Все знают GROUP BY.
Тот самый оператор, который превращает кучу строк в аккуратную табличку с суммами и средними.

Но можно и по-другому взглянуть на GROUP BY

А пока подписывайся на мой канал На связи: SQL Там я публикую посты про особенности и нюансы SQL. Этот канал про то, как не бояться баз данных, понимать, что такое JOIN, GROUP BY и почему NULL ≠ 0. Его я веду с нуля подписчиков. Присоединяйся!

В большинстве случаев GROUP BY используют вместе с агрегирующими функциями SUM, COUNT или AVG.

Но есть и другие возможности использования группировки.

1. В качестве изящной замены DISTINCT

   SELECT department FROM employees GROUP BY department;

   работает так же, как

   SELECT DISTINCT department FROM employees;

2. Группировать можно по выражениям, а не только по столбцам

   Например, хочешь посчитать заказы по годам:

   SELECT EXTRACT(YEAR FROM created_at) AS year, COUNT(*)

   FROM orders

   GROUP BY EXTRACT(YEAR FROM created_at);

   Или сгруппировать товары по тысячам рублей:

   SELECT (price / 1000)::int AS price_group, COUNT(*)

   FROM products

   GROUP BY (price / 1000)::int;

3. GROUP BY умеет строить иерархии

   ROLLUP, CUBE, GROUPING SETS — три команды богов:

   SELECT region, city, SUM(sales)

   FROM orders

   GROUP BY ROLLUP (region, city);

   → покажет суммы по городам, по регионам и общий итог.
   И всё это одним запросом.

4. NULL — это тоже группа

   Если у тебя несколько строк с NULL в поле department,
   то GROUP BY department соберёт их все в одну группу NULL.

   SELECT department, COUNT(*)

   FROM employees

   GROUP BY department;

   Логичней использовать COALESCE, чтобы потом не работать с пустыми строками

   SELECT COALESCE(department, 'Unknown') AS department, COUNT(*)

   FROM employees

   GROUP BY COALESCE(department, 'Unknown');

5. SELECT vs GROUP BY — всё, что не агрегат, должно быть в GROUP BY

   SELECT department, name, COUNT(*)

   FROM employees

   GROUP BY department;

   Запрос упадёт, потому что name не в агрегате и не в GROUP BY.

   В PostgreSQL есть хитрости: можно использовать array_agg(name) или string_agg(name, ', ')

6. GROUP BY и оконные функции — не конкуренты

   GROUP BY сжимает таблицу.
   OVER(PARTITION BY) — сохраняет строки, но добавляет агрегат.

   SELECT name, department,

   SUM(salary) OVER (PARTITION BY department) AS dep_total

   FROM employees;

7. SQL сам решает, как группировать

   PostgreSQL может выбрать:

   • HashAggregate — если данных много

   • Sort + GroupAggregate — если их мало или мало уникальных значений

   То есть одна и та же команда GROUP BY под капотом работает по-разному.
   Вот почему один и тот же запрос на 10k строк работает мгновенно, а на 10M — вечность.
   
   PostgreSQL не просто тупо группирует строки, а выбирает стратегию (план выполнения) — как именно эту группировку реализовать.
   

   Это можно отследить в EXPLAIN и уже потом контролировать включением/выключением конкретных алгоритмов.

   SET enable_hashagg = off;

   SET enable_sort = off;

   Это полезно для тестирования или отладки - посмотреть, как изменится план.

GROUP BY — это не просто «посчитать среднюю зарплату по отделу».
Это мощный инструмент, который может:

• имитировать DISTINCT

• строить иерархические отчёты

• объединяться с оконными функциями

• …и при этом легко устроить тебе день боли, если ты не знаешь, что делаешь 😅

Псевдонимы в SQL (алиасы) кажутся «косметикой», но у них есть подводные камни и тонкости, которые реально влияют на запросы.

Псевдонимы - это временные имена для таблиц или столбцов внутри запроса.

В моем канале На связи: SQL уже есть про это посты: вот этот. Канал я веду с нуля подписчиков. Чтобы показать, что аналитика и SQL это не страшно, а интересно. Присоединяйся, если ты тоже хочешь начать изучать SQL или вспомнить и осознать некоторые нюансы.

SELECT c.name AS customer_name

FROM customers c;

• c — алиас таблицы customers

• customer_name — алиас для колонки c.name

Внутри результата ты видишь только customer_name.

Где применяются

Сокращение длинных имён

SELECT o.id, c.name

FROM orders o

JOIN customers c ON o.customer_id = c.id;

- читабельнее, чем всё время писать orders.id, customers.name

JOIN и self-join
Без алиасов нельзя отличить таблицы при самосоединении:

SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

Агрегация и подзапросы
Алиас часто обязателен:

SELECT avg(salary) AS avg_salary

FROM employees;

Derived tables (подзапросы в FROM)
Тут алиас обязателен:

SELECT sub.department, sub.cnt

FROM ( SELECT department, count(*) cnt

FROM employees

GROUP BY department

) sub;

Где их нельзя применять / ограничения

В том же уровне WHERE

SELECT salary * 1.2 AS new_salary

FROM employees

WHERE new_salary > 5000; -- ❌ Ошибка

• Алиасы доступны только в SELECT/ORDER BY, но не в WHERE или GROUP BY.
  → нужно повторно писать выражение или завернуть в CTE.

  Порядок обработки
  SQL выполняется в порядке: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
  Алиасы появляются только после SELECT.
  Поэтому:

  • в WHERE и GROUP BY алиасы ещё не видны;

  • в ORDER BY алиасы уже можно использоват

Нюансы, которые часто забывают

1. Алиасы влияют только на внешний уровень запроса
   Они не меняют структуру таблицы.
   В EXPLAIN ты увидишь исходные имена столбцов, а не алиасы.

2. В подзапросах алиас обязателен для таблицы
   Даже если кажется, что можно без него.

SELECT *

FROM (SELECT 1) -- ❌ Ошибка

SELECT *

FROM (SELECT 1) AS t;

1. ORDER BY может использовать алиас
   Ускоряет жизнь:

   SELECT salary * 1.2 AS new_salary
   FROM employees
   ORDER BY new_salary DESC;

2. CTE против алиасов
   Если выражение слишком длинное (например, CASE WHEN …), лучше вынести его в CTE вместо алиаса в SELECT → так его можно переиспользовать и в WHERE, и в ORDER BY.

3. Алиасы могут влиять на читаемость, но и запутывать
   Пример анти-паттерна:

   SELECT u.id AS order_id -- ❌ псевдоним вводит в заблуждение
   FROM users u;

   → плохая идея, потому что вводит читателя в ошибку.

Итог: алиасы — не просто косметика. Это инструмент:

• для читаемости;

• для самосоединений и подзапросов;

• для управления порядком сортировки.

Но они не живут дольше SELECT-а

Подписывайся на мой канал На связи SQL и давай изучать особенности вместе!

Про JOIN обычно пишут общую теоретическую информацию. Всегда упоминают виды JOIN-ов: INNER, LEFT, RIGHT, FULL, CROSS, а за кулисами могут остаться интересные факты, подводные камни и тонкости, которые редко упоминаются, но которые могут реально пригодиться.

В моем канале На связи: SQL уже есть про это посты: вот этот и вот этот. Канал я веду с нуля подписчиков. Чтобы показать, что аналитика и SQL это не страшно, а интересно. Присоединяйся, если ты тоже хочешь начать изучать SQL или вспомнить и осознать некоторые нюансы.

Неочевидные факты про JOIN

Условие в ON vs WHERE

• Для INNER JOIN — разницы нет, фильтрация в ON или в WHERE даст одинаковый результат.

• Но для LEFT JOIN это уже не так:

  • ON фильтрует при объединении (строка может остаться с NULL в правой таблице).

  • WHERE фильтрует после — и может "выбросить" строки, ради которых делался LEFT JOIN.

👉 Классический баг у новичков: пишут условие в WHERE и не понимают, почему LEFT превратился в INNER.

Предположим, у нас есть две таблицы:

orders (таблица заказов):

order_id | customer_id | amount

---------|-------------|--------

1 | 1 | 100

2 | 2 | 200

3 | 1 | 150

customers (таблица клиентов):

customer_id | country

------------|---------

1 | USA

2 | UK

3 | USA

И есть два запроса:

SELECT o.order_id, c.customer_id, c.country

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.customer_id

AND c.country = 'USA';

SELECT o.order_id, c.customer_id, c.country

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.customer_id

WHERE c.country = 'USA';

Результаты этих запросов будут одинаковы?

Можно писать свои рассуждения в комментариях.

JOIN на неравенстве

Обычно мы пишем ON a.id = b.id, но можно и:

SELECT *

FROM a

JOIN b ON a.value BETWEEN b.min AND b.max;

Это называется non-equi join (неравенственный джойн).
👉 В BI и аналитике это часто используют для «поиска диапазона» (например, попадает ли дата заказа в акцию).
Но! Такой JOIN почти всегда тяжелее, потому что индексы плохо помогают.

FULL JOIN в проде почти не используют

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

• Почти всегда можно заменить комбинацией LEFT JOIN UNION RIGHT JOIN.

• А оптимизаторы некоторых СУБД работают с FULL OUTER JOIN медленнее.
  👉 Часто факт наличия FULL JOIN в запросе сигнализирует, что "что-то не так со схемой данных".

CROSS JOIN — не только для Декарта

Все думают, что CROSS JOIN = "перемножить всё на всё". Но он используется:

• для генерации тестовых данных:

SELECT d::date

FROM generate_series('2025-01-01', '2025-01-31', interval '1 day') d

CROSS JOIN users;

для построения матриц, календарей, отчетов с дырками.
👉 То есть CROSS JOIN часто — инструмент BI-разработчика

JOIN и NULL — подстава

• NULL = NULL → всегда FALSE.
  Поэтому если соединяешь таблицы по колонке с NULL, то такие строки просто теряются.
  👉 В проде это часто ломает аналитику: ожидали, что будет связь «пустое с пустым», а SQL этого не понимает.

Производительность JOIN-ов

• Много JOIN-ов (10+) ≠ всегда медленно. Оптимизаторы умеют работать с огромными планами.

• Но JOIN + функции (ON lower(a.name) = lower(b.name)) почти всегда убивает индекс → дорого.
  👉 Лучшее решение — хранить данные в нормализованном виде (например, имена в нижнем регистре).

ANTI JOIN

Вместо NOT IN или NOT EXISTS можно писать LEFT JOIN ... WHERE b.id IS NULL.
Это часто быстрее, особенно в старых версиях MySQL.
👉 Но не забывай: NOT IN (NULL, ...) ведет себя неожиданно (возвращает пустой набор).

JOIN ≠ JOIN ORDER

SQL — декларативный язык. Ты пишешь JOIN-ы в любом порядке, но оптимизатор сам решает, какую таблицу читать первой, как переставить местами соединения. Поэтому писать «самую маленькую таблицу первой» часто не имеет смысла. Но иногда хинты (JOIN ORDER, FORCE JOIN, USE INDEX) всё же нужны, когда оптимизатор ошибается.

Когда мы пишем:

SELECT *

FROM orders o

JOIN customers c ON o.customer_id = c.id

JOIN regions r ON c.region_id = r.id;

мы как бы говорим:
👉 «Дай мне все заказы, вместе с клиентами и регионами».

НО! Мы не указываем порядок, в котором эти таблицы реально будут соединяться.
Оптимизатор (query planner) сам решает:

• какую таблицу читать первой;

• по какому индексу идти;

• в каком порядке выполнять JOIN-ы.

И этот порядок почти всегда ≠ порядок в SQL-запросе.

Как это работает на практике

• Оптимизатор строит граф зависимостей между таблицами и условиями.

• Считает «стоимость» разных стратегий (в PostgreSQL это cost-based optimizer).

• Выбирает план с минимальной стоимостью: например, начать с маленькой таблицы, потом по индексу сходить в большую.

OIN ORDER hints

Иногда оптимизатор ошибается. Причины:

• Неправильная статистика (например, таблица только что обновилась).

• Очень сложный запрос (10+ JOIN-ов, подзапросы).

• Особенности движка (MySQL раньше любил «сначала левую таблицу»).

Тогда СУБД позволяют подсказать оптимизатору:

• FORCE ORDER (SQL Server, Oracle) → использовать JOIN-ы в том порядке, как написаны.

• LEADING (Oracle) → указать, с какой таблицы начать.

• USE INDEX (MySQL) → подсказать, какой индекс использовать.

• PostgreSQL не имеет FORCE JOIN, но можно:

  • отключить конкретные алгоритмы (SET enable_hashjoin = off;)

  • использовать LATERAL, JOIN LATERAL, CROSS JOIN чтобы «подсказать» порядок.

🔹 Когда порядок JOIN реально важен

1. Суперсложные запросы (20+ таблиц).
   Оптимизатор может выбрать очень дорогой план → запрос работает минуты/часы.
   Иногда правильный хинт → ускорение в десятки раз.

2. Необновлённая статистика.
   Оптимизатор думает: «таблица маленькая», но на самом деле она разрослась. → выбирает плохой порядок JOIN.

3. LIMIT + ORDER BY.
   Тут реально важно, с чего начать — иногда оптимизатор «тянет» всю таблицу, хотя мог бы остановиться раньше.

🔹 Лайфхак для практики

• В PostgreSQL можно посмотреть план:

  EXPLAIN (ANALYZE, BUFFERS) SELECT ...

  → увидишь реальный JOIN order.

• Не доверяй слепо «писать маленькую таблицу первой» — это миф, из старых времён MySQL.

• Иногда лучше переписать запрос так, чтобы оптимизатору нечего было гадать. Например, вынести фильтрацию в CTE или subquery.

JOIN — это не только "соединить таблицы", а целый набор особенностей: от NULL и порядка фильтрации до генерации календарей через CROSS JOIN.

Подписывайся на мой канал На связи SQL и давай изучать особенности вместе!

NULL — это специальное значение в SQL, которое означает отсутствие данных или неизвестное значение. Важно понимать, что NULL ≠ 0 и NULL ≠ пустая строка (‘’).

А пока подписывайся на мой канал На связи: SQL Там я публикую посты про особенности и нюансы SQL. Этот канал про то, как не бояться баз данных, понимать, что такое JOIN, GROUP BY и почему NULL ≠ 0. Его я веду с нуля подписчиков. И пост про NULL уже ждет тебя! Присоединяйся!

NULL не равен ничему, даже самому себе. Не является числом, строкой или другим типом данных.

И это надо запомнить при работе с данными.

Все что вы будете сравнивать с NULL будет возвращаться как UNKNOWN.

Для работы с NULL есть специальные операторы.

-- Проверка на NULL

SELECT *

FROM table

WHERE column IS NULL;

-- Проверка на НЕ NULL

SELECT *

FROM table

WHERE column IS NOT NULL;

-- Замена NULL на значение

SELECT COALESCE(column, 'значение_по_умолчанию')

FROM table;

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

Вообще, понятие NULL появилось в начале 1970-х годов. Разработчики реляционных БД специально создали такое значение для обозначения отсутствия данных. Это было революционным решением, позволившим хранить информацию более гибко.

Математические фокусы с NULL:

• Любое число + NULL = NULL

• NULL * 100 = NULL

• NULL / 2 = NULL

• Индексы могут работать медленнее при наличии NULL

• Некоторые типы индексов вообще не поддерживают NULL

• Агрегатные функции игнорируют NULL по умолчанию

Ну и коротко про то что такое Но(у)ль:
Ноль — это чётко определённое значение:

• Числовое значение

• Имеет математический смысл

• Участвует в вычислениях

• Равно самому себе

А теперь про пусто:
Пустота — отсутствие содержимого

Пустое значение может быть:

• Пустой строкой (‘’ или “”)

• Пустым массивом

• Пустым объектом

• Имеет конкретный тип данных

Рекомендации:

• Используйте NULL для неизвестных данных

• Применяйте 0 для числовых значений по умолчанию

• Используйте пустые строки для незаполненных текстовых полей

Заключение

Помните: каждое из этих значений несёт свой смысл. Неправильное использование может привести к:

• Ошибочным расчётам

• Некорректной логике

• Потерям данных

• Системным ошибкам

А как вы работаете с этими значениями в своих проектах? Поделитесь опытом в комментариях!

DELETE — инструменте, который может как спасти вашу базу данных от хлама, так и превратить её в пустыню за считанные секунды.

DELETE как цифровой пылесос**, который может убрать всё, что вы ему скажете. Только вот вернуть обратно будет ой как непросто!

А пока подписывайся на мой канал На связи: SQL Там я публикую посты про особенности и нюансы SQL. Этот канал про то, как не бояться баз данных, понимать, что такое JOIN, GROUP BY и почему NULL ≠ 0. Его я веду с нуля подписчиков. Присоединяйся!

Базовый синтаксис:

DELETE FROM таблица
WHERE условие;

Можно использовать EXISTS с подзапросами

DELETE FROM orders

WHERE EXISTS (

SELECT 1

FROM customers

WHERE customers.id = orders.customer_id

AND customers.status = 'deleted' );

Удаляет заказы неактивных клиентов.

Можно использовать JOIN

DELETE o

FROM orders o

INNER JOIN customers c ON o.customer_id = c.id

WHERE c.registration_date < '2020-01-01';

Удаляет заказы клиентов, зарегистрированных до 2020 года.

Как происходит удаление записей?

Физическое удаление в SQL — это не мгновенное стирание данных с диска. Когда вы выполняете DELETE:

• Система записывает операцию в журнал транзакций

• Помечает удалённые строки как свободные

• Физическое место может быть использовано для новых данных

• Сами данные остаются в файле некоторое время

Формируется основной файл .mdf (или аналогичный). Также информация записывается в журнал транзакций. Сроки хранения удаленных данных зависят от:
- модели восстановления базы данных
- активности базы данных
- настроек резервного копирования
- объема свободного места

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

Способы восстановления данных после DELETE

1. Восстановление из резервной копии

   RESTORE DATABASE имя_базы FROM DISK = 'путь_к_бэкапу'

2. Использование моментальных снимков (Snapshot)

   CREATE DATABASE имя_снимка

   ON (NAME = имя_файла, FILENAME = 'путь_к_файлу')

   AS SNAPSHOT OF исходная_база;

   Восстановление данных из снимка:

   INSERT INTO исходная_таблица

   SELECT * FROM снимок.dbo.таблица

3. Восстановление через LSN (Log Sequence Number)

   Если есть журналы транзакций:

   BACKUP LOG имя_базы TO DISK = 'путь_к_журналу'

   Поиск удалённых записей:
   SELECT [Current LSN], [Transaction ID]

   FROM fn_dblog(NULL, NULL)

   WHERE Operation = 'LOP_DELETE_ROWS'

4. Либо использовать специальные посторонние программы для восстановления БД

Чтобы предотвратить потерю данных:

• Регулярно создавайте резервные копии

• Используйте транзакции (BEGIN TRANSACTION / ROLLBACK)

• Тестируйте DELETE-запросы на тестовой базе

• Применяйте WHERE с осторожностью

• Настройте политики резервного копирования

DELETE vs TRUNCATE

Многие думают, что TRUNCATE и DELETE — это одно и то же. На самом деле:

• DELETE удаляет строки по одной и записывает каждую операцию в журнал транзакций

• TRUNCATE мгновенно очищает таблицу, минуя журнал (кроме некоторых случаев)

При выполнении DELETE:

• Блокируются только удаляемые строки

• Другие транзакции могут читать незаблокированные данные

• В некоторых СУБД возможна эскалация блокировок до уровня таблицы

• Не затрагивает структуру индексов

• Индексы остаются в прежнем состоянии

• Это ускоряет процесс удаления

• Не сбрасывает счётчики автоинкремента

• Сохраняет текущее значение последовательности

• Важно при работе с первичными ключами

• Активируются триггеры AFTER DELETE

• Можно отслеживать удалённые строки через виртуальную таблицу deleted

• Триггеры могут отменить операцию удаления

Чтобы сохранить целостность данных можно использовать ON DELETE CASCADE
Это позволит там, где есть зависимость по внешним ключам произвести удаление зависимых строк.