SQL LIMIT и OFFSET: первые N строк и пагинация

Урок 2.7 · Время чтения: ~5 мин · Задачи для практики ↓

Предложение LIMIT в SQL ограничивает количество строк, которые возвращает запрос, а OFFSET пропускает заданное число строк перед выводом остальных. В этом уроке вы узнаете, как получить первые N строк, как построить постраничный вывод, почему LIMIT нужен ORDER BY и как то же самое записывается в других СУБД.

В таблицах часто тысячи и миллионы строк, и все сразу нужны редко. Отчёту нужны первые 10 позиций, приложение показывает по 20 результатов на странице. LIMIT и OFFSET возвращают ровно ту часть результата, которая нужна.

Предложение LIMIT

LIMIT задаёт максимальное число строк в результате. Оно записывается в конце запроса, после ORDER BY.

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

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT row_count;

SELECT title, length
FROM film
ORDER BY length DESC
LIMIT 5;

Результат: 5 самых длинных фильмов.

Почему LIMIT нужен ORDER BY

Без ORDER BY база данных возвращает строки в любом удобном ей порядке, поэтому «первые 5 строк» каждый раз могут оказаться разными. Всегда сортируйте, прежде чем ограничивать.

Сортировка должна быть ещё и однозначной. В запросе выше у нескольких фильмов одинаковая длительность - 185 минут, и какие из них попадут в первую пятёрку, решает база данных. Добавьте столбец, который разрешает совпадения:

SELECT title, length
FROM film
ORDER BY length DESC, title
LIMIT 5;

Результат: 5 самых длинных фильмов; фильмы одинаковой длительности упорядочены по названию, поэтому результат при каждом запуске одинаков.

Предложение OFFSET

OFFSET пропускает заданное число строк, прежде чем LIMIT начнёт отсчёт:

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY actor_id
LIMIT 5 OFFSET 10;

Результат: актёры с 11-го по 15-го по actor_id: первые 10 строк пропущены, возвращены следующие 5.

MySQL и MariaDB принимают также краткую запись LIMIT 10, 5, где первое число - смещение, а второе - количество строк. Явная запись LIMIT 5 OFFSET 10 легче читается и работает также в PostgreSQL и SQLite.

Постраничный вывод с LIMIT и OFFSET

Пагинация делит длинный результат на страницы. Для страницы N по S строк:

• LIMIT S
• OFFSET (N - 1) * S

SELECT film_id, title
FROM film
ORDER BY title, film_id
LIMIT 10 OFFSET 20;

Результат: третья страница списка фильмов, отсортированного по названию.

База данных всё равно читает и пропускает все строки смещения, поэтому очень большие смещения (страница 10 000) работают медленно. Для глубокой пагинации приложения обычно запоминают последнее значение предыдущей страницы и фильтруют через WHERE.

LIMIT в других СУБД

LIMIT поддерживают MySQL, MariaDB, PostgreSQL и SQLite. В других СУБД синтаксис отличается:

Ключевые выводы:

• LIMIT n возвращает не более n строк.
• OFFSET n пропускает n строк перед выводом результата.
• Всегда используйте LIMIT вместе с ORDER BY и делайте сортировку однозначной, чтобы результат был стабильным.
• Страница N по S строк: LIMIT S OFFSET (N - 1) * S.
• В SQL Server используется TOP, а в стандарте SQL - FETCH FIRST n ROWS ONLY.

Часто задаваемые вопросы

Чем отличаются LIMIT и OFFSET?

LIMIT задаёт, сколько строк вернуть, а OFFSET - сколько строк пропустить перед этим. LIMIT 5 OFFSET 10 пропускает 10 строк и возвращает следующие 5.

Что использовать в SQL Server вместо LIMIT?

SELECT TOP 5 ... для первых строк или ORDER BY ... OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY для пагинации.

Почему запрос с LIMIT каждый раз возвращает разные строки?

Потому что в запросе нет ORDER BY или в столбцах сортировки есть одинаковые значения. Сортируйте по столбцам, которые однозначно определяют строку, например добавьте первичный ключ последним ключом сортировки.