SQLでBETWEEN演算子とIN演算子を使用する方法
序章
特定のStructuredQuery Language(SQL)ステートメントでは、 WHERE句を使用して、特定の操作が影響する行を制限できます。 これは、検索条件と呼ばれる、影響を受けるために各行が満たさなければならない特定の基準を定義することによって行われます。 検索条件は、1つ以上の述語、または「true」、「false」、または「unknown」のいずれかに評価される特殊な式で構成され、操作は、 WHERE
句は「true」と評価されます。
SQLを使用すると、ユーザーはさまざまなタイプの述語を提供することにより、詳細な結果セットを取得できます。各述語は、特定の演算子を使用して行を評価します。 このガイドでは、2種類の述語の概要を説明します。 BETWEEN
演算子、およびを使用するメンバーシップ述語を設定します IN
オペレーター。
このガイドでは、例で 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以外のシステムでテストすると、正確な構文や出力が異なる場合があります。
MySQLへの接続とサンプルデータベースの設定
SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
- ssh sammy@your_server_ip
次に、MySQLサーバープロンプトを開き、置き換えます sammy
with the name of your MySQL user account:
- 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
:営業チームの各メンバーの名前。varchar
最大20文字のデータ型widgets
:各営業担当者が販売したウィジェットの総数。int
データ・タイプdoodads
:各営業担当者が販売したドゥーダッドの数。int
gizmos
:各営業担当者が販売したギズモの数。int
次を実行します CREATE TABLE
名前の付いたテーブルを作成するステートメント sales
次の4つの列があります。
- 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では、値式(スカラー式とも呼ばれる)は、単一の値を返す任意の式です。 値の式は、文字列や数値などのリテラル値、数式、または列名にすることができます。 ほとんどの場合、少なくとも1つの値式が WHERE
節述語は、操作で参照されるテーブル内の列の名前です。 FROM
句。
を含むSQLクエリを実行する場合 WHERE
句を指定すると、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つの値式の間にあるかどうかをテストする演算子。 A WHERE
検索条件に範囲述語を含む句は、次の一般的な構文に従います。
- SELECT column_list
- FROM table_name
- WHERE column_name BETWEEN value_expression1 AND value_expression2;
次の WHERE
キーワードは値式であり、ほとんどのSQL操作では列の名前です。 データベースシステムは各行に順番に検索条件を適用するため、検索条件の値式として列名を指定すると、RDBMSは、その列の各行の値をその行の検索条件の反復の値式として使用するように指示されます。
列名の後には BETWEEN
演算子と、で区切られた2つの値式 AND
. 指定された列の値が、で区切られた2つの値の最初の値以上である行の場合、検索条件は「true」に解決されます。 AND
、ただし秒以下。
範囲述語がどのように機能するかを説明するには、次のクエリを実行します。 これにより、 name
と widgets
その行の列 widgets
値は 14
と 19
、包括的:
- 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;
営業チームの1人のメンバーだけが gizmos
それらの間にある値 widgets
と doodads
値なので、その行のみが結果セットに表示されます。
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;
今回は、クエリは2つの行を返します。 gizmos
値が行の値以上 widgets
値がその以下 doodads
価値。 この出力が示すように、このように順序を変更すると、完全に異なる結果セットが返されます。
Output+-------+---------+---------+--------+
| name | widgets | doodads | gizmos |
+-------+---------+---------+--------+
| Tyler | 12 | 22 | 18 |
| Maya | 5 | 9 | 7 |
+-------+---------+---------+--------+
2 rows in set (0.00 sec)
以下のような <、>、<=、および>=比較演算子 、文字列値を保持する列を評価するために使用される場合 BETWEEN
演算子は、これらの値がアルファベット順に2つの文字列値の間にあるかどうかを判別します。
説明のために、次のクエリを実行して、 name
の任意の行の値 sales
そのテーブル 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)
この結果セットには含まれていないことに注意してください Maya
検索条件で提供された範囲が A
に M
. これは、アルファベット順に、文字「M」で始まり、複数の文字を含む文字列の前にあるため、Mayaは、名前が内にない他の営業担当者とともにこの結果セットから除外されるためです。与えられた範囲。
メンバーシップ述語
メンバーシップ述語を使用すると、値が指定されたデータセットのメンバーであるかどうかに基づいてクエリ結果をフィルタリングできます。 の WHERE
句、それらは一般的にこの構文に従います:
- . . .
- WHERE column_name IN (set_of_data)
- . . .
後に WHERE
キーワードには値式があります。 この場合も、この最初の値の式は通常、列の名前です。 続いて IN
演算子、それ自体の後にデータセットが続きます。 このセットは、リテラルや列名など、コンマで区切られた任意の数の有効な値式、またはこれらのいずれかを含む数式をリストすることで明示的に定義できます。
説明のために、次のクエリを実行します。 これにより、 name
と gizmos
すべての行の列 gizmos
値は、後に定義されたセットのメンバーです。 IN
オペレーター:
- SELECT name, doodads
- FROM sales
- WHERE doodads IN (1, 2, 11, 12, 21, 22);
営業チームのメンバーは3人だけです doodads
値のスコアはこのセットのいずれかの値と等しいため、これらの行のみが返されます。
Output+-------+---------+
| name | doodads |
+-------+---------+
| Tyler | 22 |
| Lisa | 2 |
| Henry | 2 |
+-------+---------+
3 rows in set (0.00 sec)
セットの各メンバーを自分で書き出す代わりに、次のようにしてセットを導き出すことができます。 IN
サブクエリを持つ演算子。 サブクエリ— ネストされたまたは内部クエリとも呼ばれます—は SELECT
別の句の1つに埋め込まれたステートメント SELECT
声明。 サブクエリは、で定義されたテーブルと同じデータベース内の任意のテーブルから情報を取得できます。 FROM
「外部」操作の句。
注:メンバーシップ述語の一部としてセットを定義するサブクエリを作成するときは、スカラーサブクエリ、または単一の列のみを返すサブクエリを使用するようにしてください。 データベース管理システムは通常、メンバーシップ述語で複数の列を返すサブクエリを許可しません。これは、データベースシステムがどの列をセットとして評価するかが明確でないためです。
サブクエリを使用してメンバーシップ述部にセットを定義する例として、次のステートメントを実行して、 example_set_table
列が1つしかない。 この列の名前は 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);
次に、次のクエリを実行します。 これはから値を返します name
と widgets
からの列 sales
テーブル、およびその 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)
4人の営業担当者だけが、に保存されている素数のいずれかに等しい数のウィジェットを販売したためです。 example_set_table
、このクエリはこれらの4行のみを返します。
結論
このガイドに従うことで、SQLの使用方法を学びました BETWEEN
列の値が指定された範囲内にあるかどうかをテストする演算子。 また、使用方法も学びました IN
列の値がセットのメンバーであるかどうかをテストする演算子。
ここに示すコマンドはほとんどのリレーショナルデータベースで機能するはずですが、すべてのSQLデータベースが独自の言語実装を使用していることに注意してください。 各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。