再びSQL関連の記事です(`・ω・´)
職場で、
そこでROWNUM関数/LIMIT関数とORDER BY句の併用方法について、
DBで違いがあったので、
ORDER BY句の説明は以前記事にしているので、良ければご覧くださいー！
もくじ
２．ROWNUM関数／LIMIT関数と、ORDER BY句の親和性
４．PostgreSQLの挙動（LIMIT×ORDER BY）
５．まとめ
１．ROWNUM関数／LIMIT関数とは
◎ROWNUM関数
Oracle Databaseの関数の１つ。
問合せ結果の返却数を制限する。
例えば、ROWNUM関数で3を指定すると、
最初の3件だけが返される。
実行イメージ（以下は最初の3件までが返される）↓
> SELECT * FROM FRUITS_TABLE WHERE ROWNUM <= 3;
ID FRUIT_NAME SHOP_ID
---------- ---------- ----------
1 apple 2
2 banana 2
3 orange 2
※参考 ROWNUM関数を指定しなかった場合（結果レコードすべてが返される）↓
> SELECT * FROM FRUITS_TABLE;
ID FRUIT_NAME SHOP_ID
---------- ---------- ----------
1 apple 2
2 banana 2
3 orange 2
4 chery 1
5 grape 4
7 peach
◎LIMIT関数
ROWNUM関数のPostgreSQL版。
PostgreSQLでは、LIMIT関数を用いて、
実行イメージは以下の通り。（テーブルの中身はOracleに同じ）
> SELECT * FROM FRUITS_TABLE LIMIT 3;
id | fruit_name | shop_id
----+------------+---------
1 | apple | 2
2 | banana | 2
4 | chery | 1
２．ROWNUM関数／LIMIT関数と、ORDER BY句の親和性
ROWNUM関数、LIMIT関数は、ORDER BY句（結果の並び替え（ソート）を行うための句）
ROWNUM、LIMIT各関数は、問合せ結果のうち「最初の件」を返却するためのものであるが、
指定した件数以上のレコードが条件に当てはまっている場合、
どのレコードが「最初の●件」内に来るかは分からない。
つまり状況によって、返されるレコードが変わってしまい、
意図しないレコードが「最初の●件」
実際、１．ROWNUM関数／LIMIT関数とは で紹介した、OracleとPostgreSQLの実行結果は、
どちらも同じ中身のテーブルで、「最初の3件」
同じ結果レコードを想定していたはずが、
ORDER BYであらかじめ検索結果を並び替え、
そのうえでROWNUM、LIMIT各関数の返却レコードを、
ただし、１つ注意点がある。
それは、OracleとPostgreSQLで、ROWNUM／LIMIT関数とORDER BY句の併用時の挙動には、大きな違いがあるということ。
以降でそれぞれの挙動を説明する。
３．Oracleの挙動（ROWNUM×ORDER BY）
まずOracleの挙動から見ていく。
前提として、以下のテーブルがあるとする。
> SELECT * FROM FRUITS_TABLE;
ID FRUIT_NAME SHOP_ID
---------- ---------- ----------
1 apple 2
2 banana 2
3 orange 2
4 chery 1
5 grape 4
7 peach
以下の例では、SHOP_
> SELECT *
FROM FRUITS_TABLE
WHERE ROWNUM <= 3
ORDER BY SHOP_ID;
期待結果は以下を期待したいが…。
ID FRUIT_NAME SHOP_ID
---------- ---------- ----------
4 chery 1
1 apple 2
3 orange 2
実際は以下のようになり、ORDER BYが効いていないように見える。
ID FRUIT_NAME SHOP_ID
---------- ---------- ----------
1 apple 2
3 orange 2
2 banana 2
◎なぜか？
Oracleで、上記のクエリでROWNUM関数とORDER BYを併用した場合、
①ROWNUMにより返却されるレコードが決まる（
↓
②絞ったレコードに対し、ORDER BYで並び替え
という順で処理される。
しかしここでやりたいことは、
①ORDER BYで結果を並び替え
↓
②並び替えた結果のうち、上位3件を取得する
という順である。
この順を実現するには、面倒だが、
具体的には以下のようなクエリとする必要がある。
> SELECT *
FROM (
SELECT *
FROM FRUITS_TABLE
ORDER BY SHOP_ID )
WHERE ROWNUM <= 3;
サブクエリが先に処理されるため、まずORDER BYが実行され、
ORDER BY実行後の結果に対し、
実行結果は以下のようになる。SHOP_ID昇順に並び替えがきちんと行われている。
ID FRUIT_NAME SHOP_ID
---------- ---------- ----------
4 chery 1
1 apple 2
3 orange 2
４．PostgreSQLの挙動（LIMIT×ORDER BY）
一方でPostgreSQLは、
OracleのROWNUM関数は、WHERE条件内に記述していたのに対し、
PostgreSQLのLIMIT関数は、WHERE条件外に記述するため、ORDER BYよりも後の挙動になるためである。
PostgreSQLでは、以下のような記述で、
ORDER BY並び替え→LIMITで件数制御 という処理順になる。
> SELECT *
FROM FRUITS_TABLE
ORDER BY SHOP_ID
LIMIT 3;
実行結果。
id | fruit_name | shop_id
----+------------+---------
4 | chery | 1
1 | apple | 2
2 | banana | 2
※今回はORDER BY句で指定したのがSHOP_ID列だけだったため、Oracleでサブクエリを用いた際の実行結果とは異なっている。もし、より厳密にしたいのであれば、SHOP_ID列に加えて、ID列もソート指定するとよい。
５．まとめ
今回の内容を改めてまとめると、以下のようになる。
◎Oracle
サブクエリを入れなかった場合、
ROWNUMで数を制御 → ORDER BYで並び替え
の処理順になる。
並び替えたうえで数を制御したい場合は、ORDER BY句をサブクエリとして先に実行する必要がある。
◎PostgreSQL
サブクエリは不要であり、
ORDER BYで並び替え → LIMITで数を制御
の処理順となる。
Oracleは一手間加えないといけないので、特に注意する必要がありそう。
今回は以上！