SQLのwhere節で範囲指定する場合に便利なbetween演算子。
コピペで動作を確認できるサンプルコードを紹介します。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
SQLのbetweenを使ったサンプルコード
数値を範囲指定するサンプル
以下のSQLは、employees（雇用者テーブル）から、社員番号20000以上、20050以下の社員を抽出します。
SELECT * FROM employees where emp_no between 20000 and 20050
SQLの実行結果は以下の通りです。
betweenは演算子の一種で、where句で使用します。構文は以下の通りです。
カラム名 between 下限の値 and 上限の値
not（否定）を使う場合は、以下のようにbetweenの前に記述します。
カラム名 not between 下限の値 and 上限の値
同じ動きをするSQLを、不等号を使って書くと以下のようになります。
SELECT * FROM employees where emp_no >= 20000 and emp_no <= 20050
betweenを使うことで、範囲指定の条件をシンプルに書くことができるんですね。
日付の範囲指定するサンプル
SELECT * FROM employees where hire_date between '1990-01-01' and '1991-01-01'
文字列の範囲指定するサンプル
departments(部署名）テーブルから、’C’ < 部署名 < ‘H’の条件に合うデータを抽出するSQLです。
SELECT * FROM departments where dept_name between 'C' and 'H'
実行すると以下の通り。
SQL実行結果
問題なさそうですが、少し条件を変えて、以下を実行してみましょう。
SELECT * FROM departments where dept_name between 'C' and 'F'
SQL実行結果
’C’ < ‘Finance’ < ‘F’ が偽となるため、実行結果に「Finance」が含まれません。
‘F’ < ‘Finance’となるためです。
SQLをパッと見ると、パッと見ると、「F」で始まる「Finance」も含みそうな気がしませんか？
betweenで文字列を扱う場合、このように一見してコードの意図を誤解してしまうコードになり、可読性を下げてしまうケースがあります。
もともとbetweenのメリットは、「わかりやすく書ける」のがポイント。文字列を扱う場合は、注意が必要です。
変数を使い、相対的に範囲指定するサンプル
set @vdate='1996-01-01'; SELECT * FROM employeeswhere where hire_date between @vdate and @vdate + interval 1 year
変数@vdateを使って、指定範囲を開始を「@vdate」と「@vdate + interval 1 year」を使って相対指定しています。
‘1996-01-01’ + interval 1 yearは、以下のSQLを実行することで確認できます。
SELECT '1996-01-01' + interval 1 year
実行結果として、「1997-01-01」が返って来ました。
上記の変数を使ったSQLは、以下と同じ意味になります。
SELECT * FROM employees where hire_date between '1996-01-01' and '1997-01-01'
外部プログラムでSQLを生成する場合や、ストアドプロシジャ、ストアドファンクションで使えるので覚えておきましょう。
「未満」「より大きい」境界値を含まず範囲指定するサンプル
betweenは、指定範囲の境界値を含む「以上、以下」の指定のみ可能です。
しかし、not inを組み合わせることで、「未満」「より大きい」指定が可能になります。
以下の例は、月末日を求めずに、「2月1日～2月末日」の範囲指定をするSQLです。
SELECT * FROM employees
where (hire_date between '1986-02-01' and '1986-03-01')
and hire_date != ('1986-03-01')
1986-02-01～1986-03-01の範囲指定をおこなった後、1986-03-01を除外しています。
「未満」「より大きい」を同時に指定したい場合は、not inを使って、２つの境界値を除外します。
※1986-02-02～1986年2月末日の範囲指定をするSQL
SELECT * FROM employees
where (hire_date between '1986-02-01' and '1986-03-01')
and hire_date not in ('1986-02-01','1986-03-01')
whereの不等号条件指定と、betweenの実行速度比較
範囲指定を行う場合、関数と不等号を組み合わせると、クエリのレスポンスが遅くなるので、betweenを使うと高速化できるという情報がネットで見つかりました。
実際に速くなるのか、試してみました。
以下のSQLは、salaries（年収）テーブルから、to_date（雇用日）が1996年4月～1998年3月のデータを抽出するSQLです。salariesは、280万件以上のデータが入っています。
まず、salariesテーブルのto_dateにインデックスを設定します。
ALTER TABLE salaries ADD INDEX `to_date_index` (`to_date`);
続いて、以下のSQLを実行します。
SELECT * FROM salaries where date_format( to_date, '%Y%m') >= '199604' and date_format( to_date, '%Y%m') <= '199803'
実行時間は「Query took 0.0004 seconds.」と表示されました。
Explainで実行計画を確認すると、テーブルフルスキャン（全件検索）が実行されていて、インデックスが利用されていません。使用可能なキー（possible keys）はNULLと表示されています。が、さほど遅いとも思えませんでした。
SQLを書き換えて、以下を実行します。
SELECT * FROM salaries where to_date between '1996-04-01' and '1998-03-31'
実行時間は同じく、「Query took 0.0004 seconds.」と表示されました。
Explainでは、使用可能なキーに、先ほど設定した「to_date_index」が表示されました。しかし、実際のクエリ実行にはインデックスは使われていないようです。
このことから、「betweenを使うと無条件で速くなるわけではない。ただし、インデックスを有効に使えるクエリの場合は、速くなる可能性がある」と言えるでしょう。
まとめ
- 範囲条件指定にbetweenを使うと、可読性が良くなる
- 文字列を扱う場合は、逆にわかりづらくなるので注意
- 「not in」などを組み合わえて、「未満」「より大きい」指定が可能
- 不等号の範囲指定をbetweenに置き換えると高速化するかはケースバイケース
