SQLでBETWEEN演算子とIN演算子を使用する方法
序章
特定のStructuredQuery Language（SQL）ステートメントでは、 WHERE句を使用して、特定の操作が影響する行を制限できます。 これは、検索条件と呼ばれる、影響を受けるために各行が満たさなければならない特定の基準を定義することによって行われます。 検索条件は、1つ以上の述語、または「true」、「false」、または「unknown」のいずれかに評価される特殊な式で構成され、操作は、[ X215X]句は「true」と評価されます。
SQLを使用すると、ユーザーはさまざまなタイプの述語を提供することにより、詳細な結果セットを取得できます。各述語は、特定の演算子を使用して行を評価します。 このガイドでは、BETWEEN演算子を使用する範囲述語と、IN演算子を使用するセットメンバーシップ述語の2種類の述語について概説します。
このガイドでは、例で SELECTステートメントのみを使用しますが、ここで説明する概念は、多くのSQL操作で使用できます。 特に、WHERE句とその検索条件は、UPDATEおよびDELETE操作の重要なコンポーネントです。
前提条件
このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム（RDBMS）を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。
- Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
- Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。 このガイドは、ステップ3 で説明されているように、新しく作成されたユーザーによって検証されました。
- また、さまざまな
WHERE句の述語を使用して練習するために使用できるサンプルデータがロードされたいくつかのテーブルを含むデータベースも必要です。 MySQLサーバーに接続し、このガイド全体の例で使用されるテストデータベースを作成する方法の詳細については、次のMySQLへの接続とサンプルデータベースのセットアップセクションを実行することをお勧めします。
注：多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドは、PostgreSQLやSQLiteを含むほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストすると、正確な構文や出力が異なる場合があります。
このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。
インタラクティブターミナルを起動します！
MySQLへの接続とサンプルデータベースの設定
SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
ssh sammy@your_server_ip
次に、MySQLサーバープロンプトを開き、sammyをMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。
mysql -u sammy -p
プロンプトから、between_in_dbという名前のデータベースを作成します。
CREATE DATABASE between_in_db;
データベースが正常に作成されると、次のような出力が表示されます。
OutputQuery OK, 1 row affected (0.01 sec)
between_in_dbデータベースを選択するには、次のUSEステートメントを実行します。
USE between_in_db;
OutputDatabase changed
between_in_dbを選択した後、その中にテーブルを作成します。
このガイドで使用されている例に従うために、会社の営業チームを管理していると想像してください。 この会社は、ウィジェット、ドゥーダッド、ギズモの3つの製品のみを販売しています。 チームの各メンバーがSQLデータベースで販売した各製品のユニット数の追跡を開始します。 このデータベースには、4つの列を持つ1つのテーブルがあると決定します。
name：最大20文字のvarcharデータ型を使用して表された営業チームの各メンバーの名前widgets：各営業担当者が販売したウィジェットの総数。intデータ型で表されます。doodads：各営業担当者が販売したドゥーダッドの数。intとも呼ばれます。gizmos：各営業担当者が販売したギズモの数。これもintとして表されます。
次のCREATE TABLEステートメントを実行して、次の4つの列を持つsalesという名前のテーブルを作成します。
CREATE TABLE sales ( name varchar(20), widgets int, doodads int, gizmos int );
OutputQuery OK, 0 rows affected (0.01 sec)
次に、salesテーブルにサンプルデータをロードします。 次のINSERT INTO操作を実行して、チームの営業担当者と販売した各製品の数を表す7行のデータを追加します。
INSERT INTO sales
VALUES
('Tyler', 12, 22, 18),
('Blair', 19, 8, 13),
('Lynn', 7, 29, 3),
('Boris', 16, 16, 15),
('Lisa', 17, 2, 31),
('Maya', 5, 9, 7),
('Henry', 14, 2, 0);
これで、ガイドの残りの部分に従い、BETWEENおよびIN演算子を使用してデータをフィルター処理する方法を学習する準備が整いました。
WHERE句の述語を理解する
既存のテーブルからデータを読み取るSQL操作では、FROM句の後にWHERE句を続けて、操作が影響するデータを制限できます。 WHERE句は、検索条件を定義することによってこれを行います。 検索条件を満たさない行は操作から除外されますが、満たす行は含まれます。
検索条件は、1つ以上の述語、または1つ以上の値式を評価し、「true」、「false」、または「unknown」の結果を返すことができる式で構成されます。 SQLでは、値式（スカラー式とも呼ばれる）は、単一の値を返す任意の式です。 値の式は、文字列や数値などのリテラル値、数式、または列名にすることができます。 ほとんどの場合、WHERE句の述語の少なくとも1つの値式が、操作のFROM句で参照されるテーブルの列の名前であることに注意してください。
WHERE句を含むSQLクエリを実行すると、DBMSはFROM句で定義された論理テーブルのすべての行に検索条件を適用します。 次に、検索条件のすべての述部が「true」と評価された行のみを返します。
SQL標準では18種類の述語が定義されていますが、すべてのRDBMSがSQLの実装にそれぞれを含むわけではありません。 以下に、最も一般的に使用される5つの述語タイプと、それぞれの述語タイプとそれらが使用する演算子の簡単な説明を示します。
Compareson ：比較述語は、ある値の式を別の値の式と比較します。 クエリでは、ほとんどの場合、これらの値の式の少なくとも1つが列の名前である場合があります。 6つの比較演算子は次のとおりです。
=：2つの値が等しいかどうかをテストします<>：2つの値が同等でないかどうかをテストします<：最初の値が2番目の値よりも小さいかどうかをテストします>：最初の値が2番目の値より大きいかどうかをテストします<=：最初の値が2番目の値以下かどうかをテストします>=：最初の値が2番目の値以上かどうかをテストします
Null ：IS NULL演算子を使用する述語は、特定の列の値がNullであるかどうかをテストします Range ：範囲述語は、BETWEEN演算子を使用して1つの値式が他の2つの間にあるMembership：このタイプの述語は、IN演算子を使用して、値が特定のセットのメンバーであるかどうかをテストしますパターンマッチ：パターン一致する述語は、LIKE演算子を使用して、値が文字列パターンと一致するかどうかをテストします
はじめに述べたように、このガイドでは、SQLのBETWEENおよびIN演算子を使用してデータをフィルター処理する方法の概要に焦点を当てています。 比較演算子またはIS NULL演算子の使用方法を知りたい場合は、SQLでの比較およびISNULL演算子の使用方法に関するこのガイドを確認することをお勧めします。 または、LIKE演算子を使用して、ワイルドカード文字を含む文字列パターンに基づいてデータをフィルタリングする方法を知りたい場合は、SQLでワイルドカードを使用する方法に関するガイドに従ってください。 最後に、WHERE句の概要について詳しく知りたい場合は、SQLでWHERE句を使用する方法に関するチュートリアルに興味があるかもしれません。
範囲述語
範囲述語は、BETWEEN演算子を使用して、1つの値式が他の2つの値式の間にあるかどうかをテストします。 検索条件に範囲述語を含むWHERE句は、次の一般的な構文に従います。
SELECT column_list FROM table_name WHERE column_name BETWEEN value_expression1 AND value_expression2;
WHEREキーワードの後には値式があり、ほとんどのSQL操作では列の名前です。 データベースシステムは各行に順番に検索条件を適用するため、検索条件の値式として列名を指定すると、RDBMSは、その列の各行の値をその行の検索条件の反復の値式として使用するように指示されます。
列名の後には、BETWEEN演算子と、ANDで区切られた2つの値式が続きます。 指定された列の値がANDで区切られた2つの値の最初の値以上で、2番目の値以下の行の場合、検索条件は「true」に解決されます。
範囲述語がどのように機能するかを説明するには、次のクエリを実行します。 これにより、widgetsの値が14と19の間にある行のname列とwidgets列が返されます。
SELECT name, widgets FROM sales WHERE widgets BETWEEN 14 AND 19;
Output+-------+---------+ | name | widgets | +-------+---------+ | Blair | 19 | | Boris | 16 | | Lisa | 17 | | Henry | 14 | +-------+---------+ 4 rows in set (0.00 sec)
BETWEEN演算子の後に定義する範囲は、列名を含む任意の値式のペアで構成できることに注意してください。
次のクエリは、salesテーブルのすべての列を返します。 返すすべての列をリストするのではなく、代わりにSELECTキーワードの後にアスタリスク（*）を付けます。 これは、「すべての列」のSQLの省略形です。 このクエリのWHERE句は、gizmos値がdoodads値よりも大きく、widgets値よりも小さい行のみを返すように制限します。
SELECT * FROM sales WHERE gizmos BETWEEN doodads AND widgets;
widgetsとdoodadsの値の間にあるgizmos値を持つのは、営業チームの1人のメンバーだけなので、その行だけが結果セットに表示されます。
Output+-------+---------+---------+--------+ | name | widgets | doodads | gizmos | +-------+---------+---------+--------+ | Blair | 19 | 8 | 13 | +-------+---------+---------+--------+ 1 row in set (0.00 sec)
範囲を定義する値式をリストする順序に注意してください。BETWEEN演算子の後の最初の値は常に範囲の下限であり、2番目は常に上限です。 次のクエリは、範囲の両端を定義する列の順序を反転することを除いて、前のクエリと同じです。
SELECT * FROM sales WHERE gizmos BETWEEN widgets AND doodads;
今回、クエリは、gizmos値が行のwidgets値以上で、doodads値以下の2つの行を返します。 この出力が示すように、このように順序を変更すると、完全に異なる結果セットが返されます。
Output+-------+---------+---------+--------+ | name | widgets | doodads | gizmos | +-------+---------+---------+--------+ | Tyler | 12 | 22 | 18 | | Maya | 5 | 9 | 7 | +-------+---------+---------+--------+ 2 rows in set (0.00 sec)
以下のような <、>、<=、および>=比較演算子 、文字列値を保持する列を評価するために使用される場合、BETWEEN 演算子は、これらの値がアルファベット順に2つの文字列値の間にあるかどうかを判断します。
説明のために、name値が文字AとM、アルファベット順。
この例では、範囲の両端を構成する値式として2つの文字列リテラルを使用しています。 これらのリテラル値は一重引用符または二重引用符で囲む必要があることに注意してください。 それ以外の場合、DBMSはAおよびMという名前の列を検索し、クエリは失敗します。
SELECT name FROM sales WHERE name BETWEEN 'A' AND 'M';
Output+-------+ | name | +-------+ | Blair | | Lynn | | Boris | | Lisa | | Henry | +-------+ 5 rows in set (0.00 sec)
検索条件で指定された範囲がAからMであっても、この結果セットにはMayaが含まれていないことに注意してください。 これは、アルファベット順に、文字「M」で始まり、複数の文字を含む文字列の前にあるため、Mayaは、名前が内にない他の営業担当者とともにこの結果セットから除外されるためです。与えられた範囲。
メンバーシップ述語
メンバーシップ述語を使用すると、値が指定されたデータセットのメンバーであるかどうかに基づいてクエリ結果をフィルタリングできます。 WHERE句では、通常、次の構文に従います。
. . . WHERE column_name IN (set_of_data) . . .
WHEREキーワードの後に、値式が続きます。 繰り返しますが、この最初の値の式は通常、列の名前です。 続いてIN演算子があり、その後に一連のデータが続きます。 このセットは、リテラルや列名など、コンマで区切られた任意の数の有効な値式、またはこれらのいずれかを含む数式をリストすることで明示的に定義できます。
説明のために、次のクエリを実行します。 これにより、gizmos値がIN演算子の後に定義されたセットのメンバーであるすべての行に対して、name列とgizmos列が返されます。
SELECT name, doodads FROM sales WHERE doodads IN (1, 2, 11, 12, 21, 22);
営業チームのdoodads値スコアの3人のメンバーのみがこのセットの値のいずれかに等しいため、これらの行のみが返されます。
Output+-------+---------+ | name | doodads | +-------+---------+ | Tyler | 22 | | Lisa | 2 | | Henry | 2 | +-------+---------+ 3 rows in set (0.00 sec)
セットの各メンバーを自分で書き出す代わりに、IN演算子の後にサブクエリを実行することでセットを導出できます。 subquery — nestedまたはinnerquery とも呼ばれます—は、別のSELECTステートメント。 サブクエリは、「外部」操作のFROM句で定義されたテーブルと同じデータベース内の任意のテーブルから情報を取得できます。
注：メンバーシップ述語の一部としてセットを定義するサブクエリを作成するときは、スカラーサブクエリ、または単一の列のみを返すサブクエリを使用するようにしてください。 データベース管理システムは通常、メンバーシップ述語で複数の列を返すサブクエリを許可しません。これは、データベースシステムがどの列をセットとして評価するかが明確でないためです。
サブクエリを使用してメンバーシップ述部にセットを定義する例として、次のステートメントを実行して、列が1つしかないexample_set_tableという名前のテーブルを作成します。 この列にはprime_numbersという名前が付けられ、intデータ型の値が保持されます。
CREATE TABLE example_set_table ( prime_numbers int );
次に、このテーブルに2行のサンプルデータをロードします。 テーブルの唯一の列の名前に合わせて、次のINSERTステートメントは10行のデータをテーブルにロードし、それぞれが最初の10個の素数の1つを保持します。
INSERT INTO example_set_table VALUES (2), (3), (5), (7), (11), (13), (17), (19), (23), (29);
次に、次のクエリを実行します。 これは、salesテーブルのname列とwidgets列から値を返し、そのWHERE句は、widgetsの各値をテストします。列は、サブクエリSELECT prime_numbers FROM example_set_tableによって派生したセットに含まれています。
SELECT name, widgets FROM sales WHERE widgets IN (SELECT prime_numbers FROM example_set_table);
Output+-------+---------+ | name | widgets | +-------+---------+ | Blair | 19 | | Lynn | 7 | | Lisa | 17 | | Maya | 5 | +-------+---------+ 4 rows in set (0.00 sec)
example_set_tableに格納されている素数のいずれかに等しい数のウィジェットを販売した営業担当者は4人だけなので、このクエリはそれらの4行のみを返します。
結論
このガイドに従うことで、SQLのBETWEEN演算子を使用して、列の値が特定の範囲内にあるかどうかをテストする方法を学習しました。 また、IN演算子を使用して、列の値がセットのメンバーであるかどうかをテストする方法も学習しました。
ここに示すコマンドはほとんどのリレーショナルデータベースで機能するはずですが、すべてのSQLデータベースが独自の言語実装を使用していることに注意してください。 各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。
