LIMIT и OFFSET: ограничение и пагинация

В предыдущем уроке вы научились сортировать результат с помощью ORDER BY. Теперь добавим последний штрих: ограничение количества строк. Представьте таблицу с миллионом заказов — вы не хотите получать все миллион в одном запросе. Нужно только 10 самых свежих, или 20 товаров для первой страницы каталога. Для этого используются LIMIT и OFFSET.

Синтаксис LIMIT

LIMIT ставится в конец запроса и указывает максимальное число строк в результате:

SELECT колонки
FROM   таблица
WHERE  условие
ORDER BY колонка
LIMIT  N;

Простой пример — топ-3 самых высокооплачиваемых сотрудника:

SELECT name, salary
FROM   employees
ORDER BY salary DESC
LIMIT  3;

Результат:

name   | salary
-------+--------
Борис  | 110000
Дина   | 95000
Анна   | 90000

Из 5 сотрудников вернулись только трое — с наибольшими зарплатами. Важно: LIMIT применяется после ORDER BY. Поэтому логика правильная: сначала сортируем по убыванию, потом берём первые три строки.

LIMIT без ORDER BY — опасно

Технически LIMIT работает и без ORDER BY:

SELECT name FROM employees LIMIT 3;

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

Правило: LIMIT всегда используйте с ORDER BY, если нужен детерминированный (предсказуемый) результат. Иначе вы получаете «случайную выборку».

Единственное исключение — когда порядок действительно не важен и нужно просто «взять любые N строк» для быстрой проверки данных:

SELECT * FROM products LIMIT 5;   -- посмотреть на структуру данных

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

OFFSET: пропустить N строк

OFFSET указывает, сколько строк пропустить перед тем, как начать возвращать результат:

SELECT name, salary
FROM   employees
ORDER BY salary DESC
LIMIT  2
OFFSET 2;

Логика: отсортировать по убыванию зарплаты, пропустить первые 2 строки, вернуть следующие 2.

Полная отсортированная таблица:

Борис — 110000
Дина — 95000
Анна — 90000 ← OFFSET 2 начинает здесь
Вера — 85000
Гриша — 75000

Результат запроса: Анна (90000) и Вера (85000).

Пагинация: страницы данных

LIMIT и OFFSET вместе реализуют пагинацию — разбивку большого результата на страницы. Это стандартный паттерн для любого списка: товары в каталоге, посты в блоге, сотрудники в HR-системе.

Формула для N-й страницы при размере страницы page_size:

OFFSET = (page_number - 1) * page_size
LIMIT  = page_size

Пример: размер страницы 10, вывести страницу 1, 2, 3:

-- Страница 1 (строки 1-10)
SELECT name, price FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Страница 2 (строки 11-20)
SELECT name, price FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- Страница 3 (строки 21-30)
SELECT name, price FROM products ORDER BY id LIMIT 10 OFFSET 20;

В реальном приложении page_number передаётся как параметр из URL (например, /products?page=2), и бэкенд подставляет вычисленный OFFSET в запрос.

Проверь себя: при размере страницы 5, какой OFFSET нужен для страницы 4?

Топ-N запросы

Помимо пагинации, LIMIT используется для классических «топ-N» запросов:

-- Топ-5 самых продаваемых товаров
SELECT product_name, total_sold
FROM   sales_summary
ORDER BY total_sold DESC
LIMIT  5;

-- Последние 10 заказов
SELECT order_id, created_at, total_amount
FROM   orders
ORDER BY created_at DESC
LIMIT  10;

-- Один любой заказ конкретного пользователя (проверить наличие)
SELECT order_id
FROM   orders
WHERE  user_id = 42
ORDER BY created_at DESC
LIMIT  1;

Последний пример — распространённый паттерн: когда нужно проверить «есть ли хоть один заказ» или «какой последний заказ», берут LIMIT 1.

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

У пагинации через OFFSET есть известная проблема производительности. СУБД не может «перепрыгнуть» к нужному месту в данных — она должна пройти все OFFSET строк и отбросить их. На большой странице (OFFSET 100000) это означает обработку 100000 строк только для того, чтобы показать 10.

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

Профессиональное решение — «cursor-based pagination» (пагинация по курсору), где вместо OFFSET используется условие WHERE id > last_id. Но это более продвинутая техника, выходящая за пределы этого урока.

LIMIT со значением 0 и NULL

Специальные случаи:

LIMIT 0    -- вернёт 0 строк (только структуру без данных)
LIMIT ALL  -- снять ограничение, вернуть все строки (эквивалент без LIMIT)

LIMIT 0 иногда полезен для проверки структуры таблицы без загрузки данных. LIMIT ALL — редко нужен, но существует как явная форма записи.

LIMIT в разных СУБД

LIMIT + OFFSET — синтаксис PostgreSQL, SQLite и MySQL. В Microsoft SQL Server используется другая конструкция:

-- SQL Server: TOP вместо LIMIT
SELECT TOP 10 name, salary FROM employees ORDER BY salary DESC;

-- SQL Server: OFFSET-FETCH для пагинации
SELECT name, salary
FROM   employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Oracle использует ROWNUM или FETCH FIRST N ROWS ONLY. Это важно знать, если придётся работать с разными СУБД. В рамках этого курса мы работаем с PostgreSQL/SQLite, где LIMIT + OFFSET работают отлично.

Полная структура запроса SELECT

Теперь мы изучили все базовые клаузы. Полная структура запроса:

SELECT name, salary * 12 AS annual
FROM   employees
WHERE  department = 'IT' AND salary > 60000
ORDER BY annual DESC
LIMIT  10
OFFSET 0;

Порядок клауз фиксирован. Запомните его как мантру:

SELECT → FROM → WHERE → ORDER BY → LIMIT → OFFSET

Типичные ошибки

1. LIMIT до ORDER BY:

SELECT * FROM products LIMIT 5 ORDER BY price;  -- ошибка синтаксиса

ORDER BY идёт до LIMIT.

2. LIMIT без ORDER BY в боевом коде:

SELECT * FROM products LIMIT 10;  -- 10 «случайных» строк

Для пагинации всегда нужен ORDER BY.

3. Пагинация начинается с 0:

-- Первая страница: OFFSET 0 (не OFFSET 1)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

Пример: новостная лента с пагинацией

Рассмотрим, как пагинация работает в реальном приложении — блоге с постами:

-- Показать 5 постов на странице 1 (самые свежие)
SELECT title, author, published_at
FROM   posts
WHERE  status = 'published'
ORDER BY published_at DESC
LIMIT  5
OFFSET 0;

-- Страница 2 — следующие 5 постов
SELECT title, author, published_at
FROM   posts
WHERE  status = 'published'
ORDER BY published_at DESC
LIMIT  5
OFFSET 5;

Обратите внимание: WHERE status = 'published' фильтрует черновики, ORDER BY published_at DESC ставит новые посты первыми, а LIMIT 5 OFFSET N реализует постраничную навигацию.

Когда OFFSET стоит заменить на условие WHERE

Для больших таблиц вместо OFFSET 10000 эффективнее использовать условие WHERE с последним известным значением:

-- Вместо (медленно на большом OFFSET)
SELECT id, title FROM posts ORDER BY id ASC LIMIT 20 OFFSET 10000;

-- Лучше (если знаем последний id предыдущей страницы = 10045)
SELECT id, title FROM posts WHERE id > 10045 ORDER BY id ASC LIMIT 20;

Второй вариант работает значительно быстрее, потому что PostgreSQL может использовать индекс по id и перейти сразу к нужному месту без сканирования 10000 строк. Это «cursor pagination» — важный паттерн для высоконагруженных систем. Пока запомните, что OFFSET — удобный, но не самый быстрый инструмент для глубокой пагинации.

Краткий итог

LIMIT N ограничивает результат первыми N строками
OFFSET M пропускает первые M строк
Пагинация: страница P при размере page_size = LIMIT page_size OFFSET (P-1)*page_size
LIMIT без ORDER BY даёт непредсказуемый результат
OFFSET может быть медленным на больших значениях; для глубокой пагинации рассматривайте cursor pagination
Синтаксис LIMIT/OFFSET работает в PostgreSQL и SQLite; SQL Server использует другой синтаксис

Что дальше

Вы освоили LIMIT и OFFSET. Следующий урок — про приоритет операторов и скобки: почему a OR b AND c — не то же самое, что (a OR b) AND c, и как избежать логических ловушек в сложных условиях WHERE.

LIMIT и OFFSET: ограничение и пагинация