Раздел 9 — SQL: выборка и фильтрация данных

Основной инструмент работы с базой: как получить именно те строки, которые нужны

Прогресс курса Раздел 9 из 13

Что вы освоите в этом разделе

Строить список выборки с выражениями и псевдонимами
Фильтровать данные всеми доступными операторами
Искать по тексту через LIKE, ILIKE и регулярные выражения
Сортировать с учётом NULL и русской локали
Реализовывать пагинацию, работающую на больших таблицах
Применять операции над множествами
Писать условную логику через CASE
Понимать логический порядок выполнения запроса и объяснять его следствия

6 академических часов: 1 час теории, 5 часов практики. Все примеры — на учебной базе «Социальная сеть».

SELECT: что возвращать

-- Конкретные столбцы — так и надо писать в коде приложения
SELECT name, city FROM users;

-- Выражения и псевдонимы
SELECT name,
       age,
       2026 - age              AS birth_year,
       upper(city)             AS city_upper,
       name || ' (' || city || ')' AS label
FROM   users;

-- Уникальные значения
SELECT DISTINCT city FROM users;

-- Уникальность по части столбцов: одна строка на город, с наибольшим возрастом
SELECT DISTINCT ON (city) city, name, age
FROM   users
ORDER BY city, age DESC;

SELECT * допустим при изучении данных и недопустим в коде приложения. Причины из раздела 1: он ломает логическую независимость — добавление столбца меняет форму ответа; он тянет по сети данные, которые не нужны; он мешает работать Index Only Scan. Псевдоним AS формально необязателен, но без него запрос читается хуже.

DISTINCT ON — расширение PostgreSQL, которого нет в стандарте. Оно решает частую задачу «по одной, самой свежей записи на каждого» короче, чем оконные функции. Требование: первые выражения в ORDER BY должны совпадать со списком в DISTINCT ON.

WHERE: отбор строк

-- Сравнение
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE city <> 'Moscow';

-- Диапазон: границы включаются
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- Список значений
SELECT * FROM users WHERE city IN ('Moscow', 'Kazan');

-- Отсутствие значения — только через IS
SELECT * FROM users WHERE city IS NULL;

-- Логические операторы; AND сильнее OR, скобки обязательны при смешении
SELECT * FROM users
WHERE  (city = 'Moscow' OR city = 'Kazan')
  AND  age > 20;

AND имеет более высокий приоритет, чем OR. Запрос WHERE a = 1 OR a = 2 AND b = 3 означает a = 1 OR (a = 2 AND b = 3) — почти наверняка не то, что имелось в виду. Ставьте скобки всегда, когда в условии есть и AND, и OR: они ничего не стоят и снимают вопрос.

Поиск по тексту

-- LIKE: % — любое число символов, _ — ровно один
SELECT title FROM posts WHERE title LIKE 'tutorial%';   -- начинается с
SELECT title FROM posts WHERE title LIKE '%2026';       -- заканчивается на
SELECT title FROM posts WHERE title LIKE '%review%';    -- содержит

-- ILIKE: без учёта регистра (расширение PostgreSQL)
SELECT name FROM users WHERE name ILIKE 'anna';

-- Регулярные выражения: ~ с учётом регистра, ~* без учёта
SELECT title FROM posts WHERE title ~ '^(tutorial|guide)';
SELECT name  FROM users WHERE name !~* '^a';   -- НЕ начинается на a

Обычный индекс работает только для шаблона с якорем в начале: LIKE 'tutorial%' проиндексируется, LIKE '%review%' — нет, и на большой таблице это полное сканирование. Для поиска подстроки в середине нужен триграммный индекс (pg_trgm), а для поиска по словам — полнотекстовый (to_tsvector + GIN). Об индексах — раздел 11.

ORDER BY: сортировка

-- Направление и несколько ключей
SELECT name, city, age FROM users
ORDER BY city ASC, age DESC;

-- Сортировка по выражению и по псевдониму
SELECT title, score * 1.1 AS boosted FROM posts
ORDER BY boosted DESC;

-- Куда девать NULL
SELECT name, city FROM users ORDER BY city NULLS FIRST;
SELECT name, city FROM users ORDER BY city DESC NULLS LAST;

По умолчанию NULL считается наибольшим значением: при ASC он в конце, при DESC — в начале. Это удивляет, потому что интуитивно «пусто» ощущается как «меньше всего». Если порядок важен для отчёта — задавайте NULLS FIRST или NULLS LAST явно.

Без ORDER BY порядок строк не определён — это прямое следствие свойств отношения из раздела 2. Сегодня запрос возвращает строки «по порядку», завтра после VACUUM или изменения плана порядок другой. Полагаться на «естественный порядок» нельзя никогда.

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

-- Явно указать правило сортировки для одного запроса
SELECT name FROM users ORDER BY name COLLATE "ru_RU.utf8";

-- Проверить, какая локаль у базы
SELECT datname, datcollate, datctype FROM pg_database;

LIMIT, OFFSET и пагинация

-- Первые 5 постов по убыванию рейтинга
SELECT title, score FROM posts
ORDER BY score DESC
LIMIT 5;

-- Третья страница по 5 записей
SELECT title, score FROM posts
ORDER BY score DESC, id
LIMIT 5 OFFSET 10;

У пагинации через OFFSET два дефекта. Первый — скорость: чтобы отдать строки с 100 000-й, сервер обязан прочитать и отбросить сто тысяч предыдущих. Последние страницы каталога работают в сотни раз медленнее первых. Второй — пропуски: если между запросами страниц кто-то вставит или удалит строку, часть записей пользователь увидит дважды, а часть не увидит вовсе.

Решение — пагинация по ключу: вместо номера строки запоминается позиция последней показанной записи.

-- Первая страница
SELECT id, title, score FROM posts
ORDER BY score DESC, id DESC
LIMIT 5;

-- Следующая: продолжаем с последней увиденной пары (score, id)
SELECT id, title, score FROM posts
WHERE  (score, id) < (900, 8)     -- сравнение кортежей
ORDER BY score DESC, id DESC
LIMIT 5;

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

Операции над множествами

Прямая реализация операций реляционной алгебры из раздела 2. Требование одно: одинаковое число столбцов и совместимые типы.

-- Объединение с удалением дубликатов
SELECT user_id FROM likes
UNION
SELECT user_id FROM views;

-- Объединение без удаления дубликатов — быстрее
SELECT user_id FROM likes
UNION ALL
SELECT user_id FROM views;

-- Разность: кто просматривал ленты, но ни разу не лайкал
SELECT user_id FROM views
EXCEPT
SELECT user_id FROM likes;

-- Пересечение: и лайкал, и просматривал
SELECT user_id FROM likes
INTERSECT
SELECT user_id FROM views;

-- ORDER BY относится ко всему результату и пишется в конце
SELECT user_id FROM likes
UNION
SELECT user_id FROM views
ORDER BY user_id;

Операция	Что даёт	Удаляет дубликаты
`UNION`	строки обоих запросов	да
`UNION ALL`	строки обоих запросов	нет
`EXCEPT`	строки первого без строк второго	да
`INTERSECT`	строки, входящие в оба	да

UNION для удаления дубликатов выполняет сортировку или построение хеш-таблицы — на больших объёмах это заметная работа. Если дубликаты невозможны по смыслу данных или не мешают, берите UNION ALL. Разница в производительности бывает кратной.

Условные выражения

-- CASE: возрастные категории
SELECT name, age,
       CASE
           WHEN age < 18 THEN 'несовершеннолетний'
           WHEN age < 25 THEN 'молодёжь'
           WHEN age < 45 THEN 'основная аудитория'
           ELSE              'старшая аудитория'
       END AS age_group
FROM   users;

-- Короткая форма — сравнение с одним выражением
SELECT name,
       CASE gender WHEN 'male' THEN 'М' WHEN 'female' THEN 'Ж' ELSE '—' END AS g
FROM   users;

-- Работа с NULL
SELECT name,
       coalesce(city, 'не указан')  AS city,      -- первое не-NULL
       nullif(city, '')             AS city_norm  -- пустую строку → NULL
FROM   users;

-- CASE внутри агрегата — приём для сводных таблиц (раздел 10)
SELECT city,
       count(*) AS total,
       count(*) FILTER (WHERE gender = 'male')   AS males,
       count(*) FILTER (WHERE gender = 'female') AS females
FROM   users
GROUP BY city;

CASE вычисляет условия по порядку и останавливается на первом истинном — как цепочка if / else if. Без ветви ELSE невыполнение всех условий даёт NULL, и это частая причина неожиданных пустот в отчёте. Конструкция FILTER — более читаемая замена приёму count(CASE WHEN ... THEN 1 END).

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

Запрос пишется в одном порядке, а выполняется в другом. Понимание этого снимает большинство вопросов вида «почему так нельзя».

Порядок написания        Порядок выполнения
─────────────────        ──────────────────
SELECT      ──┐      1.  FROM      источник данных
FROM        ──┼──▶   2.  WHERE     отбор строк
WHERE       ──┤      3.  GROUP BY  группировка
GROUP BY    ──┤      4.  HAVING    отбор групп
HAVING      ──┤      5.  SELECT    вычисление выражений, псевдонимы
ORDER BY    ──┤      6.  DISTINCT  удаление дубликатов
LIMIT       ──┘      7.  ORDER BY  сортировка
                     8.  LIMIT     ограничение

Три следствия, каждое из которых экономит время на отладке.

Что из этого следует

Псевдоним из SELECT недоступен в WHERE. На момент фильтрации он ещё не вычислен. В ORDER BY — доступен, потому что сортировка идёт после.
WHERE фильтрует строки, HAVING — группы. WHERE работает до группировки и агрегат в нём использовать нельзя.
LIMIT применяется последним. Сервер всё равно обработает и отсортирует весь набор — LIMIT не делает тяжёлый запрос лёгким.

-- Ошибка: псевдоним в WHERE
SELECT name, 2026 - age AS birth_year
FROM   users
WHERE  birth_year > 2000;
-- ОШИБКА: column "birth_year" does not exist

-- Верно: повторить выражение
SELECT name, 2026 - age AS birth_year
FROM   users
WHERE  2026 - age > 2000;

-- Или вынести во вложенный запрос либо CTE
SELECT * FROM (
    SELECT name, 2026 - age AS birth_year FROM users
) t
WHERE  t.birth_year > 2000;

-- А в ORDER BY псевдоним работает
SELECT name, 2026 - age AS birth_year
FROM   users
ORDER BY birth_year DESC;

Это логический порядок — модель, объясняющая семантику. Физически планировщик волен делать что угодно, лишь бы результат совпадал: он проталкивает условия внутрь подзапросов, меняет порядок соединений, применяет LIMIT раньше, если это безопасно. Реальный план показывает EXPLAIN — раздел 11.

Декартово произведение

Простейший способ соединить две таблицы — взять все возможные пары строк. Это операция × реляционной алгебры.

-- Явная форма
SELECT u.name, c.name
FROM   users u CROSS JOIN communities c;

-- Неявная: тот же результат
SELECT u.name, c.name FROM users u, communities c;

-- 10 пользователей × 6 сообществ = 60 строк
SELECT count(*) FROM users, communities;

Забытое условие связи при перечислении таблиц через запятую даёт декартово произведение. На учебной базе это 60 строк, на боевой — миллион на миллион, то есть триллион. Запрос не вернётся никогда, а сервер займёт весь диск временными файлами. Признак ошибки — результат заметно больше ожидаемого. Осмысленные применения CROSS JOIN есть — например, построить сетку «все даты × все товары» для отчёта без пропусков, — но они всегда намеренные.

Соединение с условием — то есть JOIN — тема следующего раздела.

Ключевые выводы раздела

Запомните главное

SELECT * — только для изучения данных, не для кода приложения
AND приоритетнее OR: при смешении ставьте скобки
LIKE '%текст%' не использует обычный индекс — это полное сканирование
Без ORDER BY порядок строк не гарантирован
NULL при сортировке считается наибольшим; управляется NULLS FIRST / NULLS LAST
Пагинация через OFFSET медленна на глубине, а при вставках и удалениях показывает строки дважды или теряет их; надёжнее пагинация по ключу
UNION ALL быстрее UNION, если дубликаты не мешают
CASE без ELSE даёт NULL — частая причина пустот в отчётах
Псевдоним из SELECT доступен в ORDER BY, но не в WHERE
Перечисление таблиц через запятую без условия связи — декартово произведение

Практические задания — на учебном портале. Задания, критерии оценивания, сдача работ и оценки преподавателя — в курсе на portal.nevabit.ru. Учётную запись выдаёт преподаватель.

Раздел 8: DML Раздел 10: JOIN и группировка