SQL - BETWEEN の使い方

SQL の BETWEEN オペレータ

SQL の BETWEEN オペレータは、範囲を指定して、値がその範囲内にある場合は TRUE を、そうでなければ FALSE を返してくれるオペレーターです。

WHERE 句や CASE WHEN の条件文など、boolean(ブーリアン)を返す式が必要な箇所で使えます。

今回は、SQL の BETWEEN の使い方や注意点などをご説明します。

BETWEEN の使い方
NOT BETWEEN の使い方
BETWEEN の範囲の値は含む？
BETWEEN で NULL が絡むとどうなる？
BETWEEN で日付の範囲を指定する

BETWEEN の使い方

まずは、SQL の BETWEEN オペレータの使い方です。

BETWEEN オペレータの構文は以下の通りです。

[評価する値] BETWEEN [範囲開始の値] AND [範囲終了の値]

[評価する値] が [範囲開始の値] ～ [範囲終了の値] にある場合は TRUE を、そうでなければ FALSE を返します。

[評価する値]、[範囲開始の値]、[範囲終了の値] は基本的に同じデータ型でなくてはいけません。

暗黙的な変換が行われてうまくいく場合もありますが、できるだけ同じデータ型の値を指定しましょう。

例えば、次のような Student テーブルがあります。

このうち、StudentID が 2 ~ 5 までのレコードを取得したい時は、BETWEEN を使って次のように取得することができます。

SELECT	* 
FROM	Student
WHERE	StudentID BETWEEN 2 AND 5;

NOT BETWEEN の使い方

続いて SQL の NOT BETWEEN の使い方です。

構文は先ほどの BETWEEN の前に NOT を付けます。

[評価する値] NOT BETWEEN [範囲開始の値] AND [範囲終了の値]

NOT BETWEEN では [評価する値] が [範囲開始の値] より前、もしくは [範囲終了の値] より後にある場合は TRUE を、そうでなければ FALSE を返します。

例えば、先ほどのクエリーの前に NOT をつけて実行すると、次のように StudentID が 2 より小さいレコードと、5 より大きいレコードが得られます。

SELECT	* 
FROM	Student
WHERE	StudentID NOT BETWEEN 2 AND 5;

BETWEEN の範囲の値は含む？

上の結果でおわかりかもしれませんが、BETWEEN が値を評価する際に、範囲の開始終了値を含むかどうかですが、正解は「含みます」。

[評価する値] が [範囲開始の値] もしくは [範囲終了の値] の時は TRUE を返します。

ですので以下の二つのクエリーは同じ結果を返します。

SELECT	* 
FROM	Student
WHERE	StudentID BETWEEN 2 AND 5;

SELECT	* 
FROM	Student
WHERE	StudentID >= 2
	AND StudentID <= 5;

NOT BETWEEN の時は [評価する値] が [範囲開始の値] もしくは [範囲終了の値] の時は FALSE を返しますので、範囲の開始終了値は「含まず」、次のクエリーと同じ結果を返します。

SELECT	* 
FROM	Student
WHERE	StudentID NOT BETWEEN 2 AND 5;

SELECT	* 
FROM	Student
WHERE	StudentID < 2
	OR StudentID > 5;

BETWEEN で NULL が絡むとどうなる？

BETWEEN の [評価する値]、[範囲開始の値]、[範囲終了の値] のどれかひとつでも NULL になると、評価の結果は UNKNOWN となり、FALSE と同様の扱いになります。

特に次のように [範囲開始の値] か [範囲終了の値] が NULL になる可能性がある時は、評価の結果が全レコード UNKNOWN になってしまいますので、注意してください。

DECLARE @StudentIDFrom INT = 2,
	@StudentIDTo INT;

SELECT	* 
FROM	Student
WHERE	StudentID BETWEEN @StudentIDFrom AND @StudentIDTo;

NOT BETWEEN のほうは、先ほど同等のクエリーでご紹介したように OR 扱いなので、NULL じゃないほうの範囲の条件は効いて、StudentID が 2 より小さいレコードが返ってきていますね。

DECLARE @StudentIDFrom INT = 2,
	@StudentIDTo INT;

SELECT	* 
FROM	Student
WHERE	StudentID NOT BETWEEN @StudentIDFrom AND @StudentIDTo;

NULL は比較の際に期待する結果が得られない原因になる可能性がありますので、先に NULL をチェックして値を置き換えるなど、NULL の時の扱いが一目でわかるようなクエリーにしておくと良いと思います。

BETWEEN で日付の範囲を指定する

BETWEEN で日付の範囲を指定する際に少し気を付けなければいけないことがあります。

DATETIME などの時間を含める日付型の場合、[範囲開始の値] と [範囲終了の値] に日付のみを指定すると、[範囲終了の値] の日付はその日の 0 時ちょうど以外含まれないことになります。

言葉ではわかりにくいかもしれないので、例をあげて説明します。

先ほどの Student テーブルに DATETIME 型の ModifiedOn カラムを追加して、ModifiedOn 順にソートしました。

このテーブルに対して、2020 年 1 月 2 日～ 2020 年 1 月 5 日に変更されたデータを取得したいと思って、次のように BETWEEN を使ってしまうと 1 月 5 日に変更したデータが得られません。

DECLARE @ModifiedDateFrom DATE = '2020-01-02',
	@ModifiedDateTo DATE= '2020-01-05';

SELECT	 *
FROM	 Student
WHERE	 ModifiedOn BETWEEN @ModifiedDateFrom AND @ModifiedDateTo	
ORDER BY ModifiedOn;

これは、@ModifiedDateFrom と @ModifiedDateTo が DATETIME に変換され、ModifiedOn が "2020-01-02 00:00:00.000" 以降で "2020-01-05 00:00:00.000" 以前の時のみ TRUE を返すからです。

日付の箇所のみを比較するには BETWEEN を使わずに DATEDIFF を使ったり、データ型に応じて [範囲終了の値] の日付に TIME の部分の最大値を追加して比較したり、いろんな方法があります。

このように、BETWEEN を使わずに [範囲終了の値] の日付に 1 日足して、<= ではなく < にするのもいいと思いますし、ModifiedOn を DATE に変換してしまうのも、ひとつの手です。

DECLARE @ModifiedDateFrom1 DATE = '2020-01-02',
	@ModifiedDateTo1 DATE= '2020-01-06';
	
SELECT	 *
FROM	 Student
WHERE	 ModifiedOn >= @ModifiedDateFrom1
	 AND ModifiedOn < @ModifiedDateTo1
ORDER BY ModifiedOn;

-------------------------

DECLARE @ModifiedDateFrom2 DATE = '2020-01-02',
	@ModifiedDateTo2 DATE= '2020-01-05';

SELECT	 *
FROM	 Student
WHERE	 CONVERT(DATE, ModifiedOn) BETWEEN @ModifiedDateFrom2 AND @ModifiedDateTo2	
ORDER BY ModifiedOn;

データ取得のパフォーマンスが良いのは 1 日足す方法かと思いますが、どの方法を使うかはデータ量やインデックスのありなし、開発やメンテナンスの大変さなど、状況に応じて使い分けたら良いと思っています。

BETWEEN で DATE の範囲を指定する際にはご注意くださいね。

以上、SQL の BETWEEN の使い方と注意点などをご説明しました。