Help us understand the problem. What is going on with this article?

Oracle SQL(11g)でMySQLのLIMIT OFFSET句を再現したい

More than 1 year has passed since last update.

動作環境

  • MySQL 8.0
  • Oracle Database 11g

MySQLの場合

10レコード分取得した場合は、

SELECT column1 FROM table1 LIMIT 10;

10レコード目から20レコード取得した場合、下記のようになります。

-- MySQLの書き方①
SELECT column1 FROM table1 LIMIT 10 OFFSET 20;

-- MySQLの書き方②
SELECT column1 FROM table1 LIMIT 10 20;

②のように、OFFSETを省略して記載することも可能です。
しかし、個人的には、①をよく使っている気がします。

Oracle SQLの場合

でもOracleだと、LIMITもOFFSETもありません。
Oracleの場合は、疑似列ROWNUMを使用すれば実現できます。

10レコード分取得した場合は、

SELECT column1 FROM table1 WHERE ROWNUM <= 10;

注意しなければならないのは、10レコード目から20レコード取得した場合です。
下記のように書きたくなりますが、これでは取得できません。
(自分はよく忘れて、こう書きます…………)

-- これでは取得できない!!!
SELECT column1 FROM table1 WHERE ROWNUM <= 10 AND 20 <= ROWNUM;
SELECT column1 FROM table1 WHERE ROWNUM BETWEEN 10 AND 20;

最初にfetchされた1行目がROWNUM=1となり、WHERE句の条件を満たすものがないため、レコードを取得できません。
なので、副問合せを使用して、ROWNUMを確定させてから、範囲指定して取得する方法を取る必要があります。

SELECT
  column1 
FROM
  ( 
    SELECT
      column1
      , ROWNUM AS rn 
    FROM
      table1
  ) 
WHERE
  rn BETWEEN 10 AND 20

しかし、ROWNUMには欠点があります。
それはORDER BYを使った場合、意図しない順番でデータが取れてしまう場合があることです。
詳細は割愛しますが、ORDER BYする前に、ROWNUMの採番が行われてしまっているためです。

なので、Xレコード目からXレコード分取得する場合は、
ROW_NUMBER()を使用したほうが無難です。

SELECT
  column1 
FROM
  ( 
    SELECT
      column1
      , ROW_NUMBER() OVER (ORDER BY column1) AS rn 
    FROM
      table1
  ) 
WHERE
  rn BETWEEN 10 AND 20

ちなみに、selectする項目にアスタリスク(*)を使いたい場合は、

SELECT
  *
FROM
  ( 
    SELECT
      table1.*
      , ROW_NUMBER() OVER (ORDER BY column1) AS rn 
    FROM
      table1
  ) 
WHERE
  rn BETWEEN 10 AND 20

テーブル名.*という形式になります。

おまけ

Oracle Database 12cの場合

oracle 12cの場合は、もっと簡潔に記載することができます。

SELECT column1 FROM table1 ORDER BY column1
OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;
riekure
■目標:100items ■投稿内容:Unity(C#), Ruby, Rails, Java, AWS, SQL, Python など ■近況報告:\(^o^)/オワタ
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした