Урок 6. TOP, LIMIT, FETCH и OFFSET в других базах

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

Давайте немного подробнее изучим этот вопрос. Продолжаем работать с таблицей из прошлого урока:

За основу возьмем SQL-запрос из прошлого, который должен вернуть два товара для третьей страницы. Будем использовать этот запрос как начальный для всех СУБД в этом уроке:

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

PostgreSQL

Попробуем выполнить его в PostgreSQL и у нас всё получилось:

И всё, потому что PostgreSQL поддерживает и OFFSET, и LIMIT. Точно так же, как и MySQL, а также SQLite, но об этом позже

Единственное отличие — в постгресе можно использовать OFFSET и LIMIT не только вместе, но и по отдельности. Оставим только OFFSET 4 и запустим:

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

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

Если мы заменим 4 на 1 и еще раз выполним, то получим все товары, кроме самого дорогого, который пропускаем:

Пожалуй, это единственно значимое отличие данной конструкций в PostgreSQL, поэтому двигаемся далее, к Microsoft SQLServer:

Microsoft SQLServer

Тут та же таблица и тот же стартовый запрос. И если его выполнить то мы получим ошибку синтаксиса, так как SQLServer не поддерживает LIMIT и OFFSET. Вместо них вверху после SELECT и до списка столбцов, то есть до звёздочки напишем:

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

То есть выбираем верхние две записи из таблицы:

И с учетом сортировки мы получили два самых дорогих товара.

Перед тем как перейти к смещению, давайте напишем так:

SELECT TOP 50 PERCENT * FROM products
WHERE count > 0 
ORDER BY price DESC

И поcле ввыполнения SQL-запроса мы получим первые 50% строк, то есть половину:

И это одна из отличительных особенностей SQLSerever. В MySQL и Postgresql можно выбирать только строки. Проценты там недоступны. Тут же чуть больше возможностей.

Теперь давайте добавим смещение. Для этого надо полностью удалить блок с TOP, а внизу написать так:

SELECT * FROM products
WHERE count > 0 
ORDER BY price DESC
OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY

Получилось целое предложение, которое читается так: пропустить 4 строки и получить следующие за ним 2. Запустим, всё снова работает. При этом у такой конструкции есть три особенности: во-первых, её нельзя использовать вместе с TOP. Или TOP, или OFFSET FETCH. Во-вторых, OFFSET FETCH является частью ORDER BY и её нельзя использовать без сортировки. В конце прошлого урока я показал, что в MySQL можно использовать LIMIT OFFSET без ORDER BY, но предостерег, что данные могут выводиться в каком-то случайном порядке, поэтому так лучше не делать. В SQLServer это запрещено синтаксически, что защищает вас от логических ошибок.

И наконец, третья особенность заключается в том, что если вам нужно использовать ограничение со смещением, как сейчас, то вам придется писать всю строку из шести слов и двух чисел, так как это цельная синтаксическая конструкция.

Единственное, если вам нужно только смещение, то можно оставить OFFSET 4 ROWS тогда произойдет смещение на 4 записи и после база данных выведет все оставшиеся:

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

С SQLServer на этом мы заканчиваем и далее переходим к Oracle.

Oracle

Тот же запрос, те же данные, и после запуска мы получаем ошибку синтаксиса. И в случае Oracle нам также нужно использовать синтаксис OFFSET FETCH как и MS SQLServer. То есть надо написать так:

SELECT * FROM products
WHERE count > 0 
ORDER BY price DESC
OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY

И, казалось бы, мы имеем тот же самый синтаксис, но нет, в Oracle есть особенности. Например, вы можете встретить варианты, когда вместо NEXT используется FIRST:

...
OFFSET 4 ROWS FETCH FIRST 2 ROWS ONLY

Разницы между ними нет. Она только смысловая:

OFFSET 4 ROWS FETCH NEXT 2 NEXT ONLY

Пропустить 4 строки и получить следующие 2 строки (после пропущенных).

OFFSET 4 ROWS FETCH FIRST 2 NEXT ONLY

Пропустить 4 строки и получить первые две строки (также после пропущенных). Смысл один, просто разные слова.

Также вместо ROWS вы можете использовать просто ROW. Например, если вам нужно получить только одну строку, то слово ROW в единственном числе будет более уместно:

SELECT * FROM products
WHERE count > 0 
ORDER BY price DESC
OFFSET 4 ROWS FETCH FIRST 1 ROW ONLY

Также вы можете убрать FETCH и оставить только OFFSET, чтобы получить все строки, исключая первые четыре:

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

Или наоборот, можете убрать OFFSET и оставить только FETCH, чтобы получить первую первые N строк. В нашем случае первую строку:

SELECT * FROM products
WHERE count > 0 
ORDER BY price DESC
FETCH FIRST 1 ROW ONLY

FETCH FIRST 1 ROW ONLY эквивалентна LIMIT 1 из MySQL или PostgresSQL:

А еще вы можете написать так:

SELECT * FROM products
WHERE count > 0 
ORDER BY price DESC
FETCH FIRST (или NEXT) 50 PERCENT ROWS ONLY

После выполнения мы снова получим 50% товаров, которые есть в наличии. В SQLServer для аналогичного эффекта мы использовали отдельную конструкцию SELECT TOP 50 PERCENT, а в Oracle всё доступно в рамках FETCH PERCENT.

Что ж, осталась последняя база данных — SQLite.

SQLite

И в SQLite действуют точно такие же правила, как в MySQL. То есть все ограничения и смещения делаем с помощью LIMIT и OFFSET и при этом OFFSET является частью LIMIT и её нельзя использовать отдельно. Выполним запрос. Всё работает.

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