MySQLのWHEREとINを使って複数レコードを取得する！

MySQLでWHEREの条件指定時にINを使って、複数レコードを対象にする方法について書いています。
WHEREで条件をつけるときに、INを使うことによって、指定した値で取得するレコードを絞ることができます。

載せているSQLについては、MySQLのバージョン8.0.32を使って動作を確認しています。
公式ドキュメントにはこちらにINについての記載があります。

WHEREにINを使ってデータ取得する

WHEREにINを使うことで、INの中に複数の値を並べて、テーブルの対象になる複数のレコードを対象にすることができます。
例えば、下記のようにINを使うことができます。

SELECT * FROM fruits WHERE id IN (2, 4, 6);

このように書くと、fruitsテーブルのidカラムが「2・4・6」のレコードを検索して取得します。
INの中には、このように条件で取得したいカラムの値を、カンマで区切って記載します。

INとORとの比較

ORを使っても、下記のように条件としては同じように書けます。

SELECT * FROM fruits WHERE id = 2 OR id = 4 OR id = 6;

取得できる結果も同じになりますが、渡す値が多いほどINの方が見やすくなります。

NOT INは否定

NOT INを使うと、指定した値が含まれていないレコードを取得します。
例えば、下記のようにNOT INを使うと、渡した値が含まれていないレコードを取得します。

SELECT * FROM fruits WHERE id NOT IN (2, 4, 6);

fruitsテーブルのidカラムが「2・4・6」以外のレコードを取得することができます。

INに指定できる上限は？

MySQLのINに指定できる上限は、max_allowed_packetの設定値によって変わるようです。
公式のこちらの記事に、下記のように記載があります。

IN() リストの値の数は、max_allowed_packet 値によってのみ制限されます。

INを使ったサンプルSQL3つ

実際にINを使ってSQLを書いて、動作を確認してみました。

テストデータ

下記のSQLでfruitsテーブルを作りました。

CREATE TABLE fruits (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(256),
  color VARCHAR(256),
  price INT,
  PRIMARY KEY (id)
);

データはこちらを使っています。

INを使ってレコードを取得

INを使って、指定した値のレコードを取得してみます。

SELECT * FROM fruits WHERE color IN ('yellow', 'blue');

fruitsテーブルからcolorカラムの値が「’yellow’, ‘blue’」のレコードをINを使って取得しました。
実際に実行すると、下記のように表示されます。

mysql> SELECT * FROM fruits WHERE color IN ('yellow', 'blue');
+----+-----------+--------+-------+
| id | name      | color  | price |
+----+-----------+--------+-------+
|  2 | ぶどう    | blue   |   120 |
|  4 | バナナ    | yellow |   110 |
|  6 | レモン    | yellow |   120 |
+----+-----------+--------+-------+
3 rows in set (0.01 sec)

colorに「’yellow’, ‘blue’」が含まれるレコードのみが取得できました。

NOT INを使ってレコードを取得

NOT INを使って、指定した値が含まれないレコードを取得してみます。

SELECT * FROM fruits WHERE price NOT IN (120, 110, 100);

priceカラムに対して、NOT INを使って「120, 110, 100」以外のレコードを対象にしました。
実際に実行すると、下記のように表示されます。

mysql> SELECT * FROM fruits WHERE price NOT IN (120, 110, 100);
+----+-----------+--------+-------+
| id | name      | color  | price |
+----+-----------+--------+-------+
|  3 | みかん    | orange |   130 |
|  5 | もも      | pink   |   125 |
+----+-----------+--------+-------+
2 rows in set (0.00 sec)

対象になっていないpriceカラムが「130, 125」のレコードが取得できました。

値としてサブクエリを指定する

INに対して、サブクエリを使って条件をつけることもできます。

このときにはサブクエリでは、ひとつのカラムのみを返す必要があります。
複数カラムを返すと、比較する値がわからないためです。

例えば、下記のようにサブクエリを渡すことができます。

SELECT * FROM fruits WHERE id IN (SELECT id FROM select_fruits);

select_fruitsテーブルにはidカラムが「1, 3, 5」のレコードがあります。
このサブクエリの結果で返ってくる値で、fruitsテーブルのidカラムのレコードを参照して、結果を取得しています。

実際に実行すると、下記のように表示されます。

mysql> SELECT * FROM fruits WHERE ID IN (SELECT id FROM select_fruits);
+----+-----------+--------+-------+
| id | name      | color  | price |
+----+-----------+--------+-------+
|  1 | りんご    | red    |   100 |
|  3 | みかん    | orange |   130 |
|  5 | もも      | pink   |   125 |
+----+-----------+--------+-------+
3 rows in set (0.00 sec)

サブクエリの結果で返ってくる「1, 3, 5」のレコードのみが取得できました。

WHEREにINを使う方法についてまとめ

今回はWHEREにINを使ってレコードを取得する方法について書きました。
記事の内容をまとめると、下記のようになります。