Pagination : LIMIT, OFFSET … FETCH, TOP, ROWNUM

Chaque exemple ci-dessous renvoie les mêmes dix lignes : les produits 21 à 30 par identifiant.

SQLite, MySQL, MariaDB, PostgreSQL

Ces quatre bases utilisent la syntaxe LIMIT <nombre de lignes> OFFSET <nombre de lignes à ignorer>. Une requête écrite pour l’une fonctionne donc telle quelle sur les trois autres.

-- Rows 21 to 30, ordered by id.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- The older comma form means the same thing, with the arguments reversed:
-- offset first, then count. Easy to misread, so prefer the form above.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 20, 10;

MySQL et MariaDB acceptent aussi LIMIT 20, 10. Dans cette écriture, 20 est le nombre de lignes à ignorer et 10 le nombre de lignes à renvoyer. Elle équivaut à LIMIT 10 OFFSET 20, mais l’ordre des nombres est inversé. La forme avec OFFSET est généralement plus claire.

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- PostgreSQL also accepts the SQL standard spelling.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL Server

-- OFFSET / FETCH requires an ORDER BY. Without one it is a syntax error.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- TOP caps the result but cannot skip, so it only gives you the first page.
SELECT TOP 10 id, name, price
FROM products
ORDER BY id;

Deux choses diffèrent du groupe précédent. OFFSET … FETCH est un suffixe de ORDER BY, pas une clause indépendante : une requête sans ORDER BY est donc une erreur de syntaxe et non une page non triée. Et TOP, la forme plus ancienne et plus courte, n’a aucun moyen de sauter des lignes : il peut donner la première page et rien après.

C’est pour cela que Planche génère deux formes différentes pour SQL Server. La limite de lignes de sécurité sur une instruction de l’éditeur est appliquée en injectant TOP n après SELECT, ce qui n’exige rien d’autre ; la grille de données, qui pagine, émet OFFSET n ROWS FETCH NEXT m ROWS ONLY et ajoute un ORDER BY pour que ce soit légal.

Oracle

-- Oracle 12c and later.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Before 12c, ROWNUM had to be applied to an already-sorted subquery.
SELECT * FROM (
  SELECT t.*, ROWNUM AS rn FROM (
    SELECT id, name, price FROM products ORDER BY id
  ) t
  WHERE ROWNUM <= 30
)
WHERE rn > 20;

Oracle 12c (2013) a ajouté la clause standard, et FETCH FIRST n ROWS ONLY seul en est la forme sans pagination. Avant 12c, il n’y avait aucune clause, seulement ROWNUM — et ROWNUM est attribué à mesure que les lignes sont produites, avant le tri : WHERE ROWNUM <= 10 ORDER BY price trie donc dix lignes arbitraires au lieu de renvoyer les dix premières. La forme imbriquée de l’exemple est le contournement : trier dans la requête la plus interne, numéroter dans celle du milieu, filtrer à l’extérieur.

Ce que coûte réellement OFFSET

Avec un OFFSET élevé, le moteur doit d’abord parcourir les lignes précédentes. OFFSET 10000 lui fait ainsi lire 10 010 lignes pour n’en renvoyer que 10. Le temps de réponse augmente donc avec le numéro de page, ce qui devient visible sur les grandes tables.

Quand les lignes ont une clé unique et ordonnée, demandez la page suivante par cette clé :

-- The same page without OFFSET: continue after the last id of the
-- previous page. The database jumps straight there through the index.
SELECT id, name, price
FROM products
WHERE id > 20
ORDER BY id
LIMIT 10;

Au lieu de demander « ignore 20 lignes », cette requête demande « commence après l’id 20 ». Un index peut atteindre ce point directement, de sorte que le coût reste stable au fil des pages. En contrepartie, on ne peut pas accéder à un numéro de page arbitraire : cette approche convient au défilement continu et aux API à curseur, pas aux listes numérotées. La condition doit utiliser les mêmes colonnes que l’ORDER BY et former un ordre unique. Pour ORDER BY price, id, la suite s’écrit WHERE (price, id) > (:price, :id). PostgreSQL, MySQL 8, SQLite et Oracle prennent cette syntaxe en charge directement, contrairement à SQL Server.