SQLでBETWEEN演算子とIN演算子を使用する方法

getdocs

4年前

序章

特定の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行のみを返します。