Paging: LIMIT, OFFSET … FETCH, TOP, ROWNUM

Jedes Beispiel unten liefert dieselben zehn Zeilen: die Produkte 21 bis 30 nach ID.

SQLite, MySQL, MariaDB, PostgreSQL

Diese vier Datenbanken verwenden LIMIT <anzahl der Zeilen> OFFSET <zu überspringende Zeilen>. Eine Abfrage dafür läuft daher auch auf den drei anderen unverändert.

-- 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 und MariaDB akzeptieren auch LIMIT 20, 10. In dieser Schreibweise ist 20 der Offset und 10 die Anzahl der zurückgegebenen Zeilen. Das entspricht LIMIT 10 OFFSET 20, aber die Zahlen stehen in umgekehrter Reihenfolge. Die Schreibweise mit OFFSET ist meist leichter zu lesen.

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;

Zwei Dinge unterscheiden sich von der Gruppe oben. OFFSET … FETCH ist ein Anhängsel von ORDER BY und keine eigenständige Klausel, eine Abfrage ohne ORDER BY ist also ein Syntaxfehler und nicht etwa eine unsortierte Seite. Und TOP, die ältere und kürzere Form, kann keine Zeilen überspringen: Es liefert die erste Seite und nichts danach.

Deshalb erzeugt Planche für SQL Server zwei verschiedene Formen. Das Sicherheits-Zeilenlimit auf einer Editor-Anweisung wird durch ein TOP n hinter SELECT gesetzt, wofür die Abfrage sonst nichts braucht; das blätternde Tabellenraster gibt OFFSET n ROWS FETCH NEXT m ROWS ONLY aus und ergänzt ein ORDER BY, damit es zulässig ist.

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) hat die Standardklausel eingeführt; FETCH FIRST n ROWS ONLY allein ist die Form ohne Paging. Vor 12c gab es überhaupt keine Klausel, nur ROWNUM – und ROWNUM wird vergeben, während die Zeilen entstehen, also vor dem Sortieren. WHERE ROWNUM <= 10 ORDER BY price sortiert daher zehn beliebige Zeilen, statt die Top Ten zu liefern. Die verschachtelte Form im Beispiel ist der Ausweg: innen sortieren, in der Mitte nummerieren, außen filtern.

Was OFFSET wirklich kostet

Bei einem großen OFFSET muss die Datenbank die vorherigen Zeilen zunächst durchlaufen. Mit OFFSET 10000 liest sie 10.010 Zeilen, um nur 10 zurückzugeben. Bei großen Tabellen steigt die Antwortzeit deshalb mit der Seitennummer.

Wenn die Zeilen einen eindeutigen, sortierten Schlüssel haben, fragen Sie die nächste Seite stattdessen über diesen Schlüssel an:

-- 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;

Statt „überspringe 20“ lautet die Bedingung „beginne nach ID 20“. Ein Index kann diese Stelle direkt finden, sodass der Aufwand auch bei späteren Seiten ähnlich bleibt. Dafür lässt sich nicht zu einer beliebigen Seitennummer springen; das Verfahren eignet sich für Infinite Scroll und Cursor-APIs, nicht für nummerierte Seiten. Die Bedingung muss dieselben Spalten wie ORDER BY verwenden und eine eindeutige Reihenfolge bilden. Bei ORDER BY price, id lautet sie WHERE (price, id) > (:price, :id). PostgreSQL, MySQL 8, SQLite und Oracle unterstützen das direkt, SQL Server nicht.