SQL betweenで範囲指定するサンプルコード 速度アップにもつながる?
  • facebookページ
  • twitterページ
  • 2019.10.13

    SQL betweenで範囲指定するサンプルコード 速度アップにもつながる?

    SQLのwhere節で範囲指定する場合に便利なbetween演算子。

    コピペで動作を確認できるサンプルコードを紹介します。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    SQLのbetweenを使ったサンプルコード

    数値を範囲指定するサンプル

    以下のSQLは、employees(雇用者テーブル)から、社員番号20000以上、20050以下の社員を抽出します。

    SQLの実行結果は以下の通りです。

    betweenは演算子の一種で、where句で使用します。構文は以下の通りです。

    not(否定)を使う場合は、以下のようにbetweenの前に記述します。

    同じ動きをするSQLを、不等号を使って書くと以下のようになります。

    betweenを使うことで、範囲指定の条件をシンプルに書くことができるんですね。

    日付の範囲指定するサンプル

    文字列の範囲指定するサンプル

    departments(部署名)テーブルから、’C’ < 部署名 < ‘H’の条件に合うデータを抽出するSQLです。

    実行すると以下の通り。

    SQL実行結果

    問題なさそうですが、少し条件を変えて、以下を実行してみましょう。

    SQL実行結果

    ’C’ < ‘Finance’ < ‘F’ が偽となるため、実行結果に「Finance」が含まれません。

    ‘F’ < ‘Finance’となるためです。

    SQLをパッと見ると、パッと見ると、「F」で始まる「Finance」も含みそうな気がしませんか?

    betweenで文字列を扱う場合、このように一見してコードの意図を誤解してしまうコードになり、可読性を下げてしまうケースがあります。

    もともとbetweenのメリットは、「わかりやすく書ける」のがポイント。文字列を扱う場合は、注意が必要です。

    変数を使い、相対的に範囲指定するサンプル

    変数@vdateを使って、指定範囲を開始を「@vdate」と「@vdate + interval 1 year」を使って相対指定しています。

    ‘1996-01-01’ + interval 1 yearは、以下のSQLを実行することで確認できます。

    実行結果として、「1997-01-01」が返って来ました。

    上記の変数を使ったSQLは、以下と同じ意味になります。

    外部プログラムでSQLを生成する場合や、ストアドプロシジャ、ストアドファンクションで使えるので覚えておきましょう。

    「未満」「より大きい」境界値を含まず範囲指定するサンプル

    betweenは、指定範囲の境界値を含む「以上、以下」の指定のみ可能です。

    しかし、not inを組み合わせることで、「未満」「より大きい」指定が可能になります。

    以下の例は、月末日を求めずに、「2月1日~2月末日」の範囲指定をするSQLです。

    1986-02-01~1986-03-01の範囲指定をおこなった後、1986-03-01を除外しています。

    「未満」「より大きい」を同時に指定したい場合は、not inを使って、2つの境界値を除外します。

    ※1986-02-02~1986年2月末日の範囲指定をするSQL

    whereの不等号条件指定と、betweenの実行速度比較

    範囲指定を行う場合、関数と不等号を組み合わせると、クエリのレスポンスが遅くなるので、betweenを使うと高速化できるという情報がネットで見つかりました。

    実際に速くなるのか、試してみました。

    以下のSQLは、salaries(年収)テーブルから、to_date(雇用日)が1996年4月~1998年3月のデータを抽出するSQLです。salariesは、280万件以上のデータが入っています。

    まず、salariesテーブルのto_dateにインデックスを設定します。

    続いて、以下のSQLを実行します。

    実行時間は「Query took 0.0004 seconds.」と表示されました。

    Explainで実行計画を確認すると、テーブルフルスキャン(全件検索)が実行されていて、インデックスが利用されていません。使用可能なキー(possible keys)はNULLと表示されています。が、さほど遅いとも思えませんでした。

    SQLを書き換えて、以下を実行します。

    実行時間は同じく、「Query took 0.0004 seconds.」と表示されました。

    Explainでは、使用可能なキーに、先ほど設定した「to_date_index」が表示されました。しかし、実際のクエリ実行にはインデックスは使われていないようです。

    このことから、「betweenを使うと無条件で速くなるわけではない。ただし、インデックスを有効に使えるクエリの場合は、速くなる可能性がある」と言えるでしょう。

    まとめ

    ポテパンダの一言メモ
    • 範囲条件指定にbetweenを使うと、可読性が良くなる
    • 文字列を扱う場合は、逆にわかりづらくなるので注意
    • 「not in」などを組み合わえて、「未満」「より大きい」指定が可能
    • 不等号の範囲指定をbetweenに置き換えると高速化するかはケースバイケース


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE