方言別のページング：LIMIT・OFFSET FETCH・TOP・ROWNUM

ページ単位で行を取得する構文はデータベースごとに異なります。同じページを返す5つの書き方と、大きなテーブルで有効なキ―セットページングを比べます。

以下の例はすべて、id 順で21〜30番目の同じ10行を返します。

SQLite・MySQL・MariaDB・PostgreSQL

この4つは LIMIT <取得件数> OFFSET <スキップ件数> という構文を共有しています。どれか1つ向けに書いたクエリを、残りのデータベースでもそのまま実行できます。

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

Planche で実行

結果 — 10 行のうち先頭 5 行

id	name	price
21	Onyx Sofa	1637.9
22	Zen Soundbar	1991.58
23	Onyx Catan Plus	525.91
24	Onyx Sleeping Bag X	2019.45
25	Pixel Lean Startup	188.86

ブラウザの別タブで Planche が開き、サンプルデータベースを読み込んだ状態でこのクエリが入ります。このページの例はどれも同じタブを使い回します。F9 を押すまで実行はされません。

MySQL / MariaDB

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 / MariaDB — 比較のために載せています。ブラウザ内のサンプルは SQLite なので、この文はここでは実行できません。

MySQL と MariaDB は LIMIT 20, 10 も受け付けます。この形では、20 がスキップ件数、10 が取得件数です。LIMIT 10 OFFSET 20 と同じ結果ですが、数字の並びが逆になります。新しく書くクエリでは OFFSET を使う形のほうが分かりやすいでしょう。

PostgreSQL

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;

PostgreSQL — 比較のために載せています。ブラウザ内のサンプルは SQLite なので、この文はここでは実行できません。

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;

SQL Server — 比較のために載せています。ブラウザ内のサンプルは SQLite なので、この文はここでは実行できません。

上のグループと違う点が2つあります。OFFSET … FETCH は独立した句ではなく ORDER BY の後ろに付くものなので、ORDER BY のないクエリは「順序のないページ」ではなく構文エラーになります。そしてより古く短い TOP には、行を飛ばす手段がありません。最初のページは取れますが、その先は取れません。

Planche が SQL Server 向けに2つの形を作り分けているのはこのためです。エディタの文にかける安全上の行数上限は SELECT の直後に TOP n を差し込むだけで済み、他に何も要りません。一方、ページをめくるテーブル表示は OFFSET n ROWS FETCH NEXT m ROWS ONLY を出し、成立させるために ORDER BY を足します。

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 — 比較のために載せています。ブラウザ内のサンプルは SQLite なので、この文はここでは実行できません。

Oracle 12c（2013年）で標準の句が入りました。ページングしない形は FETCH FIRST n ROWS ONLY だけです。12c より前には句そのものがなく、ROWNUM しかありませんでした。しかも ROWNUM は行が生成される順、つまり並べ替えの前に振られるので、WHERE ROWNUM <= 10 ORDER BY price は適当な10行を並べ替えるだけで、上位10件にはなりません。例に出した入れ子の形が回避策です。いちばん内側で並べ替え、中間で番号を振り、外側で絞ります。

OFFSET の実際のコスト

OFFSET が大きい場合、エンジンはその手前の行も順に処理します。OFFSET 10000 では、10行を返すために10,010行を読みます。そのため、大きなテーブルではページ番号が進むほど応答時間も長くなります。

一意で順序のあるキーがあるなら、「次のページ」をそのキーで聞いてください。

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