Урок 5. Ограничение и смещение

Ограничение с помощью LIMIT

Кроме фильтров и сортировок, в SELECT-запросах можно также ограничивать выборку. Например, когда вам нужные не все данные, а скажем только первые 10 строк. Для этого существует конструкция LIMIT.

Работать в этом уроке будем с такой таблицей:

Давайте попробуем вывести 5 самых дорогих товаров. Сперва напишем базовый SQL-запрос:

SELECT * FROM products 
ORDER BY price DESC

И с помощью данного запроса мы получим все товары, отсортированные по цене в обратном порядке — ORDER BY price DESC. То есть дорогие товары будут сверху:

Теперь, если я оставлю верхние 5 записей, то как раз и получу 5 самых дорогих товаров:

SELECT * FROM products 
ORDER BY price DESC 
LIMIT 5

И LIMIT 5 как раз и говорит базе, что нам нужны только 5 первых (верхних) записей. После выполнения запроса мы получим такую таблицу:

Теперь давайте получим 5 самых дорогих товаров, которые есть на складе. Для этого нужно написать такой SQL-запрос:

SELECT * FROM products 
WHERE count > 0 
ORDER BY price DESC 
LIMIT 5

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

Обратите внимание на порядок следования блоков запроса:

1. Сперва идет SELECT FROM — выбрать ИЗ;
2. Затем блок условия WHERE;
3. После сортировка ORDER BY;
4. И завершает ограничение LIMIT.

Первый блок SELECT FROM обязательный, так как он говорит, что мы хотим сделать.

Остальные блоки опциональны. То есть мы можем убрать любой из них, главное, чтобы сохранялся порядок: (1) WHERE (2) ORDER BY (3) LIMIT.

Смещение с помощью OFFSET

Но вернемся снова к LIMIT. И помимо ограничения выборки, мы также можем делать смещение. Например, искать не первые 5 самых дорогих товаров, а следующую пятерку.

Для этого после LIMIT нужно добавить блок OFFSET:

SELECT * FROM products 
WHERE count > 0 
ORDER BY price DESC 
LIMIT 5 OFFSET 5

И собственно конструкция OFFSET указывает на то, сколько записей нужно пропустить. После запуска мы получим 3 товара, так как изначально в таблице у нас 8 товаров, из которых 5 мы пропускаем с помощью OFFSET, а затем с помощью LIMIT выводим еще 5, но так как после пропуска остается всего 3 товара, то их мы и видим:

Постраничный просмотр

Благодаря этой особенности, с помощью LIMIT и OFFSET можно организовывать постраничный просмотр информации. Например, нам нужно выводить по два товара на странице, скажем на каком сайте или в программе.

Тогда в LIMIT мы прописываем двойку, а для OFFSET изначально ставим 0:

SELECT * FROM products 
WHERE count > 0 
ORDER BY price DESC 
LIMIT 2 OFFSET 0

После запуска SQL-запроса мы увидим два самых дорогих товара, которые будут отображаться на первой странице:

Далее нам нужно получить следующую пару. Для этого делаем пропуск первых двух товаров:

SELECT * FROM products 
WHERE count > 0 
ORDER BY price DESC 
LIMIT 2 OFFSET 2

Теперь получим товары на третьей странице и для этого мы можем воспользоваться вот такой формулой:
(P — 1) x N, где P — это номер страницы, которая нам нужна, а N – это количество товаров, которые мы выводим на странице.

Значение N у нас фиксировано и указывается в блоке LIMIT. То есть сейчас это 2.
Значение P мы выбираем из набора целых чисел: 1, 2, 3 и тд.
Результат вычисления мы подставляем в блок OFFSET.

Сейчас мы делаем расчеты для третьей страницы, поэтому получаем:
(P — 1) x N = (3 — 1) x 2 = 4

Ставим четверку в OFFSET и получаем финальный SQL-запрос:

SELECT * FROM products 
WHERE count > 0 
ORDER BY price DESC 
LIMIT 2 OFFSET 4

Который вернет следующую таблицу:

С основой мы закончили, но хочу отметить одну особенность конструкции OFFSET, а именно то, что OFFSET в MySQL является частью LIMIT.

То есть мы можем использовать или чистый LIMIT, или LIMIT в паре с OFFSET, но не можем использовать OFFSET сам по себе, так как это будет синтаксической ошибкой.

Также мы не можем менять LIMIT и OFFSET местами.

Что ж, на этом с базовыми возможностями ограничений и смещений мы заканчиваем, а в следующем уроке рассмотрим особенности применения LIMIT и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.