SQL練習室
← SQLガイド

SQLのLIMITとOFFSETとは?上位N件・ページング・同値の並び順を解説

LIMITは返す行数の上限を決め、OFFSETは先頭から何行を飛ばすかを決めます。上位N件の取得や、1ページ3件ずつの一覧表示に使います。文房具8件の表で、ORDER BYとの組み合わせ、同額の商品があるときに順序を一意にする方法、標準SQLのFETCH FIRSTまで確認しましょう。

例で使う表

itemsは商品を保存した表で、1行が商品1件です。priceは価格(円)です。300円の商品が3件(ノート・定規・ハサミ)、150円の商品が2件(ペン・鉛筆)あり、同額の行をどう並べるかが後半の論点になります。この表のidは重複しない値です。

items:商品8件(priceは価格・円)
idnameprice
1ノート300
2ペン150
3定規300
4消しゴム100
5付箋200
6鉛筆150
7ファイル400
8ハサミ300

LIMIT・OFFSETの基本構文

sql
SELECT *
FROM items
ORDER BY id
LIMIT 3;

LIMITで先頭の3件だけ取り出す

LIMITの後ろに件数を書くと、結果の先頭からその件数までに絞れます。idの昇順に並べてLIMIT 3とすると、idが1・2・3の3行が返ります。表に8件あっても返るのは3件です。表の件数がLIMITより少ない場合は、ある分だけが返ります。LIMITは必ずORDER BYの後ろに書きます。

sql
SELECT *
FROM items
ORDER BY id
LIMIT 3;
実行結果
idnameprice
1ノート300
2ペン150
3定規300

OFFSETで先頭の行を飛ばしてページングする

OFFSETは、返す前に飛ばす行数です。1ページ3件とすると、1ページ目はOFFSET 0、2ページ目はOFFSET 3、3ページ目はOFFSET 6です。ここではLIMIT 3 OFFSET 3で、idが4・5・6の行を取り出します。OFFSETで飛ばした行はLIMITの件数に含まれません。ページ番号pのOFFSETは(p - 1)× 件数で求められます。

sql
SELECT *
FROM items
ORDER BY id
LIMIT 3 OFFSET 3;
実行結果
idnameprice
4消しゴム100
5付箋200
6鉛筆150

価格の高い上位3件を取り出し、同額はidで順序を決める

上位N件は、ORDER BYで並べ替えてからLIMITで切ります。ところがpriceの降順だけだと、300円の3件(ノート・定規・ハサミ)の順序が決まりません。400円のファイルの次に、この3件のうちどれが2位と3位に入るかが実行ごとに変わる可能性があります。ORDER BY price DESC, idのように重複しない列を最後に足すと、順序が一意になります。結果はファイル、ノート、定規です。

sql
SELECT *
FROM items
ORDER BY price DESC, id
LIMIT 3;
実行結果
idnameprice
7ファイル400
1ノート300
3定規300

標準SQLのFETCH FIRST n ROWS ONLYで書く

LIMITはPostgreSQLなどで使える書き方で、標準SQLでは OFFSET n ROWS と FETCH FIRST n ROWS ONLY を使います。PostgreSQLは両方に対応しています。次のSQLは、価格の高い順(同額はid順)に並べた4番目から3件を取り、8(ハサミ)、5(付箋)、2(ペン)を返します。LIMIT 3 OFFSET 3と同じ意味で、標準SQLの書き方なので、ほかのDBへ移す場合の候補になります(対応状況はDBごとに確認が必要です)。

sql
SELECT *
FROM items
ORDER BY price DESC, id
OFFSET 3 ROWS
FETCH FIRST 3 ROWS ONLY;
実行結果
idnameprice
8ハサミ300
5付箋200
2ペン150

最後のページは件数が足りなくなる

8件を3件ずつ表示すると、3ページ目は2件しかありません。LIMIT 3 OFFSET 6は、7番目と8番目の行だけを返します。OFFSETが総件数以上だと、0行が返ります。ページング画面では、件数が足りないページや空のページが出ることを想定しておきます。ORDER BYで順序を固定していないと、ページをまたいで同じ行が重複したり、行が抜けたりする原因になります。

sql
SELECT *
FROM items
ORDER BY id
LIMIT 3 OFFSET 6;
実行結果
idnameprice
7ファイル400
8ハサミ300

LIMIT・OFFSETでよくある間違い

ORDER BYなしのLIMITは、どの行が返るか保証されない

ORDER BYがないSELECTは行の順序が決まらないため、LIMITで切ると「どの3件か」も不定です。手元で毎回同じ結果に見えても、データの更新やプランの変化で変わります。ページングでLIMITやOFFSETの値を変えると、別の実行計画になり、行の重複や抜けが起きることもあります。必ずORDER BYを付け、同値がありうる列だけで並べるときは重複しない列を最後に足します。

sql
SELECT *
FROM items
LIMIT 3;

実際に1問解いてみる

LIMIT

itemsをidの昇順に並べ、先頭の3件を全列取得してください。

テーブル

読み込み中…

実行結果

関連構文

LIMIT・OFFSETについてよくある質問

LIMITとFETCH FIRSTのどちらを使えばよいですか?
PostgreSQLでは同じ意味で、どちらも使えます。LIMITは短く書け、FETCH FIRSTは標準SQLなので他のDBへ移しやすい書き方です。DBによって使える構文が異なるため、移行先の対応を確認してください。
ORDER BYを付けずにLIMIT 1を使うとどうなりますか?
1行は返りますが、どの行かは保証されません。最新の1件や最大の1件が欲しいときは、ORDER BYで並べ替えてからLIMIT 1にします。同値がありうる場合は、idなど重複しない列も並べ替えに足します。
OFFSETを使わずに次のページを取るにはどうしますか?
前のページの最後の行の値を使い、WHEREでそれより後ろの行に絞る方法があります。ノート記載のキーセット方式です。OFFSETで飛ばす行の処理が不要になるため、深いページでも速度が落ちにくくなります。
LIMIT 0やLIMIT NULLはどうなりますか?
LIMIT 0は0行を返します。LIMIT NULLとLIMIT ALLは、LIMITを書かないのと同じで制限なしになります。OFFSET 0とOFFSET NULLは、OFFSETを書かないのと同じです。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。