SQLのLIMITとOFFSETとは?上位N件・ページング・同値の並び順を解説
LIMITは返す行数の上限を決め、OFFSETは先頭から何行を飛ばすかを決めます。上位N件の取得や、1ページ3件ずつの一覧表示に使います。文房具8件の表で、ORDER BYとの組み合わせ、同額の商品があるときに順序を一意にする方法、標準SQLのFETCH FIRSTまで確認しましょう。
例で使う表
itemsは商品を保存した表で、1行が商品1件です。priceは価格(円)です。300円の商品が3件(ノート・定規・ハサミ)、150円の商品が2件(ペン・鉛筆)あり、同額の行をどう並べるかが後半の論点になります。この表のidは重複しない値です。
| id | name | price |
|---|---|---|
| 1 | ノート | 300 |
| 2 | ペン | 150 |
| 3 | 定規 | 300 |
| 4 | 消しゴム | 100 |
| 5 | 付箋 | 200 |
| 6 | 鉛筆 | 150 |
| 7 | ファイル | 400 |
| 8 | ハサミ | 300 |
LIMIT・OFFSETの基本構文
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の後ろに書きます。
SELECT *
FROM items
ORDER BY id
LIMIT 3;| id | name | price |
|---|---|---|
| 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)× 件数で求められます。
SELECT *
FROM items
ORDER BY id
LIMIT 3 OFFSET 3;| id | name | price |
|---|---|---|
| 4 | 消しゴム | 100 |
| 5 | 付箋 | 200 |
| 6 | 鉛筆 | 150 |
価格の高い上位3件を取り出し、同額はidで順序を決める
上位N件は、ORDER BYで並べ替えてからLIMITで切ります。ところがpriceの降順だけだと、300円の3件(ノート・定規・ハサミ)の順序が決まりません。400円のファイルの次に、この3件のうちどれが2位と3位に入るかが実行ごとに変わる可能性があります。ORDER BY price DESC, idのように重複しない列を最後に足すと、順序が一意になります。結果はファイル、ノート、定規です。
SELECT *
FROM items
ORDER BY price DESC, id
LIMIT 3;| id | name | price |
|---|---|---|
| 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ごとに確認が必要です)。
SELECT *
FROM items
ORDER BY price DESC, id
OFFSET 3 ROWS
FETCH FIRST 3 ROWS ONLY;| id | name | price |
|---|---|---|
| 8 | ハサミ | 300 |
| 5 | 付箋 | 200 |
| 2 | ペン | 150 |
最後のページは件数が足りなくなる
8件を3件ずつ表示すると、3ページ目は2件しかありません。LIMIT 3 OFFSET 6は、7番目と8番目の行だけを返します。OFFSETが総件数以上だと、0行が返ります。ページング画面では、件数が足りないページや空のページが出ることを想定しておきます。ORDER BYで順序を固定していないと、ページをまたいで同じ行が重複したり、行が抜けたりする原因になります。
SELECT *
FROM items
ORDER BY id
LIMIT 3 OFFSET 6;| id | name | price |
|---|---|---|
| 7 | ファイル | 400 |
| 8 | ハサミ | 300 |
LIMIT・OFFSETでよくある間違い
ORDER BYなしのLIMITは、どの行が返るか保証されない
ORDER BYがないSELECTは行の順序が決まらないため、LIMITで切ると「どの3件か」も不定です。手元で毎回同じ結果に見えても、データの更新やプランの変化で変わります。ページングでLIMITやOFFSETの値を変えると、別の実行計画になり、行の重複や抜けが起きることもあります。必ずORDER BYを付け、同値がありうる列だけで並べるときは重複しない列を最後に足します。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:LIMITとOFFSET — LIMIT・OFFSETの動作、ORDER BYの必要性、OFFSETの処理コスト
- PostgreSQL公式:SELECT(LIMIT句) — FETCH FIRST、OFFSET ROWS、WITH TIESの構文