PostgreSQLとOracleによるデータベース相互移行マニュアル第4回

SQL文の移行（1）

結果セットにおける行番号の指定 Oracleでは、結果セットの特定の行だけを表示するために、ROWNUM疑似列およびLINEBETWEEN条件が使えます。次のSQL文はROWNUMを用いた例です。SELECT * FROM 商品マスタ WHERE 卸単価 IS NOT NULL AND R

奥畑裕樹

2005年10月27日 20:00

結果セットにおける行番号の指定

Oracleでは、結果セットの特定の行だけを表示するために、ROWNUM疑似列およびLINEBETWEEN条件が使えます。次のSQL文はROWNUMを用いた例です。

SELECT * FROM 商品マスタ
  WHERE 卸単価 IS NOT NULL
  AND ROWNUM = 5
  ORDER BY 卸単価 DESC;

PostgreSQLには、等価な機能を実現できるOFFSET、LIMITがあるので、表4のような組み合わせで変換します。

Oracle		PostgreSQL
ROWNUM	←→	OFFSET
LINE BETWEEN	←→	OFFSET + LIMIT

表4：特定行の表示機能の変換

ただし、PostgreSQLのLIMIT、OFFSETはWHERE句の一部ではないので、ROWNUMを削除するだけではなく、WHERE句に条件の指定がない場合には、WHERE句も削除する必要があるので注意が必要です。

SELECT * FROM 商品マスタ
   WHERE 卸単価 IS NOT NULL
   ORDER BY 卸単価 DESC
   LIMIT 5 OFFSET 0;

図4は、このSQL文の実行結果です。

商品id	\|	商品名	\|	グループ名	\|	仕入単価	\|	卸単価
--------	+	------------------------	+	--------------	+	----------	+	--------
2	\|	ノート型パソコン	\|	パソコン本体	\|	230000	\|	270000
1	\|	デスクトップ型パソコン	\|	パソコン本体	\|	150000	\|	180000
5	\|	15型_液晶ディスプレイ	\|	周辺機器	\|	100000	\|	120000
4	\|	19型_ディスプレイ	\|	周辺機器	\|	80000	\|	95000
3	\|	17型_ディスプレイ	\|	周辺機器	\|	40000	\|	50000
(5 rows)

図4：「LIMIT/OFFSET」を利用した実行結果

OFFSET + LIMIT を LINE BETWEENで実現する

例えば、下記のようなPostgreSQL用のクエリがあったとします。これは、結果を2行捨てて3行目から3行分表示させるものです。

SELECT 商品ID, 商品名, 卸単価 FROM 商品マスタ
     WHERE 卸単価 IS NOT NULL
     ORDER BY 卸単価 DESC
     LIMIT 3 OFFSET 2;

Oracleで同じことをするには、下記のようにROWNUM擬似列を使い、lineのような別名の列にし、それをBETWEEN句で絞る方法が使えます。

SELECT *
     FROM (SELECT 商品ID, 商品名, 卸単価, ROWNUM line
          FROM (SELECT * FROM 商品マスタ
               WHERE 卸単価 IS NOT NULL
               ORDER BY 卸単価 DESC ))
     WHERE line BETWEEN 3 AND 5;

上記のテクニックは、若干「むりやり感」がありますが、これは下記のように単純にROWNUMをBETWEEN句で用いてしまうと、期待する結果が得られないためです。

SELECT * FROM 商品マスタ
     WHERE 卸単価 IS NOT NULL
          AND ROWNUM BETWEEN 3 AND 5
     ORDER BY 卸単価 DESC

レコードが選択されませんでした。

アウタージョインの構文の違い

Oracle、PostgreSQLともにOUTER JOINの機能がありますが、構文が違いますのでOracleの(+)を使った記法を次のように変更する必要があります。

Oracleのアウタージョインの構文

SELECT B.商品名, SUM(A.数量)
  FROM 売上データA, 商品マスタB
  WHERE A.商品ID (+)= B.商品ID
  GROUP BY B.商品名;

PostgreSQLのアウタージョインの構文

SELECT B.商品名,
         SUM(A.数量) AS "SUM(A.数量)"
    FROM 売上データA
         RIGHT OUTER JOIN
         商品マスタB
ON A.商品ID = B.商品ID
    GROUP BY B.商品名;

図5はその実行結果です。

商品名	\|	SUM(A.数量)
--------------------------------	+	-------------
15型_液晶ディスプレイ	\|	6
17型_ディスプレイ	\|	5
19型_ディスプレイ	\|	3
HUB	\|	5
スキャナ	\|	1
ターミナルアダプタ	\|	2
デジタルカメラ	\|
デスクトップ型パソコン	\|	6
ノート型パソコン	\|	8
プリンタ	\|	1
(10 rows)