1. Введение: Почему SQL всё еще «база»?

Кажется, что в ИТ всё меняется каждые пару лет. Фреймворки рождаются и умирают, архитектурные подходы сменяют друг друга, но SQL стабильно остается на месте. Он спокойно пережил хайп вокруг NoSQL, эпоху Big Data и повсеместное внедрение нейросетей.

Почему так происходит? Потому что SQL давно перестал быть просто «языком запросов реляционных баз». Сегодня это универсальный стандарт общения с данными. Неважно, что именно стоит у вас на проекте: классический PostgreSQL, аналитический ClickHouse или распределенная система — скорее всего, вы будете общаться с ней через диалект SQL.

Кому сегодня действительно нужно понимать, как пишутся запросы? Спойлер: почти всем, кто соприкасается с разработкой или продуктом.

  • Бэкенд-разработчикам: чтобы понимать, как данные достаются из базы, не перекладывать работу БД на приложение и не пытаться фильтровать сто тысяч строк в оперативной памяти с помощью условного filter().

  • Аналитикам: это основной рабочий инструмент. Без SQL придется жить в ожидании, пока разработчики сжалятся и выгрузят нужную CSV-шку.

  • QA-инженерам: чтобы проверять реальное состояние системы. Одно дело — увидеть, что фронтенд нарисовал зеленую галочку, и совсем другое — убедиться, что транзакция реально прошла и в таблице orders появился нужный статус.

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

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

2. Анатомия базы данных: с чем мы вообще работаем

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

Реляционная база данных — это, грубо говоря, набор таблиц, которые связаны между собой строгими правилами. Если вы работали в Excel или Google Таблицах, концепция вам знакома.

  • Колонки (поля) задают структуру: какие данные мы храним (имя, email, дата регистрации).

  • Строки (записи) — это сами данные. Одна строка — один конкретный пользователь, заказ или товар.

Главное отличие базы от таблицы в Excel — жесткая типизация и связи. В колонку для чисел база не даст записать текст «пять», а связь не позволит оформить заказ на удаленного пользователя. За это отвечают ключи.

Ключи: как таблицы узнают друг друга

База данных называется «реляционной» (от англ. relation — отношение, связь) именно из-за того, что таблицы умеют ссылаться друг на друга.

  • Primary Key (Первичный ключ) — это уникальный идентификатор конкретной строки. Можно сказать, её паспорт. Чаще всего это колонка id (число или UUID). Первичный ключ гарантирует, что в таблице нет двух абсолютно идентичных строк. Если вам нужно обновить или удалить конкретную запись, вы делаете это по Primary Key.

  • Foreign Key (Внешний ключ) — это колонка, которая ссылается на Primary Key другой таблицы. Например, в таблице orders (заказы) есть колонка user_id. Это и есть внешний ключ. Он указывает, какой именно пользователь из таблицы users сделал этот заказ. Foreign Key следит за консистентностью: база просто не позволит вставить в orders заказ с user_id = 999, если пользователя с таким ID не существует.

Типы данных: на что обратить внимание

Когда мы создаем колонку, мы обязаны сказать базе, что в ней будет лежать. Типов много, но в 90% случаев вы будете работать с базовым набором. Вот пара нюансов, о которых стоит знать:

  • INT / BIGINT — целые числа. Обычно используются для ID, счетчиков, количества товаров. Если таблица планируется огромной, для ID лучше сразу брать BIGINT, чтобы потом не столкнуться с переполнением (ограничение стандартного INT — чуть больше 2 миллиардов).

  • VARCHAR / TEXT — строки. Привычка из нулевых — ставить везде VARCHAR(255) «на всякий случай». В современных базах (особенно в PostgreSQL) тип TEXT работает так же эффективно, но не заставляет вас жестко лимитировать длину строки.

  • TIMESTAMP / TIMESTAMPTZ — дата и время. Важное правило, написанное кровью разработчиков: всегда сохраняйте время в UTC (для этого в Postgres есть TIMESTAMPTZ — время с часовым поясом). Переводить время в локальное для пользователя должен фронтенд или клиентское приложение. Если начнете сохранять локальное время в базу — запутаетесь при первом же переходе на летнее время или при появлении пользователей из других стран.

  • JSONB — мост между реляционным миром и NoSQL. Если структура данных часто меняется или у сущности есть динамические атрибуты (например, разные характеристики для ноутбуков и футболок в одном магазине), их можно сложить в колонку типа JSONB. База (особенно Postgres) умеет быстро искать по таким полям, индексировать их и извлекать конкретные ключи прямо внутри SQL-запроса.

3. Извлечение данных (DQL) — С чего всё начинается

Самая частая операция в базах данных — это чтение. В SQL за это отвечает Data Query Language (DQL), а если говорить проще — команда SELECT. Запросы на чтение никак не меняют данные, поэтому писать их можно смело, ничего не сломаете (максимум — нагрузите базу, но об этом позже).

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

SELECT и FROM: выбираем всё или конкретно

Базовый синтаксис интуитивен: мы говорим базе «выбери вот это из вот этой таблицы».

SELECT title, price FROM products;

Часто, особенно на этапе отладки, хочется посмотреть вообще всё, что есть в таблице. Для этого используют звездочку:

SELECT * FROM products;

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

AS (псевдонимы): делаем вывод читаемым

Иногда названия колонок в базе исторически получились неудачными, или вы хотите сразу отдать фронтенду готовый JSON с правильными ключами. Ключевое слово AS позволяет переименовать колонку в результирующей выборке (в самой базе она не изменится).

SELECT 
    title AS product_name, 
    price AS cost 
FROM products;

Слово AS писать не обязательно (можно просто title product_name), но с ним код читается гораздо однозначнее. Позже мы увидим, что алиасы также спасают при объединении таблиц, когда нужно сократить их названия.

DISTINCT: избавляемся от дублей

Допустим, вы хотите узнать, какие вообще категории товаров есть в вашем магазине. Если написать SELECT category FROM products;, база выведет категорию для каждого товара. У вас будет список из 1000 строк с повторяющимися значениями "смартфоны" и "ноутбуки".

Чтобы получить только уникальные значения, добавляем DISTINCT:

SELECT DISTINCT category FROM products;

Теперь в ответе будет ровно столько строк, сколько уникальных категорий существует в таблице.

LIMIT и OFFSET: пагинация здорового человека

Представьте, что в таблице миллион товаров. Если выполнить SELECT без ограничений, база попытается отдать вам весь миллион. Скорее всего, ваше приложение упадет с нехваткой памяти, а пользователи будут ждать ответа вечно.

Чтобы отдавать данные порциями (как страницы в интернет-магазине), используют LIMIT (сколько строк вернуть) и OFFSET (сколько строк пропустить с начала).

Выводим первую страницу (10 товаров):

SELECT title, price FROM products LIMIT 10;

Выводим третью страницу (пропускаем первые 20, берем следующие 10):

SELECT title, price FROM products LIMIT 10 OFFSET 20;

Нюанс для тех, кто хочет писать производительно:
Связка LIMIT + OFFSET отлично работает для первых десятков страниц. Но если вы напишете OFFSET 500000, база не прыгнет магическим образом на полмиллиона строк вперед. Ей придется прочитать все эти 500 000 строк вхолостую, отбросить их и только потом отдать вам нужные 10. Это медленно. Для глубокой пагинации в высоконагруженных проектах используют другие подходы (например, Keyset Pagination — выборку по id > последнего_виденного), но для старта классического OFFSET будет вполне достаточно.

4. Фильтрация и Сортировка (WHERE & ORDER BY)

Вытягивать всю таблицу целиком или даже просто первые 10 строк подряд приходится не так часто. Обычно нас интересует что-то конкретное: пользователи с подтвержденным email, товары дешевле 1000 рублей или заказы за вчерашний день. За фильтрацию строк в SQL отвечает блок WHERE.

Операторы сравнения и логики

Здесь всё работает ровно так же, как в любом языке программирования. У нас есть базовые операторы сравнения: =, >, <, >=, <=, а также неравенство != (или классический SQL-вариант <>).

Чтобы объединить несколько условий, используем логические AND, OR и NOT.

Например, найдем все смартфоны дороже 50 000:

SELECT title, price 
FROM products 
WHERE category = 'смартфоны' AND price > 50000;

Совет из практики: если вы смешиваете AND и OR в одном запросе, всегда ставьте скобки. Без них SQL выполнит AND раньше, чем OR (как умножение выполняется раньше сложения), и вы рискуете получить совершенно непредсказуемый результат.

IN, BETWEEN, LIKE: упрощаем жизнь

SQL предлагает отличный синтаксический сахар, чтобы не писать длинные "портянки" из логических операторов.

  • IN — заменяет цепочку из OR. Если вам нужны товары из трех конкретных категорий, не пишите category = 'A' OR category = 'B' OR category = 'C'.

    WHERE category IN ('смартфоны', 'ноутбуки', 'планшеты')

  • BETWEEN — фильтрует по диапазону «от и до» (включая границы). Идеально для цен или дат.

    WHERE price BETWEEN 30000 AND 60000

  • LIKE — поиск по маске в тексте. Символ % заменяет любое количество символов (даже ноль), а _ — ровно один символ. Ищем все товары, название которых начинается на "iPhone":

    WHERE title LIKE 'iPhone%'

    Нюанс: LIKE чувствителен к регистру. В PostgreSQL для поиска без учета регистра есть специальный оператор ILIKE.

Главная ловушка SQL: обработка NULL

Это классический вопрос на собеседованиях и причина миллионов багов.
NULL в базе данных — это не ноль и не пустая строка. Это маркер «значение неизвестно» или «значение отсутствует».

Представьте, что у некоторых товаров в базе не указано описание (в колонке description лежит NULL). Если вы напишете:

WHERE description = NULL

база данных ничего вам не вернет. Почему? Потому что вы пытаетесь сравнить одно неизвестное с другим. Результатом операции NULL = NULL будет не TRUE, а всё тот же NULL (неизвестность).

Запомните железное правило: проверять на пустоту нужно только с помощью IS NULL или IS NOT NULL.

-- Правильный поиск товаров без описания
SELECT title FROM products WHERE description IS NULL;

ORDER BY: наводим порядок

В реляционных базах данных строки внутри таблиц лежат в хаотичном порядке (на самом деле в том, в котором базе было удобнее их записать на диск). Если вы не указали сортировку явно, СУБД имеет полное право каждый раз отдавать вам одни и те же данные в разном порядке.

Чтобы отсортировать результат, используем ORDER BY.

  • ASC (по умолчанию) — по возрастанию (от А до Я, от меньшего к большему).

  • DESC — по убыванию.

Сортировать можно сразу по нескольки�� колонкам. База применит их по очереди. Например, мы хотим вывести сначала самые дорогие товары, а если цена одинаковая, отсортировать их по алфавиту:

SELECT title, price, category 
FROM products 
WHERE category = 'ноутбуки'
ORDER BY price DESC, title ASC;

Важно: ORDER BY — довольно ресурсоемкая операция. Если вы сортируете миллион строк по текстовому полю без индексов, база может "задуматься" надолго. Но об индексах мы поговорим чуть позже.

5. Агрегация и Группировка: Магия чисел

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

Агрегатные функции: сворачиваем таблицу в одно число

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

  • COUNT — считает количество строк.

  • SUM — складывает значения (только для чисел).

  • AVG — считает среднее арифметическое.

  • MIN / MAX — находит минимальное и максимальное значение (работает с числами, датами и даже строками).

Например, узнаем самую высокую цену товара, среднюю цену и общее количество товаров в базе:

SELECT 
    MAX(price) AS max_price,
    AVG(price) AS average_price,
    COUNT(*) AS total_products
FROM products;

Важный нюанс с COUNT:
Есть огромная разница между COUNT(*) и COUNT(column_name).
COUNT(*) считает все строки в таблице (или в отфильтрованной выборке), независимо от того, что в них лежит.
А вот COUNT(description) посчитает только те строки, где колонка description не равна NULL. Если из 100 товаров описание есть только у 20, COUNT(description) вернет 20, а COUNT(*) — 100.

GROUP BY: превращаем сырые данные в отчеты

Считать среднюю цену «по больнице» обычно бессмысленно. Куда интереснее узнать среднюю цену в разрезе каждой категории. И вот тут на сцену выходит GROUP BY.

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

SELECT 
    category, 
    COUNT(*) AS products_count, 
    AVG(price) AS avg_price
FROM products
GROUP BY category;

Железное правило SQL, о которое спотыкаются все новички:
Если в SELECT у вас есть агрегатная функция (например, AVG) и обычная колонка (например, category), то эта обычная колонка обязана быть указана в GROUP BY. Вы не можете попросить базу вывести «название товара и среднюю цену по всем товарам» в одной строке без оконных функций (до них мы еще дойдем).

HAVING: фильтрация после группировки

А теперь классический вопрос с любого собеседования: «В чем разница между WHERE и HAVING?».

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

-- ОШИБКА! Так работать не будет
SELECT category, COUNT(*) 
FROM products 
WHERE COUNT(*) > 10 
GROUP BY category;

База выдаст ошибку. Почему? Дело в порядке выполнения. WHERE фильтрует конкретные строки до того, как они будут сгруппированы. На этапе WHERE база еще ничего не посчитала, она просто не знает результат COUNT(*).

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

-- Правильно:
SELECT category, COUNT(*) AS products_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

Можно ли использовать их вместе? Конечно. Часто так и делают, чтобы не делать лишнюю работу: сначала отсекают ненужные строки с помощью WHERE (чтобы базе было легче считать), а затем фильтруют результаты подсчетов с помощью HAVING.

Например: найдем категории, в которых больше 5 активных товаров:

SELECT category, COUNT(*) 
FROM products 
WHERE status = 'active' -- 1. Отбросили все неактивные товары
GROUP BY category       -- 2. Сгруппировали то, что осталось
HAVING COUNT(*) > 5;    -- 3. Оставили только крупные категории

6. Объединение таблиц (JOIN-ы) — Самая важная часть

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

В правильной базе данных информация не дублируется. Мы не пишем имя и email клиента в каждой строке его заказа — мы храним там только user_id. Но бизнесу (или фронтенду) не нужен безликий user_id = 42, им нужен чек, где написано: «Иван Иванов купил Ноутбук за 100 000». Чтобы собрать эти данные воедино, таблицы нужно «склеить». Для этого придумали JOIN.

Обычно JOIN-ы объясняют через диаграммы Венна (пересекающиеся круги). Представьте два круга: левый — таблица users (клиенты), правый — orders (их заказы).

INNER JOIN: только строгие совпадения

Это пересечение двух кругов. INNER JOIN (слово INNER часто опускают и пишут просто JOIN) берет строку из первой таблицы, ищет совпадение во второй по указанному правилу (обычно это равенство ключей) и, если находит, склеивает их.

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

SELECT users.name, orders.amount 
FROM users
JOIN orders ON users.id = orders.user_id;

Важно: Обратите внимание на блок ON. Это клей. Именно здесь мы объясняем базе, по какому принципу строки из левой таблицы соотносятся со строками из правой.

LEFT JOIN: самый частый гость в продакшене

В 80% случаев на работе вы будете писать именно LEFT JOIN.
Визуально это весь левый круг целиком плюс тот кусочек правого, который с ним пересекается.

Суть: мы берем все строки из левой таблицы (users), независимо от того, есть ли для них пара в правой (orders). Если пара есть — данные склеиваются. Если пользователь ничего не покупал, база всё равно выведет его имя, а на месте данных о заказе (сумма, дата) просто поставит NULL.

Это идеальный инструмент, чтобы найти пользователей, которые не совершили целевое действие:

SELECT users.name, orders.amount 
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL; -- Отсекаем тех, у кого есть заказы

RIGHT JOIN и FULL OUTER JOIN (коротко)

  • RIGHT JOIN работает абсолютно так же, как LEFT JOIN, только базой выступает правая таблица. На практике его используют крайне редко, потому что читать запрос удобнее сверху вниз (слева направо). Проще поменять таблицы местами в коде и написать привычный LEFT JOIN.

  • FULL OUTER JOIN — это объединение вообще всего. База выгрузит всех пользователей (с заказами и без) и все заказы (с пользователями и без). Там, где пары нет, будут подставлены NULL. Это тяжелая операция, которая чаще всего нужна дата-инженерам для сверки данных (реконсиляции) между разными системами.

SELF JOIN: когда таблица ссылается на саму себя

Иногда JOIN может сломать мозг новичку, особенно когда таблица склеивается сама с собой. Зачем это нужно?

Классический пример — иерархия сотрудников. У в��с есть таблица employees, где лежат все: и рядовые разработчики, и тимлиды, и CTO. У каждого есть id, name и manager_id (который ссылается на id начальника из этой же таблицы).

Как вывести список в формате «Сотрудник — Его руководитель»? Сделать JOIN таблицы саму на себя, дав им разные псевдонимы (алиасы), чтобы база не запуталась:

SELECT 
    emp.name AS employee_name, 
    boss.name AS manager_name
FROM employees AS emp
LEFT JOIN employees AS boss ON emp.manager_id = boss.id;

Мы используем LEFT JOIN, потому что у самого главного босса (CTO) manager_id будет пустым, и обычный INNER JOIN просто выкинул бы его из отчета.

7. Изменение данных и схемы (DML & DDL)

До сих пор мы только читали данные. Это безопасно: SELECT ничего не сломает, даже если вы напишете его криво (ну, кроме нервов девопса, который увидит загрузку процессора на сервере БД в 100%).

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

  • DML (Data Manipulation Language) — работа с самими данными (строками).

  • DDL (Data Definition Language) — работа со структурой (таблицами, колонками).

INSERT INTO: добавляем записи

Чтобы в таблице появилось что-то новое, используется команда INSERT INTO. Синтаксис простой: говорим, в какую таблицу вставляем, перечисляем колонки и передаем значения.

INSERT INTO users (name, email, age) 
VALUES ('Алексей', 'alex@example.com', 28);

Хорошая практика: всегда явно перечисляйте колонки в первых скобках. В SQL можно написать просто INSERT INTO users VALUES ('Алексей', ...);, но если завтра кто-то добавит в таблицу новую колонку (например, phone), ваш запрос сломается, так как количество переданных значений перестанет совпадать с количеством колонок в таблице.

Если нужно вставить сразу несколько строк, не пишите 10 отдельных INSERT-ов. База работает гораздо быстрее, если передать данные "пачкой" (batch insert):

INSERT INTO users (name, email) VALUES 
('Иван', 'ivan@test.com'),
('Анна', 'anna@test.com'),
('Петр', 'petr@test.com');

UPDATE + WHERE: почему забытый WHERE — это седые волосы админа

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

UPDATE products 
SET price = 65000, updated_at = NOW() 
WHERE id = 42;

А теперь главное правило, написанное кровью и слезами тысяч разработчиков: никогда не пишите UPDATE без WHERE.

Если вы выполните запрос:

UPDATE products SET price = 0;

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

Лайфхак: прежде чем выполнить сложный UPDATE или DELETE на "живой" базе, напишите сначала SELECT с тем же условием WHERE. Убедитесь, что база нашла именно те 3 строки, которые вы хотите изменить, а не 3 миллиона. И только потом меняйте слово SELECT ... на UPDATE ... SET ....

DELETE vs TRUNCATE: в чем разница

Обе команды удаляют данные, но делают это принципиально по-разному.

  • DELETE — удаляет строки поштучно. Вы можете (и должны!) использовать WHERE, чтобы удалить что-то конкретное. База записывает удаление каждой строки в свой внутренний журнал (логи). Это работает медленно на больших объемах, но зато операцию можно отменить, если она выполняется внутри транзакции.

    DELETE FROM users WHERE last_login < '2020-01-01';

  • TRUNCATE — это бульдозер. Команда мгновенно сносит все строки в таблице. Она не читает их по одной и не пишет подробные логи, поэтому работает за доли секунды даже на таблицах в десятки гигабайт. Использовать WHERE с ней нельзя. А еще она сбрасывает счетчик автоинкремента (колонку id) обратно в единицу.

    TRUNCATE TABLE logs;

CREATE, ALTER, DROP: управляем схемой (DDL)

Если данные — это вода, то структура базы — это трубы. DDL-команды прокладывают эти трубы. Обычно разработчики не пишут их руками каждый день, для этого существуют системы миграций (Flyway, Liquibase, Alembic), но знать синтаксис нужно.

  • CREATE TABLE — создание таблицы с указанием типов данных и ограничений (констрейнтов).

    CREATE TABLE orders (
    id SERIAL PRIMARY KEY, -- SERIAL в Postgres сам делает +1 для новых строк
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

    ALTER TABLE orders ADD COLUMN promo_code VARCHAR(50);

  • DROP TABLE — уничтожение таблицы вместе со всеми данными, индексами и структурой. Безвозвратно. Команда DROP TABLE orders; сделает так, что база вообще забудет о существовании сущности "заказы". Применять с максимальной осторожностью.

8. Прод��инутый уровень (краткий обзор)

Базовых SELECT, JOIN и GROUP BY хватит, чтобы закрыть 80% повседневных задач бэкендера или продакта. Но оставшиеся 20% потребуют инструментов посерьезнее. Когда логика усложняется, писать запросы «в лоб» становится больно.

Подзапросы: вложенные SELECT

Подзапрос — это запрос внутри другого запроса. Звучит как начало фильма «Начало», и на практике выглядит примерно так же. Подзапросы нужны, когда для фильтрации или вычислений вам сначала нужно получить какие-то промежуточные данные.

Например, мы хотим найти всех сотрудников, чья зарплата выше средней по компании. Мы не можем просто написать WHERE salary > AVG(salary) (вспоминаем про порядок выполнения и HAVING). Нам нужно сначала вычислить среднюю зарплату, а потом сравнить с ней:

SELECT name, salary 
FROM employees 
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Всё отлично работает. Но у подзапросов есть проблема: когда их становится три-четыре штуки, код превращается в нечитаемую «матрешку». Вы пытаетесь понять логику, прыгая глазами из середины запроса наружу. Чтобы этого избежать, придумали CTE.

CTE (WITH): как сделать сложные запросы читаемыми

CTE (Common Table Expressions) — это, по сути, рефакторинг для SQL. Конструкция WITH позволяет вынести сложный подзапрос наверх, дать ему понятное имя и обращаться к нему дальше как к обычной таблице.

Перепишем запрос со сложной логикой. Допустим, мы хотим найти топ-клиентов (кто потратил больше 100k) и посмотреть, сколько из них из Москвы:

WITH top_clients AS (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 100000
)
SELECT users.name, top_clients.total_spent
FROM users
JOIN top_clients ON users.id = top_clients.user_id
WHERE users.city = 'Москва';

Код читается сверху вниз, как обычный текст. Сначала мы собрали временную табличку top_clients, а затем просто приджоинили её к основной. CTE не сохраняются на диск, они существуют только в момент выполнения запроса.

Оконные функции: магия аналитики

Если JOIN — это сердце реляционных баз, то оконные функции — это их мозг.

Вспомним GROUP BY: он схлопывает кучу строк в одну. Если вы сгруппировали товары по категориям, чтобы узнать максимальную цену, вы потеряете названия самих товаров (база выведет только category и max_price). Оконные функции делают то же самое (считают агрегации по группе), но не схлопывают строки. Они вычисляют результат и дописывают его в новую колонку к каждой исходной строке.

Разберем классическую задачу с собеседований уровня Middle: «Выведите топ-3 самых высокооплачиваемых сотрудников в каждом отделе».

Решить это обычным GROUP BY почти невозможно. Зато с помощью «окон» и CTE — легко. Нам нужно пронумеровать сотрудников внутри каждого отдела по убыванию зарплаты.

Для этого есть функции ROW_NUMBER() (просто порядковый номер) и RANK() (присваивает одинаковый ранг, если зарплаты равны).

WITH RankedSalaries AS (
    SELECT 
        department,
        name AS employee_name,
        salary,
        -- Магия начинается здесь:
        RANK() OVER(
            PARTITION BY department -- "Окно": бьем данные по отделам
            ORDER BY salary DESC    -- Сортируем по зарплате внутри отдела
        ) AS rank_in_dept
    FROM employees
)
-- Теперь просто отбираем тех, чей ранг 1, 2 или 3
SELECT department, employee_name, salary
FROM RankedSalaries
WHERE rank_in_dept <= 3;

Как это работает:

  1. OVER(...) говорит базе, что мы используем оконную функцию.

  2. PARTITION BY department изолирует расчеты. База нумерует сотрудников IT-отдела (1, 2, 3...), затем переходит в отдел продаж и начинает нумерацию заново (1, 2, 3...).

  3. Обернув это в CTE (RankedSalaries), мы можем легко отфильтровать результат через WHERE rank_in_dept <= 3.

Оконных функций много: они умеют заглядывать в предыдущую или следующую строку (LAG / LEAD), считать скользящие средние и нарастающие итоги. Если вы освоите этот инструмент, вы сможете решать средствами базы данных такие задачи, на которые в коде приложения ушли бы сотни строк со сложными циклами.

9. Подводные камни и порядок выполнения запроса

Если бы меня попросили оставить в этой статье только один раздел, я бы выбрал этот. Понимание того, как база данных на самом деле читает ваш код, снимает 90% вопросов в духе «почему эта строчка не работает?!».

Секретный ингредиент: Логический порядок выполнения запроса

Мы, люди, читаем и пишем SQL-запросы сверху вниз: сначала SELECT, потом FROM, затем WHERE. Нам так удобно: мы сразу заявляем, что хотим получить, а потом уточняем детали.

Но база данных мыслит иначе. Ей абсолютно всё равно, что вы там написали в SELECT, пока она не поймет, откуда вообще брать данные и как их фильтровать. Поэтому СУБД выполняет запрос в другом, строго заданном порядке:

  1. FROM и JOIN — база идет на диск (или в память) и находит нужные таблицы, склеивая их между собой.

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

  3. GROUP BY — оставшиеся строки разбиваются на группы.

  4. HAVING — отсекаются лишние группы.

  5. SELECT — и только сейчас база смотрит, какие конкретно колонки вы просили вывести, вычисляет математику и применяет псевдонимы (AS).

  6. ORDER BY — итоговый результат сортируется.

  7. LIMIT / OFFSET — база отрезает нужный кусок для пагинации и отдает его вам.

Почему это так важно знать?
Потому что это объясняет классическую ошибку новичка. Допустим, вы написали сложную формулу в SELECT и дали ей понятное имя:

SELECT price * discount AS final_price
FROM products
WHERE final_price > 1000; -- ОШИБКА!

База выругается, что колонки final_price не существует. Почему? Смотрим на порядок выполнения: шаг WHERE (фильтрация) идет до шага SELECT. Когда база фильтрует строки, она еще не вычислила final_price и не знает такого псевдонима. Придется дублировать формулу в WHERE или использовать подзапрос/CTE.

А вот в ORDER BY этот псевдоним использовать можно, потому что сортировка происходит после SELECT.

Индексы и почему SELECT * — это зло

Мы уже упоминали, что писать SELECT * в продакшен-коде — дурной тон из-за перегрузки сети. Но есть и куда более веская причина, скрытая внутри архитектуры баз данных. Она называется покрывающие индексы (Covering Indexes).

Представьте, что вы ищете пользователей со статусом «VIP». Если таблица огромная, база будет сканировать её целиком (Sequential Scan), что очень долго. Чтобы ускорить поиск, администратор БД создает индекс. Грубо говоря, это отдельная маленькая структура данных (обычно B-дерево), где лежат только отсортированные статусы и ссылки на то, где на жестком диске лежат полные строки этих пользователей.

Теперь следите за руками:
Если вы напишете SELECT id, status FROM users WHERE status = 'VIP';, база заглянет в индекс, быстро найдет нужные записи и... поймет, что ей вообще не нужно идти в основную таблицу. Все данные, которые вы запросили (id и status), уже есть в самом индексе. Это называется Index Only Scan, и это работает молниеносно.

А теперь вы пишете:

SELECT * FROM users WHERE status = 'VIP';

База точно так же найдет нужные id в индексе. Но из-за звездочки вы потребовали отдать вам email, address, bio и дату регистрации. Этих данных в индексе нет. Базе придется по каждой найденной ссылке лезть в основную таблицу на жестком диске (Heap Fetch). Если VIP-пользователей много, эти хаотичные прыжки по диску могут работать даже медленнее, чем если бы база просто прочитала всю таблицу целиком за один проход.

Мораль: всегда запрашивайте только те колонки, которые вам реально нужны в коде. Это не прихоть линтера, это физика работы баз данных.

10. Заключение и чек-лист

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

Поэтому закрывайте Хабр и идите писать код.

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

SQL — это, пожалуй, самая надежная инвестиция вашего времени. Фреймворки приходят и уходят, а базы данных остаются. Успешных вам запросов, и пусть они никогда не ложатся в Slow Query Log!