読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

指定した範囲の行を取得するクエリ

SQL DB2 Oracle PostgreSQL MySQL SQL Server Firebird HSQLDB SQLite H2 Derby

DB2 Express-C v9.7.2のWindows版で使えていたLIMIT/OFFSETが同Linux版で使えなくて、プラットフォームによって違うのかと思ってしまい、同等のことができる構文をいろいろと調べた挙句、インストール直後の設定が違っていただけという。
で、せっかくなので、調べたいろいろをまとめてみようということで。
ただDBごとに並べても面白くないので(謎)、構文ごとにまとめてみるというアプローチで(更謎)。

前提として、次のようなテーブルを作っておく。

CREATE TABLE hoge(str VARCHAR(8))
INSERT INTO hoge(str) VALUES('bbb')
INSERT INTO hoge(str) VALUES('ddd')
INSERT INTO hoge(str) VALUES('fff')
INSERT INTO hoge(str) VALUES('hhh')
INSERT INTO hoge(str) VALUES('aaa')
INSERT INTO hoge(str) VALUES('ccc')
INSERT INTO hoge(str) VALUES('eee')
INSERT INTO hoge(str) VALUES('ggg')

INSERT順をabc順にしなかったのは、ORDER BYを指定しなかったときに挿入順で返す実装が多いので、ソートされていることの確認になるかなと考えて。
このテーブルhogeを、str列で昇順にソートして、先頭2行を取れば'aaa'と'bbb'が、4行目から2行を取れば'ddd'と'eee'が、7行目以降を取れば'ggg'と'hhh'がそれぞれ得られる。

LIMIT/OFFSET

ある意味、一番ポピュラーなもの。ただ、SQL標準規格(SQL2008時点)には含まれていない。

-- (1)
SELECT str FROM hoge ORDER BY str LIMIT 2
-- (2)
SELECT str FROM hoge ORDER BY str LIMIT 2 OFFSET 3
-- (3)
SELECT str FROM hoge ORDER BY str LIMIT 3,2
-- (4)
SELECT str FROM hoge ORDER BY str OFFSET 6
-- (5)
SELECT str FROM hoge ORDER BY str LIMIT 6,18446744073709551615
-- (6)
SELECT str FROM hoge ORDER BY str LIMIT 18446744073709551615 OFFSET 6
-- (7)
SELECT str FROM hoge ORDER BY str LIMIT 6,9223372036854775807
-- (8)
SELECT str FROM hoge ORDER BY str LIMIT 9223372036854775807 OFFSET 6
-- (9)
SELECT str FROM hoge ORDER BY str LIMIT 6,4294967295
-- (10)
SELECT str FROM hoge ORDER BY str LIMIT 4294967295 OFFSET 6
-- (11)
SELECT str FROM hoge ORDER BY str LIMIT 6,2147483647
-- (12)
SELECT str FROM hoge ORDER BY str LIMIT 2147483647 OFFSET 6

(5)〜(12)は、7行目以降を取得するための苦肉の策(謎)。

指定しているでっかい数値(何)は、符号なし64ビット整数の最大、符号付き64ビット整数の最大、符号なし32ビット整数の最大、符号付き32ビット整数の最大。
これら各構文のサポート状況は次の通り。

DBMS (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) (12)
DB2 9.1 × × × × × × × × × × × ×
DB2 9.5 × × × × × × × × × × × ×
DB2 9.7 (*1) (*1) (*1) × (*1) (*1) × × (*1) (*1) × ×
Oracle 10g × × × × × × × × × × × ×
PostgreSQL 8.0 × × × × × × × × (*2)
PostgreSQL 8.1 × × × × × × × × (*2)
PostgreSQL 8.2 × × × × (*2) × ×
PostgreSQL 8.3 × × × × (*2) × ×
PostgreSQL 8.4 × × × × (*2) × ×
PostgreSQL 9.0 × × × × (*2) × ×
MySQL 4.1 ×
MySQL 5.0 ×
MySQL 5.1 ×
MySQL 5.5 ×
Firebird 2.1 × × × × × × × × × × × ×
Firebird 2.5 × × × × × × × × × × × ×
Derby 10.5.3.0 × × × × × × × × × × × ×
SQLite 3.6.14.2 × × ×
SQLServer 2005 × × × × × × × × × × × ×
SQLServer 2008 × × × × × × × × × × × ×
H2 1.2 × × × × × × ×
H2 1.3 × × × × × × ×
HSQLDB 1.8.1 × × × × × × × × ×
  • (*1)後述する互換性オプションを有効にした場合に○
  • (*2)クエリエラーにはならないが、結果がおかしい(おそらく、指定できる整数値の最大値に関係する問題)

(5)〜(12)の場合に、DB2 v9.7が不思議な挙動を示しているのだが、それはまた次の機会に(謎)。

DB2

DB2でLIMIT/OFFSETを使用するにはひとつ注意点がある。それは、互換モードをオンにしないと使えないということ。
まずは設定の確認。

db2set DB2_COMPATIBILITY_VECTOR

これで「MYS」が返ってくればOK。あるいは数値が返ってきた場合は16進表記なので、0x4000との論理積が0x4000になればOK。後者については、もっと分かりやすく言うと、下位から4桁目が「4」、「5」、「6」、「7」、「C」、「D」、「E」、「F」のいずれかになっていればOK。
この設定値がセットされていない場合は「DBI1303W Variable not set.」のようなメッセージが返ってくる。
ちなみに、Windows Vista Businessに導入していたDB2 Express-C v9.7.2では「MYS」、Ubuntu 10.04 Serverにaptで導入したDB2 Express-C v9.7.2では未設定だった。
セットする場合は以下のようにする。インスタンスの再起動が必要であることと、インスタンス全体に影響することに注意。

db2set DB2_COMPATIBILITY_VECTOR=MYS
db2stop
db2start

あるいは

db2set DB2_COMPATIBILITY_VECTOR=4000
db2stop
db2start

既に何らかの値がセットされている場合は、必要に応じて、0x4000との論理和を取った値を指定。
ちなみに、LIMIT/OFFSETに関する互換性設定パラメータの設定値(0x4000)について、マニュアルには載っていないらしい。

また、設定値「ORA」(Oracle)、「SYB」(Sybase)に関しては、対応する16進数の値が載っているが、「MYS」(MySQL?)に関しては載っていない。知ったのは以下のブログや記事から(上から順番に発見)。

FETCH FIRST n ROW(S) ONLY

クエリの結果の先頭n行を取得するための構文。SQL2008から標準規格に入ったらしい。

-- (1)
SELECT str FROM hoge ORDER BY str FETCH FIRST 2 ROWS ONLY
-- (2)
SELECT str FROM hoge ORDER BY str FETCH FIRST 2 ROW ONLY

この2者の違いは特になく、英語の文法上の問題だけだろうと。(もっと言うと、既存実装してたDBMSがこうだったから標準規格にもそのまま入ったというところだろうか)
この構文のサポート状況は次の通り。

DBMS (1) (2)
DB2 9.1
DB2 9.5
DB2 9.7
Oracle 10g × ×
PostgreSQL 8.0 × ×
PostgreSQL 8.1 × ×
PostgreSQL 8.2 × ×
PostgreSQL 8.3 × ×
PostgreSQL 8.4
PostgreSQL 9.0
MySQL 4.1 × ×
MySQL 5.0 × ×
MySQL 5.1 × ×
MySQL 5.5 × ×
Firebird 2.1 × ×
Firebird 2.5 × ×
Derby 10.5.3.0
SQLite 3.6.14.2 × ×
SQLServer 2005 × ×
SQLServer 2008 × ×
H2 1.2 × ×
H2 1.3 × ×
HSQLDB 1.8.1 × ×

ROWNUM擬似列

ROWNUMという擬似的な列があるらしい(謎)。

-- (1)
SELECT str FROM (SELECT str FROM hoge ORDER BY str) t1 WHERE ROWNUM <= 2
-- (2)
SELECT str FROM (SELECT str FROM hoge ORDER BY str) t1 WHERE ROWNUM BETWEEN 1 AND 2
-- (3)
SELECT str FROM ( SELECT str, ROWNUM rn FROM ( SELECT str FROM hoge ORDER BY str ) t1 ) t2 WHERE rn BETWEEN 1 AND 2
-- (4)
SELECT str FROM (SELECT str FROM hoge ORDER BY str) t1 WHERE ROWNUM BETWEEN 4 AND 5
-- (5)
SELECT str FROM ( SELECT str, ROWNUM rn FROM ( SELECT str FROM hoge ORDER BY str ) t1 ) t2 WHERE rn BETWEEN 4 AND 5
-- (6)
SELECT str FROM (SELECT str FROM hoge ORDER BY str) t1 WHERE ROWNUM >= 7
-- (7)
SELECT str FROM ( SELECT str, ROWNUM rn FROM ( SELECT str FROM hoge ORDER BY str ) t1 ) t2 WHERE rn >= 7

(1)と(2)と(3)が先頭2行を取るクエリ。(4)と(5)が4行目から2行を取るクエリ。(6)と(7)が7行目以降を取るクエリ。

これら各構文のサポート状況は次の通り。

DBMS (1) (2) (3) (4) (5) (6) (7)
DB2 9.1 × × × × × × ×
DB2 9.5 (*1) (*1) (*1) (*1) (*1) (*1) (*1)
DB2 9.7 (*1) (*1) (*1) (*1) (*1) (*1) (*1)
Oracle 10g (*2) (*2)
PostgreSQL 8.0 × × × × × × ×
PostgreSQL 8.1 × × × × × × ×
PostgreSQL 8.2 × × × × × × ×
PostgreSQL 8.3 × × × × × × ×
PostgreSQL 8.4 × × × × × × ×
PostgreSQL 9.0 × × × × × × ×
MySQL 4.1 × × × × × × ×
MySQL 5.0 × × × × × × ×
MySQL 5.1 × × × × × × ×
MySQL 5.5 × × × × × × ×
Firebird 2.1 × × × × × × ×
Firebird 2.5 × × × × × × ×
Derby 10.5.3.0 × × × × × × ×
SQLite 3.6.14.2 × × × × × × ×
SQLServer 2005 × × × × × × ×
SQLServer 2008 × × × × × × ×
H2 1.2 (*2) (*2) (*2) (*2)
H2 1.3 (*2) (*2)
HSQLDB 1.8.1 × × × × × × ×
  • (*1)後述する互換性オプションを有効にした場合に○
  • (*2)クエリはエラーにならないが、期待する結果は返ってこない
DB2

先の「LIMIT/OFFSET」と同様に、互換性オプションを有効にする必要がある。

db2set DB2_COMPATIBILITY_VECTOR

DB2 v9.7の場合は、これで「ORA」が返ってくればOK。あるいは数値が返ってきた場合は16進表記なので、0x0001との論理積が0x0001になればOK。後者については、もっと分かりやすく言うと、最下位の桁が「1」、「3」、「5」、「7」、「9」、「B」、「D」、「F」のいずれかになっていればOK。

DB2 v9.5の場合は、これで返ってきた数値(16進表記)と0x0001との論理積が0x0001になればOK。(以上、DB2 v9.7での動作から類推。だって、DB2 v9.5のマニュアルに載ってないんですもの)

このROWNUMを使用する方法は、書き方によってはパフォーマンス上の問題を引き起こす可能性があるらしい。

そもそも、DB2におけるROWNUMサポートはOracleとの互換性のためなので、Oracleがサポートしない(期待通りに動作しない)書き方は避けるべきかと。

Oracle

OracleでのROWNUMの挙動は一見すると不思議なのだが、おそらく、(サブ)クエリで返される行に対して1から順に採番するため、例えば「ROWNUM > 1」という条件を書くと、次のように処理されるのではないかと推測している。

  1. ROWNUMに対する条件があるので、最初の行に仮に「ROWNUM = 1」を割り当てるが、条件を満たさないので結果に含めない。
  2. まだ結果として1行も返していないので、2番目の行のROWNUMには「ROWNUM = 1」を割り当てるが、条件を満たさないので結果に含めない。
  3. まだ結果として1行も返していないので、3番目の行の(以下略)


ところで、少し関連するのだが、以下はOracleからPostgreSQLへの移行ということで書かれた記事らしい。

上記の記事を見て、OracleのROWNUM擬似列について学んだし、ROWNUMの扱い方に関して調べるきっかけにもなった。ただ‥

  • 表4の対応表は「ROWNUM←→OFFSET」じゃなくて「ROWNUM←→LIMIT」だよね、っていうのはどうでもいい話(謎)。
  • 「LINE BETWEEN」と書かれているので、Oracleにはそういう構文があるのかと一瞬思ってしまったのだが、誤解を招くのでこういう書き方はやめたほうがいいと思うよ、っていうのもどうでもいい話(更謎)。

ROW_NUMBER() OVER()

ROW_NUMBER()関数はSQL標準規格にも含まれているOLAP関数なのだが、これを使って行番号を自分で振り、これを使って取得する行の指定をしようという発想らしい。

DBMSによっては、アンダースコアなしのROWNUMBER()が同様の機能を持つらしい。

-- (1)
SELECT str FROM (SELECT str, ROW_NUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn <= 2
-- (2)
SELECT str FROM (SELECT str, ROW_NUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn BETWEEN 1 AND 2
-- (3)
SELECT str FROM (SELECT str, ROW_NUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn BETWEEN 4 AND 5
-- (4)
SELECT str FROM (SELECT str, ROW_NUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn >= 7

-- (5)
SELECT str FROM (SELECT str, ROWNUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn <= 2
-- (6)
SELECT str FROM (SELECT str, ROWNUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn BETWEEN 1 AND 2
-- (7)
SELECT str FROM (SELECT str, ROWNUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn BETWEEN 4 AND 5
-- (8)
SELECT str FROM (SELECT str, ROWNUMBER() OVER(ORDER BY str) AS rn FROM hoge) t1 WHERE rn >= 7

(1)〜(4)と(5)〜(8)の違いは、関数名にアンダースコアが入っているかいないかの違い。
この構文のサポート状況は次の通り。

DBMS (1) (2) (3) (4) (5) (6) (7) (8)
DB2 9.1
DB2 9.5
DB2 9.7
Oracle 10g × × × ×
PostgreSQL 8.0 × × × × × × × ×
PostgreSQL 8.1 × × × × × × × ×
PostgreSQL 8.2 × × × × × × × ×
PostgreSQL 8.3 × × × × × × × ×
PostgreSQL 8.4 × × × ×
PostgreSQL 9.0 × × × ×
MySQL 4.1 × × × × × × × ×
MySQL 5.0 × × × × × × × ×
MySQL 5.1 × × × × × × × ×
MySQL 5.5 × × × × × × × ×
Firebird 2.1 × × × × × × × ×
Firebird 2.5 × × × × × × × ×
Derby 10.5.3.0 × × × × × × × ×
SQLite 3.6.14.2 × × × × × × × ×
SQLServer 2005 × × × ×
SQLServer 2008 × × × ×
H2 1.2 × × × × × × × ×
H2 1.3 × × × × × × × ×
HSQLDB 1.8.1 × × × × × × × ×

TOP n

クエリの結果の先頭n行を返す。

実際にはもうちょっと幅広い機能があるらしいが、直接関係ないので触れないことにする。
このTOPを使用したTips的クエリがあるらしいので、ついでに試してみる。(keyColumnが存在し、order clauseでのソート結果がユニークになることが要求されるが)

-- (1)
SELECT TOP(2) str FROM hoge ORDER BY str
-- (2)
SELECT TOP 2 str FROM hoge ORDER BY str
-- (3)
SELECT TOP(2) str FROM hoge WHERE str NOT IN (SELECT TOP(3) str FROM hoge ORDER BY str) ORDER BY str
-- (4)
SELECT TOP 2 str FROM hoge WHERE str NOT IN (SELECT TOP 3 str FROM hoge ORDER BY str) ORDER BY str
-- (5)
SELECT str FROM hoge WHERE str NOT IN (SELECT TOP(6) str FROM hoge ORDER BY str) ORDER BY str
-- (6)
SELECT str FROM hoge WHERE str NOT IN (SELECT TOP 6 str FROM hoge ORDER BY str) ORDER BY str

この構文のサポート状況は次の通り。

DBMS (1) (2) (3) (4) (5) (6)
DB2 9.1 × × × × × ×
DB2 9.5 × × × × × ×
DB2 9.7 × × × × × ×
Oracle 10g × × × × × ×
PostgreSQL 8.0 × × × × × ×
PostgreSQL 8.1 × × × × × ×
PostgreSQL 8.2 × × × × × ×
PostgreSQL 8.3 × × × × × ×
PostgreSQL 8.4 × × × × × ×
PostgreSQL 9.0 × × × × × ×
MySQL 4.1 × × × × × ×
MySQL 5.0 × × × × × ×
MySQL 5.1 × × × × × ×
MySQL 5.5 × × × × × ×
Firebird 2.1 × × × × × ×
Firebird 2.5 × × × × × ×
Derby 10.5.3.0 × × × × × ×
SQLite 3.6.14.2 × × × × × ×
SQLServer 2005
SQLServer 2008
H2 1.2
H2 1.3 × × × ×
HSQLDB 1.8.1

H2 v1.2とv1.3の挙動の違いが謎。

FIRST/SKIP

他にないかな、とそれっぽいキーワードで探して見つけたWikipediaの情報から、調べてみた。

-- (1)
SELECT FIRST 2 str FROM hoge ORDER BY str
-- (2)
SELECT FIRST 2 SKIP 3 str FROM hoge ORDER BY str
-- (3)
SELECT SKIP 3 FIRST 2 str FROM hoge ORDER BY str
-- (4)
SELECT SKIP 6 str FROM hoge ORDER BY str
-- (5)
SELECT FIRST 18446744073709551615 SKIP 6 str FROM hoge ORDER BY str
-- (6)
SELECT FIRST 9223372036854775807 SKIP 6 str FROM hoge ORDER BY str
-- (7)
SELECT FIRST 4294967295 SKIP 6 str FROM hoge ORDER BY str
-- (8)
SELECT FIRST 2147483647 SKIP 6 str FROM hoge ORDER BY str

FIRSTとSKIPが逆のケースである(3)も、Firebirdではサポートされていないっぽいが、一応試してみた。
また、(5)〜(8)で指定しているでっかい数値(何)は、符号なし64ビット整数の最大、符号付き64ビット整数の最大、符号なし32ビット整数の最大、符号付き32ビット整数の最大。
この構文のサポート状況は次の通り。

DBMS (1) (2) (3) (4) (5) (6) (7) (8)
DB2 9.1 × × × × × × × ×
DB2 9.5 × × × × × × × ×
DB2 9.7 × × × × × × × ×
Oracle 10g × × × × × × × ×
PostgreSQL 8.0 × × × × × × × ×
PostgreSQL 8.1 × × × × × × × ×
PostgreSQL 8.2 × × × × × × × ×
PostgreSQL 8.3 × × × × × × × ×
PostgreSQL 8.4 × × × × × × × ×
PostgreSQL 9.0 × × × × × × × ×
MySQL 4.1 × × × × × × × ×
MySQL 5.0 × × × × × × × ×
MySQL 5.1 × × × × × × × ×
MySQL 5.5 × × × × × × × ×
Firebird 2.1 × × × ×
Firebird 2.5 × × × ×
Derby 10.5.3.0 × × × × × × × ×
SQLite 3.6.14.2 × × × × × × × ×
SQLServer 2005 × × × × × × × ×
SQLServer 2008 × × × × × × × ×
H2 1.2 × × × × × × × ×
H2 1.3 × × × × × × × ×
HSQLDB 1.8.1 × × × × × × × ×

ROWS/TO

同じく、他にないかな、とそれっぽいキーワードで探して見つけたWikipediaの情報から、調べてみた。

-- (1)
SELECT str FROM hoge ORDER BY str ROWS 2
-- (2)
SELECT str FROM hoge ORDER BY str ROWS 1 TO 2
-- (3)
SELECT str FROM hoge ORDER BY str ROWS 4 TO 5
-- (4)
SELECT str FROM hoge ORDER BY str ROWS 7 TO 18446744073709551615
-- (5)
SELECT str FROM hoge ORDER BY str ROWS 7 TO 9223372036854775807
-- (6)
SELECT str FROM hoge ORDER BY str ROWS 7 TO 4294967295
-- (7)
SELECT str FROM hoge ORDER BY str ROWS 7 TO 2147483647

また、(4)〜(7)で指定しているでっかい数値(何)は、符号なし64ビット整数の最大、符号付き64ビット整数の最大、符号なし32ビット整数の最大、符号付き32ビット整数の最大。
この構文のサポート状況は次の通り。

DBMS (1) (2) (3) (4) (5) (6) (7)
DB2 9.1 × × × × × × ×
DB2 9.5 × × × × × × ×
DB2 9.7 × × × × × × ×
Oracle 10g × × × × × × ×
PostgreSQL 8.0 × × × × × × ×
PostgreSQL 8.1 × × × × × × ×
PostgreSQL 8.2 × × × × × × ×
PostgreSQL 8.3 × × × × × × ×
PostgreSQL 8.4 × × × × × × ×
PostgreSQL 9.0 × × × × × × ×
MySQL 4.1 × × × × × × ×
MySQL 5.0 × × × × × × ×
MySQL 5.1 × × × × × × ×
MySQL 5.5 × × × × × × ×
Firebird 2.1 ×
Firebird 2.5 ×
Derby 10.5.3.0 × × × × × × ×
SQLite 3.6.14.2 × × × × × × ×
SQLServer 2005 × × × × × × ×
SQLServer 2008 × × × × × × ×
H2 1.2 × × × × × × ×
H2 1.3 × × × × × × ×
HSQLDB 1.8.1 × × × × × × ×