PostgreSQL BETWEENを使った範囲検索

By /

PostgreSQLで賢く範囲検索：BETWEEN演算子の徹底解説

リレーショナルデータベースを扱う上で、特定の条件に合致するデータを絞り込むことは最も基本的な操作の一つです。中でも「範囲検索」は、数値、日付、文字列など、さまざまなデータ型に対して頻繁に行われます。PostgreSQLでは、この範囲検索を簡潔に記述するための便利な演算子としてBETWEENが用意されています。

この記事では、PostgreSQLにおけるBETWEEN演算子の基本的な使い方から、その詳細な挙動、知っておくべき注意点、さらには他の範囲指定方法との比較やパフォーマンスに関する考察まで、包括的に解説します。この記事を読むことで、あなたはPostgreSQLでより正確かつ効率的な範囲検索を行うための知識とスキルを習得できるでしょう。

1. はじめに：なぜ範囲検索が重要なのか

データベースに蓄積されたデータは膨大であり、その全てを一度に参照することは稀です。通常、特定の条件を満たすデータだけを取り出して分析したり、集計したり、表示したりします。この「特定の条件」として、データの「範囲」を指定するケースは非常に多いです。

  • 「過去1ヶ月間の売上データ」
  • 「価格が1000円から5000円までの商品」
  • 「特定のアルファベットで始まる顧客名」
  • 「年齢が20歳以上30歳以下のユーザー」

これらの要求に応えるためには、データベースに対して適切な範囲検索クエリを発行する必要があります。PostgreSQLには、このような範囲指定を行うための複数の方法がありますが、中でもBETWEEN演算子は直感的で理解しやすい構文を提供します。

2. PostgreSQLにおける範囲検索の基本

BETWEEN演算子の解説に入る前に、PostgreSQLで範囲を指定する際の基本的な考え方を確認しておきましょう。範囲検索は主にSELECT文のWHERE句で行われます。WHERE句には、真偽値を返す条件式を記述し、その条件式が真となる行のみが結果として返されます。

最も原始的な範囲指定の方法は、比較演算子（<, >, <=, >=）と論理演算子（AND）を組み合わせるものです。

例えば、「価格が1000円から5000円までの商品」を検索する場合、価格カラム（仮にpriceとする）に対して以下のような条件を指定できます。

sql
SELECT product_name, price
FROM products
WHERE price >= 1000.00 AND price <= 5000.00;

この例では、priceが1000.00以上（>=）であり、かつ、5000.00以下（<=）である行を選択しています。このように、上限と下限を含む範囲を指定する場合、2つの比較演算子とANDを組み合わせる必要があります。

これはこれで正確な方法ですが、特に日付や数値の範囲検索では、このパターンが頻繁に登場します。このような定型的な範囲指定をより簡潔に記述するために、PostgreSQLはBETWEEN演算子を提供しています。

3. BETWEEN演算子とは

BETWEEN演算子は、ある値が指定した範囲内に含まれるかどうかを判定するための演算子です。その基本的な構文は以下のようになります。

sql
expr BETWEEN lower_bound AND upper_bound

ここで：
* expr: 評価したい式またはカラムの名前です。
* lower_bound: 範囲の下限値です。
* upper_bound: 範囲の上限値です。
* AND: BETWEEN演算子の一部として、下限と上限を区切るキーワードです。論理演算子のANDとは異なります。

BETWEEN演算子は、包括的（inclusive）な範囲を指定します。これは、exprの値がlower_boundまたはupper_boundと等しい場合も範囲に含まれることを意味します。

つまり、上記の構文は以下の論理式と完全に等価です。

sql
expr >= lower_bound AND expr <= upper_bound

これがBETWEEN演算子の最も重要な特性であり、理解の核となります。

3.1. 様々なデータ型への適用

BETWEEN演算子は、PostgreSQLが比較可能な様々なデータ型に適用できます。主に数値型、日付/時刻型、そして文字列型に対して使用されます。

数値型:

数値の範囲検索は最も一般的なケースです。

sql
-- 価格が50.00から300.00の間の商品を選択（50.00と300.00を含む）
SELECT product_name, price
FROM products
WHERE price BETWEEN 50.00 AND 300.00;

このクエリは、price >= 50.00 AND price <= 300.00 と等価です。

日付/時刻型:

特定の日付や時刻の範囲内にあるデータを検索する際にもBETWEENは非常に役立ちます。

sql
-- 2023年3月1日から2023年5月31日までの注文を選択（両端の日付を含む）
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-03-01' AND '2023-05-31';

日付/時刻型に関しては、後述する「詳細な挙動と注意点」のセクションでさらに詳しく掘り下げます。特に時刻情報を持つカラムに対して日付のみでBETWEENを使用する際には注意が必要です。

文字列型:

文字列型に対してもBETWEENは使用できます。この場合、文字列は辞書順（文字コード順やロケールに基づく比較順）で比較されます。

sql
-- 'M'から'P'までのアルファベットで始まる商品名を選択（辞書順で'M'以上'P'以下）
SELECT product_name
FROM products
WHERE product_name BETWEEN 'M' AND 'Pz'; -- 注意：文字列の終端は複雑
文字列の終端に関する注意点: 文字列の範囲指定は、数値や日付のように明確な「次の値」が存在しないため、終端の指定が少し複雑になることがあります。例えば、BETWEEN 'A' AND 'C' は ‘A…’, ‘B…’ といった文字列を含みますが、正確に ‘C’ で始まる文字列をどこまで含むかは、比較される文字列の長さや種類に依存します。一般的には、'C' のような単一文字を上限にした場合、その文字で「始まる」全ての文字列は含まれません（辞書順でその文字の直後にある値は含まれないため）。特定の文字で始まる文字列全てを含めたい場合は、上限をその文字に続く文字（例えば'D'）を指定し、BETWEEN 'A' AND 'D'とするか、またはLIKE 'A%' OR LIKE 'B%' OR LIKE 'C%'のように指定する方が意図が明確になることが多いです。あるいは、カラム >= 'A' AND カラム < 'D'のような非包括的な表現を使う方がより正確な制御が可能です。そのため、文字列に対するBETWEENは数値や日付ほど頻繁には使われないかもしれません。

4. BETWEEN演算子の詳細な挙動と注意点

BETWEEN演算子は簡潔で便利ですが、その詳細な挙動やいくつかの注意点を知っておくことは、正確なクエリを書く上で不可欠です。

4.1. 包括性（Inclusive）の再確認

前述の通り、BETWEENは包括的です。expr BETWEEN lower_bound AND upper_boundは、expr >= lower_bound AND expr <= upper_bound と等価です。これは、範囲の下限値と上限値そのものも検索結果に含まれることを意味します。

例えば、BETWEEN 1 AND 5 は 1, 2, 3, 4, 5 を含みます。
BETWEEN '2023-01-01' AND '2023-01-31' は 2023年1月1日のデータと2023年1月31日のデータを含みます。

この「包括的」であるという性質は、BETWEENを使う上で常に意識しておく必要があります。もし、上限や下限を含まない「非包括的（exclusive）」な範囲を指定したい場合は、BETWEENではなく比較演算子（<, >）を使用する必要があります。

例：「価格が1000円より大きく、5000円より小さい商品」

sql
SELECT product_name, price
FROM products
WHERE price > 1000.00 AND price < 5000.00;
この場合、BETWEENで直接表現することはできません。

4.2. AND演算子との関係

BETWEEN演算子は、内部的には前述のように>=, <=, ANDの組み合わせに変換されて評価されます。オプティマイザは通常、この変換を認識し、どちらの形式で記述しても同じ実行計画を生成することが期待できます。したがって、パフォーマンスの観点から見れば、通常はBETWEENと>= ... AND <= ...の間に大きな差はありません。どちらを使うかは、主にコードの可読性や記述の簡潔さで判断して構いません。

4.3. データ型の互換性

BETWEEN演算子を使用する場合、expr、lower_bound、upper_boundのデータ型は互換性がある必要があります。PostgreSQLは、必要に応じて暗黙的な型変換を試みますが、意図しない結果を避けるためには、明示的に型を指定するか、互換性のあるデータ型を使用することが推奨されます。

例えば、INTEGER型のカラムに対してBETWEEN 1.5 AND 3.5のような条件を指定した場合、境界値が整数に丸められるか、カラムの値が浮動小数点数に変換されるかといった挙動は、データ型やPostgreSQLのバージョン、設定によって異なる可能性があります。一般的には、カラムのデータ型に合わせたリテラルを使用するのが最も安全です。

4.4. 境界値の順序

BETWEEN演算子では、lower_boundは必ずupper_boundより小さい（または等しい）値でなければならないのでしょうか？

構文上は expr BETWEEN lower_bound AND upper_bound と記述しますが、PostgreSQLでは lower_bound と upper_bound の大小関係は厳密にチェックされません。

しかし、BETWEENが expr >= lower_bound AND expr <= upper_bound と等価であることを思い出してください。

もし lower_bound が upper_bound より大きい場合、例えば expr BETWEEN 10 AND 5 のような条件は、expr >= 10 AND expr <= 5 と評価されます。数値 expr が同時に10以上であり、かつ、5以下であるという条件を満たすことは不可能です（10より大きい値は必ず5より大きいため）。

したがって、lower_bound > upper_bound となる BETWEEN 条件は、常に偽（False）を返します。これはエラーにはなりませんが、結果セットは必ず空になります。

例：

sql
-- 価格が300.00から50.00の間の商品を選択（意図的に境界値を逆転）
SELECT product_name, price
FROM products
WHERE price BETWEEN 300.00 AND 50.00;

このクエリは、WHERE price >= 300.00 AND price <= 50.00 と等価になり、結果として何も返しません。

正確な範囲検索を行うためには、常に lower_bound <= upper_bound となるように値を指定すべきです。意図せず境界値を逆にしてしまうと、エラーにならないために間違いに気づきにくくなる可能性があるため、注意が必要です。

4.5. NOT BETWEEN演算子

BETWEEN演算子には否定形であるNOT BETWEENも存在します。これは、ある値が指定した範囲内に含まれないかどうかを判定します。

sql
expr NOT BETWEEN lower_bound AND upper_bound

この構文は以下の論理式と完全に等価です。

sql
expr < lower_bound OR expr > upper_bound

つまり、範囲の下限値より小さいか、または、上限値より大きい場合に真となります。BETWEENと同様に、境界値そのものは範囲に含まれません（否定形なので、境界値に等しい場合は偽となります）。

例：価格が50.00から300.00の間にない商品を選択

sql
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 50.00 AND 300.00;

このクエリは、価格が50.00より小さいか、または、300.00より大きい商品を返します。これは WHERE price < 50.00 OR price > 300.00 と等価です。

NOT BETWEENは、特定の範囲外のデータを簡単に取得したい場合に便利です。

5. BETWEEN演算子の具体的な使用例

ここからは、より具体的なシナリオでBETWEEN演算子の使い方を見ていきましょう。

サンプルとして使用するテーブル構造と基本的なデータは以下の通りです（前述の例と同じ）。

“`sql
— 商品テーブル
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

— 注文テーブル
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_id INT REFERENCES products(product_id),
order_date DATE NOT NULL,
quantity INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL
);

— データ挿入 (省略 – 前述の例を参照)
“`

5.1. 数値範囲の検索例

例1: 特定の価格帯の商品を検索

価格が100ドル以上500ドル以下の商品を検索します。

sql
SELECT product_name, price
FROM products
WHERE price BETWEEN 100.00 AND 500.00;

例2: 在庫数が特定の範囲にある商品を検索

在庫数が50個以上150個以下の商品を検索します。

sql
SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity BETWEEN 50 AND 150;

5.2. 日付/時刻範囲の検索例

日付/時刻の範囲検索は、多くの業務システムで頻繁に行われます。BETWEENはこれを簡潔に記述できます。

例1: 特定の月内の注文を検索

2023年4月中の全ての注文を検索します。

sql
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-04-01' AND '2023-04-30';
この例では、日付型カラムに対して日付リテラルを使用しています。日付リテラルは通常 'YYYY-MM-DD' 形式で指定します。PostgreSQLはこれをdate型として解釈します。

例2: 日付型カラムと時刻型カラム

ordersテーブルのorder_dateはDATE型です。もしカラムがTIMESTAMPやTIMESTAMPTZ型で、時刻情報を持っている場合は、BETWEENの挙動に注意が必要です。

例えば、productsテーブルのcreated_atカラムはTIMESTAMP WITH TIME ZONE型です。これに対して日付のみでBETWEENを使用してみます。

sql
-- 2023年4月1日中に作成された商品を検索 (created_atはTIMESTAMP型)
SELECT product_name, created_at
FROM products
WHERE created_at BETWEEN '2023-04-01' AND '2023-04-01';

このクエリは、created_at >= '2023-04-01' AND created_at <= '2023-04-01' と等価です。PostgreSQLでは、時刻情報を持たない日付リテラル（例: '2023-04-01'）がTIMESTAMPやTIMESTAMPTZと比較される際、時刻部分はデフォルトで00:00:00と解釈されます。

したがって、上記のクエリは実質的に以下と同じです。

sql
SELECT product_name, created_at
FROM products
WHERE created_at >= '2023-04-01 00:00:00' AND created_at <= '2023-04-01 00:00:00';
これにより、created_atの値が厳密に2023-04-01 00:00:00である行しか返されません。通常、ユーザーが「2023年4月1日中」という場合、その日の0時0分0秒から23時59分59.999…秒までを含意していることが多いでしょう。しかし、上記のBETWEENの使い方はその意図を満たしません。

日付/時刻の範囲検索の落とし穴と安全な書き方:

時刻情報を持つカラムに対して、ある「日付」の範囲を指定したい場合は、BETWEENの終端の挙動に注意が必要です。終端の時刻情報が00:00:00と解釈される問題を回避するには、いくつかの方法があります。

方法1: 終端の日付の時刻部分を明示的に指定

sql
-- 2023年4月1日中の作成商品を検索 (終端の時刻を指定)
SELECT product_name, created_at
FROM products
WHERE created_at BETWEEN '2023-04-01 00:00:00' AND '2023-04-01 23:59:59.999999';
これは正確ですが、終端の時刻を手動で指定する必要があり、わずらわしい場合があります。特にミリ秒以下の精度をどこまで指定するかは、カラムのデータ型や要件によります。

方法2: 非包括的な比較演算子を使用

ある日付の範囲を「その日の始まりから、次の日の始まりの直前まで」と定義する方法です。これは多くのプログラミング言語やデータベースシステムで推奨されるパターンです。

sql
-- 2023年4月1日中の作成商品を検索 (非包括的な比較)
SELECT product_name, created_at
FROM products
WHERE created_at >= '2023-04-01' AND created_at < '2023-04-02';
この方法では、下限は包括的（>=）に、上限は非包括的（<）に指定します。これにより、’2023-04-01 00:00:00′ から ‘2023-04-01 23:59:59.999…’ までの範囲が正確にカバーされ、’2023-04-02 00:00:00′ は含まれません。このパターンは、日付/時刻の範囲検索において最も一般的で安全な方法の一つとされています。BETWEENの包括性とは異なるため、BETWEENを使う場合はこの書き方には馴染みません。

方法3: 範囲型を使用する (後述)

PostgreSQL独自の範囲型を使用すると、日付/時刻の範囲操作がよりシンプルかつ強力に行えます。

日付/時刻の範囲検索でBETWEENを使用する場合は、特に終端の時刻情報に十分注意し、意図した範囲が正確に指定できているかを確認してください。日付型カラムに対する日付リテラルのBETWEENは期待通りに動作しますが、時刻情報を持つカラムに対する日付リテラルのBETWEENは期待と異なる結果を招く可能性が高いです。

5.3. 文字列範囲の検索例

文字列に対するBETWEENは、辞書順比較に基づいて動作します。

例1: 特定のアルファベット範囲で始まる商品名を検索

商品名が ‘L’ から ‘M’ の範囲にあるものを検索します。

sql
SELECT product_name
FROM products
WHERE product_name BETWEEN 'L' AND 'M'; -- これは 'L' から 'M' までの範囲
この例では、'L'と'M'の間の辞書順の値が含まれます。例えば、’Laptop’ は含まれますが、’Monitor’ は含まれません（’M’の次に来るため）。

より具体的に、「’L’ または ‘M’ で始まる商品名」を検索したい場合は、LIKE演算子を使用するか、以下のように範囲を調整する必要があります。

sql
-- 'L' または 'M' で始まる商品名
SELECT product_name
FROM products
WHERE product_name BETWEEN 'L' AND 'Mz'; -- 'M'で始まるものを含めるための一般的な手法
-- または
SELECT product_name
FROM products
WHERE product_name >= 'L' AND product_name < 'N'; -- 非包括的な比較
-- または
SELECT product_name
FROM products
WHERE product_name LIKE 'L%' OR product_name LIKE 'M%'; -- 最も意図が明確な場合が多い
文字列の終端を指定する際は、その文字の次の文字（またはその文字で始まる全ての文字列より辞書順で大きい値）を上限として非包括的な比較を行うのが安全です。BETWEENを使う場合は、慣習的に上限の末尾に辞書順で最後の文字（例: zや~）を付加する手法が使われることがありますが、これは特定のエンコーディングやロケールに依存する可能性があり、非包括的な比較 (>= 'lower' AND < 'upper_next') の方がより確実です。

文字列に対するBETWEENは、数値や日付ほど直感的でない場合があるため、使用する際は結果が期待通りになるか十分に確認する必要があります。多くの場合、LIKE演算子や正規表現 (~) を使う方が、文字列のパターンマッチングにおいてはより柔軟で意図を明確に表現できます。

6. BETWEEN演算子と他の範囲指定方法の比較

PostgreSQLで範囲検索を行う方法はBETWEENだけではありません。比較演算子や、PostgreSQL独自の範囲型も利用できます。それぞれの方法には利点と欠点があり、状況に応じて最適な方法を選択することが重要です。

6.1. 比較演算子 (>=, <=) と AND の組み合わせ vs BETWEEN

前述の通り、expr BETWEEN lower_bound AND upper_bound は expr >= lower_bound AND expr <= upper_bound と論理的に等価です。

BETWEENの利点:

  • 簡潔な構文: 特に包括的な範囲を指定する場合、BETWEENを使うことでクエリが短く、読みやすくなります。WHERE column BETWEEN val1 AND val2 は WHERE column >= val1 AND column <= val2 よりも記述量が少なく、意図が把握しやすいです。

比較演算子 + ANDの利点:

  • 柔軟性: 非包括的な範囲（例: > lower_bound AND < upper_bound）、片側だけの範囲（例: > some_value）、または上限・下限のどちらか一方のみを含む範囲（例: > lower_bound AND <= upper_bound）など、より多様な範囲条件を直接的に表現できます。BETWEENは常に包括的です。
  • 明示性: >=と<=を明示的に使うことで、条件の包括性・非包括性が明確になります。特に日付/時刻の終端の問題を回避したい場合に、>= start_date AND < end_date + interval '1 day' のように非包括的な上限を指定するパターンが一般的であり、BETWEENではこのような表現はできません。

結論:

  • 包括的な範囲（両端を含む）をシンプルに表現したい場合は、BETWEENが適しています。
  • 非包括的な範囲や、より複雑な境界条件を指定したい場合は、比較演算子 (<, >, <=, >=) と AND/OR を組み合わせる必要があります。
  • 日付/時刻の範囲検索では、特に時刻情報を含むカラムに対しては、BETWEENの終端の挙動に注意が必要であり、>= start_datetime AND < end_datetime_plus_epsilon のような非包括的なアプローチが安全な場合が多いです。

パフォーマンスに関しては、オプティマイザがこれらの同等性を認識するため、大きな差は生じにくいです。どちらの書き方でも、適切にインデックスが利用されれば効率的な検索が可能です（インデックスについては後述）。

6.2. 範囲型 (Range Types) と BETWEEN

PostgreSQLには、数値や日付/時刻などの連続したデータの範囲を表現するための「範囲型（Range Types）」という独自の強力な機能があります。例えば、int4range (32-bit integerの範囲)、int8range (64-bit integerの範囲)、numrange (数値の範囲)、daterange (日付の範囲)、tsrange (タイムスタンプの範囲)、tstzrange (タイムスタンプとタイムゾーンの範囲) などがあります。

範囲型は、テーブルのカラムのデータ型として使用したり、クエリ内で一時的に範囲値を扱ったりすることができます。範囲型を使用すると、範囲同士の比較や演算（包含、重複、結合など）を専用の演算子 (@>, <@, &&など) を使って直感的に行うことができます。

範囲型の例:

daterange 型のカラムを持つイベントテーブルを考えます。

“`sql
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
event_name VARCHAR(100),
event_period daterange — イベント期間を daterange で保持
);

INSERT INTO events (event_name, event_period) VALUES
(‘Conference’, ‘[2024-07-10, 2024-07-12]’::daterange), — 7/10 から 7/12 (両端含む)
(‘Workshop’, ‘[2024-08-01, 2024-08-02)’::daterange), — 8/1 (含む) から 8/2 (含まない)
(‘Meetup’, ‘[2024-09-05, 2024-09-05]’::daterange); — 9/5 の一日 (両端含む)
``
範囲リテラルは[lower, upper](両端含む),[lower, upper)(下限含む, 上限含まない),(lower, upper](下限含まない, 上限含む),(lower, upper)` (両端含まない) といった形式で指定できます。

範囲型とBETWEENの比較:

  • BETWEEN:
    • 単一の値 (expr) が、固定された下限 (lower_bound) と上限 (upper_bound) で定義される静的な範囲に含まれるかを判定するのに適しています。
    • 構文がシンプルで、多くのデータ型に標準的に対応しています。
  • 範囲型:
    • 範囲そのものをデータとして扱いたい場合に非常に強力です。
    • ある範囲が別の範囲に含まれるか (range1 @> range2)、重複しているか (range1 && range2)、特定の範囲に特定の時点が含まれるか (range @> value) といった、より複雑な範囲関係を表現するのに適しています。
    • テーブル設計を範囲型に対応させる必要があります。

使い分け:

  • あるカラムの値が「特定の静的な数値範囲」や「特定の固定期間」に収まっているかを検索するだけであれば、BETWEENを使うのが最も手軽です。
  • データの性質が「期間」や「範囲」そのものであり、それらの範囲同士を比較・演算したり、範囲に対してインデックスを効率的に利用したりしたい場合は、範囲型を導入することを検討すべきです。例えば、会議室の予約システムで利用時間の重複をチェックする場合などです。

範囲型は高度な機能ですが、日付/時刻のような連続したデータの範囲検索においては、BETWEENや比較演算子では煩雑になりがちなロジックをシンプルに記述できる大きなメリットがあります。特に、時刻情報を含むtimestampやtimestamptzに対する範囲型 (tsrange, tstzrange) は、前述の「時刻の落とし穴」問題を回避しやすく、安全な範囲検索を実現します。

sql
-- tsrange を使用して、2023-04-01 の「一日中」に created_at が含まれる商品を検索
-- (created_at は TIMESTAMPTZ 型と仮定)
SELECT product_name, created_at
FROM products
WHERE tstzrange('2023-04-01 00:00:00+09', '2023-04-02 00:00:00+09', '[)') @> created_at;
-- または、日付型から範囲を生成
SELECT product_name, created_at
FROM products
WHERE daterange('2023-04-01'::date, '2023-04-02'::date, '[)') @> created_at::date; -- created_at の日付部分のみで比較する場合
-- または、よりシンプルに日付としてキャストして比較 (BETWEENでも可能だが、範囲型の方が意図が明確)
SELECT product_name, created_at
FROM products
WHERE created_at::date BETWEEN '2023-04-01'::date AND '2023-04-01'::date; -- これは 2023-04-01 00:00:00 ～ 2023-04-01 23:59:59.999... を含む
このように、範囲型を使うと日付/時刻の範囲指定が直感的になる場合があります。ただし、created_at が timestamptz 型の場合、タイムゾーンを考慮した範囲指定が必要になります。

7. BETWEEN演算子とインデックス

データベースの検索パフォーマンスは、インデックスの利用状況に大きく依存します。WHERE句で指定する条件に適切なインデックスが存在する場合、データベースシステムは全件スキャンを行うことなく、インデックスを使って効率的に対象の行を特定できます。

BETWEEN演算子は、PostgreSQLで広く使われているB-treeインデックスを効率的に利用することができます。

expr BETWEEN lower_bound AND upper_bound という条件は、expr >= lower_bound AND expr <= upper_bound に書き換えられて評価されることを思い出してください。

B-treeインデックスは、データをソートされた順序で保持しており、特定の範囲の値を高速に検索するのに非常に適しています。>= lower_bound の条件は、インデックス上でlower_bound以上の最初のキーを見つけるために利用でき、<= upper_bound の条件は、スキャンをどこまで行うかを決定するために利用できます。

PostgreSQLのクエリ実行計画を確認するためのEXPLAINコマンドを使うと、クエリがインデックスをどのように利用しているかを確認できます。

例：productsテーブルのpriceカラムにインデックスがある場合

“`sql
— price カラムにインデックスを作成
CREATE INDEX idx_products_price ON products (price);

— BETWEENを使ったクエリの実行計画を確認
EXPLAIN SELECT product_name, price
FROM products
WHERE price BETWEEN 100.00 AND 500.00;
“`

EXPLAINの出力に Index Scan や Bitmap Index Scan といった行が含まれていれば、インデックスが利用されている可能性が高いです。Index Scan はインデックスを直接スキャンして行を取得する方式、Bitmap Index Scan はインデックスを使って対象行のビットマップを作成し、そのビットマップを使ってテーブルから行を取得する方式です。どちらも全件スキャン（Seq Scan）より高速であることが期待されます。

インデックスが有効に利用されるための条件:

  1. 対象カラムにインデックスが存在する: WHERE句の条件で使われているカラムに適切なインデックス（通常はB-tree）が作成されている必要があります。
  2. インデックスがスキャンに適している: B-treeインデックスは範囲検索に最も適したインデックスタイプの一つです。
  3. 条件がインデックスを利用できる形である: BETWEENや比較演算子の組み合わせはB-treeインデックスのスキャンに適しています。ただし、カラムに関数を適用したり（例: WHERE some_function(column) BETWEEN ...）、複雑な式の一部としてカラムを使用したりする場合は、通常インデックスは利用されません。この場合、関数インデックスを検討する必要があるかもしれません。
  4. 統計情報が最新である: PostgreSQLのクエリオプティマイザは、統計情報（テーブルやインデックスのデータ分布に関する情報）を元に最適な実行計画を決定します。統計情報が古いと、オプティマイザがインデックスを使うべきだと判断できなかったり、非効率なインデックススキャンを選択したりする可能性があります。定期的にANALYZEコマンドを実行して統計情報を更新することが重要です。

NOT BETWEENとインデックス:

NOT BETWEEN演算子 (expr NOT BETWEEN lower_bound AND upper_bound) は、expr < lower_bound OR expr > upper_bound と等価です。この条件は、多くの場合、テーブルの大部分の行にマッチする可能性があります。

例えば、価格が1ドルから100万ドルまでの商品がほとんどであるデータベースで、価格が1000ドルから5000ドルの範囲にない商品を検索する場合、検索対象となる行はテーブルのほとんどの行になります。

このような場合、インデックスを使って少数の行を特定するよりも、テーブルを最初から最後まで順に読み込む全件スキャン（Seq Scan）の方が効率的だとオプティマイザが判断することがよくあります。したがって、NOT BETWEEN条件はインデックスを効率的に利用しにくい傾向があります。

ただし、条件にマッチする行が少数である場合（例えば、非常に狭い範囲のNOT BETWEENや、特殊なデータ分布の場合）、あるいはPostgreSQL 9.2以降で導入されたインデックスオンリースキャンが可能な場合など、NOT BETWEENでもインデックスが利用される可能性はあります。

パフォーマンスが重要な場合は、EXPLAINコマンドを使って実際にクエリの実行計画を確認することが不可欠です。

8. BETWEEN演算子を使う上でのベストプラクティスと注意点

BETWEEN演算子を効果的かつ正確に使うために、以下のベストプラクティスと注意点を心に留めておきましょう。

  • 日付/時刻の終端に注意: 時刻情報を持つカラムに対して日付のみでBETWEENを使用する場合、終端の時刻が00:00:00と見なされることによる「一日中のデータが含まれない」問題に注意が必要です。安全な代替手段として、>= start_date AND < end_date + interval '1 day' のような非包括的な比較や、範囲型、あるいは日付型へのキャストを検討してください。
  • 境界値の順序を守る: expr BETWEEN lower_bound AND upper_bound では、常にlower_bound <= upper_bound となるように値を指定してください。順序が逆転すると、エラーにはなりませんが結果は空になります。
  • NULL値の扱いを理解する: BETWEEN演算子は、PostgreSQLにおける他の比較演算子と同様に、NULL値を特別に扱います。比較されるexprがNULLの場合、またはlower_boundやupper_boundがNULLの場合、BETWEEN演算の結果はNULL（不明）となります。WHERE句は条件が真（True）となる行のみを返すため、結果がNULLとなる行は含まれません。もしNULL値を含む範囲検索を行いたい場合は、OR expr IS NULLのような条件を追加する必要があります。
    例：価格が50.00から300.00の間にあるか、または価格がNULLの商品を選択
    sql
    SELECT product_name, price
    FROM products
    WHERE price BETWEEN 50.00 AND 300.00 OR price IS NULL;
  • データ型の整合性を確認する: expr、lower_bound、upper_boundのデータ型が互換性を持っているか、必要であれば明示的に型変換を行うかを検討してください。意図しない暗黙的な型変換は、予期せぬ結果やパフォーマンスの低下を招く可能性があります。
  • 可読性を考慮する: BETWEENは多くの場合に可読性を向上させますが、非常に複雑な式や特殊な範囲条件では、かえって分かりにくくなることもあります。その場合は、比較演算子と論理演算子の組み合わせの方が意図が明確になることもあります。
  • インデックスと統計情報を活用する: 範囲検索のパフォーマンスはインデックスに大きく依存します。検索頻度の高いカラムには適切なインデックスを作成し、定期的にANALYZEコマンドを実行して統計情報を更新することで、オプティマイザが効率的な実行計画を選択できるようにします。

9. 応用例と高度な使い方

BETWEEN演算子は、単にWHERE句で使用するだけでなく、様々なPostgreSQLの機能と組み合わせて応用できます。

9.1. 集計関数との組み合わせ

特定の範囲内のデータに対する集計（合計、平均、件数など）を行う場合に使用できます。

sql
-- 2023年第1四半期（1月1日～3月31日）の総売上を計算
SELECT SUM(total_amount) AS q1_2023_total_sales
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

9.2. サブクエリやCTEとの組み合わせ

サブクエリや共通テーブル式（CTE）の中で一時的な結果セットを作成し、その結果に対してBETWEENで範囲検索を行うといった使い方が可能です。

sql
-- 各商品の月別売上合計を計算し、特定の範囲内の月別売上だけを表示する
WITH monthly_sales AS (
SELECT
product_id,
date_trunc('month', order_date)::date AS sale_month,
SUM(total_amount) AS monthly_total
FROM orders
GROUP BY product_id, date_trunc('month', order_date)
)
SELECT p.product_name, ms.sale_month, ms.monthly_total
FROM monthly_sales ms
JOIN products p ON ms.product_id = p.product_id
WHERE ms.sale_month BETWEEN '2023-03-01' AND '2023-05-01'; -- 2023年3月1日から5月1日まで（両端含む）の月
この例では、CTE monthly_sales で月ごとの売上を集計し、その結果に対して sale_month が特定の範囲にある行をフィルタリングしています。date_trunc('month', order_date)::date は、注文日をその月の最初の日付に丸める処理です。

9.3. アプリケーションからのパラメータとしての利用

アプリケーション開発において、ユーザーからの入力（例: 検索期間、価格帯）を元にSQLクエリを構築する場合、BETWEENは非常に自然にパラメータをバインドできます。

“`sql
— アプリケーションコードから呼ばれるクエリのイメージ（擬似コード）
— 例: ユーザーが入力した開始日と終了日
start_date_param = ‘2023-04-01’;
end_date_param = ‘2023-04-30’;

— SQLクエリ
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN :start_date_param AND :end_date_param; — パラメータバインディング

— (注意: 日付/時刻カラムに対する BETWEEN の終端の問題は、アプリケーション側で適切にパラメータ値を調整するか、
— クエリ自体を非包括的な比較にするなどの対応が必要)
``
パラメータとして渡す場合も、前述した日付/時刻の範囲指定に関する注意点は変わりません。アプリケーション側で終端の日付を調整して渡すか、BETWEENではなく>= … AND < …` 形式のクエリを使用するなどの対応が必要です。

10. まとめ

PostgreSQLのBETWEEN演算子は、数値、日付/時刻、文字列などの範囲検索を簡潔に記述するための強力なツールです。その最も重要な特性は、指定された下限値と上限値を含む（包括的）であることです。

この記事を通じて、以下の点を理解していただけたかと思います。

  • BETWEEN expr lower_bound AND upper_bound は expr >= lower_bound AND expr <= upper_bound と等価である。
  • 数値、日付/時刻、文字列に対して適用可能である。
  • 特に日付/時刻型のカラムに時刻情報が含まれる場合、日付のみのBETWEEN終端は00:00:00と解釈され、意図した範囲（一日中）をカバーしない可能性があるため注意が必要である。安全な日付/時刻の範囲指定には、非包括的な比較 (>= ... AND < ...) や範囲型が推奨される場合が多い。
  • NOT BETWEEN演算子は、指定範囲外のデータを検索する際に便利である。
  • 比較演算子を組み合わせる方法やPostgreSQLの範囲型など、他の範囲指定方法も存在し、それぞれに利点がある。状況に応じて最適な方法を選択すべきである。
  • BETWEENを含む範囲検索は、適切に作成されたB-treeインデックスを効率的に利用できるため、パフォーマンス向上の鍵となる。EXPLAINを使って実行計画を確認することが重要である。
  • NULL値は範囲に含まれないため、必要に応じてIS NULL条件を追加する必要がある。
  • 境界値の順序が逆転すると結果が空になるため注意が必要である。

BETWEENはPostgreSQLで頻繁に使用される演算子であり、その挙動を正しく理解しておくことは、正確で効率的なクエリを作成するために不可欠です。特に日付/時刻の扱いは多くのユーザーがつまずきやすいポイントですので、この記事で解説した注意点を実践に活かしてください。

PostgreSQLには、この記事で触れた範囲型のように、標準SQLにはない強力な機能も多く存在します。それらを理解し、BETWEENのような基本的な演算子と組み合わせて使うことで、より高度で柔軟なデータベース操作が可能になります。

あなたのPostgreSQLによるデータ操作が、この記事を通じてさらに効率的かつ正確なものとなることを願っています。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール