SQLで情報を検索にかける際、LIKEやINなどは頻繁に使用します。
データベースの情報を広く検索する場合には、このLIKEとINを同時に使いたくなる場面も出てくると思いますが、SQLはこれを許可していません。
そこで今回はLIKEとINを同時に使いたい場合にはどのようにすれば良いか、LIKEとINの基本的使い方と共に解説してみたいと思います。
LIKEの基本
LIKEは曖昧検索をする際によく使われる命令句です。
前方一致検索や後方一致検索、または完全一致検索など様々な検索に柔軟な対応が可能なため、データベースの情報を取得する際には大いに役に立ちます。
まずはこのLIKEについて基本的な使い方を見てみましょう。
LIKEの記述
LIKEの基本的な記述は次の通りです。
SELECT [カラム名], ... FROM [テーブル名] WHERE [カラム名] LIKE [曖昧検索の条件];
実曖昧検索の条件では「%(パーセント)」ワイルドカードを使用し、前方一致検索、後方一致検索、部分一致検索、完全一致検索の四つからどの検索を使用するか指定します。
コードを見てもらえばすぐにわかると思いますので、実際のコードを見てみましょう。
なおデータベーステーブルに入っているデータは次の通りです。
MariaDB [sample]> SELECT * FROM sample_table; +----+------+-----+------------+ | id | name | age | birth | +----+------+-----+------------+ | 1 | 山田 | 29 | 1990-05-27 | | 2 | 田中 | 25 | 1994-10-22 | | 3 | 中元 | 30 | 1989-04-01 | | 4 | 宮原 | 35 | 1984-03-18 | | 5 | 鈴木 | 18 | 2001-06-15 | | 6 | 竹下 | 23 | 1996-02-21 | | 7 | 本田 | 26 | 1993-07-17 | | 8 | 中井 | 29 | 1990-08-03 | | 9 | 徳井 | 34 | 1985-05-15 | | 10 | 野間 | 40 | 1979-11-06 | +----+------+-----+------------+ 10 rows in set (0.000 sec)
このテーブルから「田」の付く名前のみ抽出する場合には、LIKE句を使って次のように記述します。
MariaDB [sample]> SELECT * FROM sample_table WHERE name LIKE "%田%"; +----+------+-----+------------+ | id | name | age | birth | +----+------+-----+------------+ | 1 | 山田 | 29 | 1990-05-27 | | 2 | 田中 | 25 | 1994-10-22 | | 7 | 本田 | 26 | 1993-07-17 | +----+------+-----+------------+ 3 rows in set (0.001 sec)
「LIKE “%田%”」とすることで、部分一致検索で田が付く名前を全て出力しています。
これがLIKEの基本的な使い方です。
INの基本
では次にINの使い方についても簡単に解説していきます。
IN句は複数の検索をする場合に使用される命令句です。
ただし曖昧検索というわけではなく、完全一致検索のみとなります。
記述は次の通りとなります。
SELECT [カラム名], ... FROM [テーブル名] WHERE [カラム名] IN([値1, 値2, 値3, ...]);
検索したい値を全て条件に指定するだけのシンプルな記述です。
ただしIN句は、プログラムによると思いますが、基本形で使用されることはあまり無いように見受けます。
ではどのように使われるかという部分については、恐らくサブクエリとして「IN句を使いSELECTで取得したデータをアップデートなどで使用する」という方法ではないでしょうか。
文章では言っている意味がよく分からないと思いますので、こちらもコードで解説してみたいと思います。
今回のサンプルで使用するデータベーステーブルは次の通りです。
// sample_table MariaDB [sample]> SELECT * FROM sample_table; +----+--------+-----+------------+----------+ | id | name | age | birth | group_id | +----+--------+-----+------------+----------+ | 1 | 山田 | 29 | 1990-05-27 | 3 | | 2 | 田中 | 25 | 1994-10-22 | 3 | | 3 | 中元 | 30 | 1989-04-01 | 2 | | 4 | 宮原 | 35 | 1984-03-18 | 2 | | 5 | 鈴木 | 18 | 2001-06-15 | 4 | | 6 | 竹下 | 23 | 1996-02-21 | 3 | | 7 | 本田 | 26 | 1993-07-17 | 3 | | 8 | 中井 | 29 | 1990-08-03 | 3 | | 9 | 徳井 | 34 | 1985-05-15 | 2 | | 10 | 野間 | 40 | 1979-11-06 | 1 | +----+--------+-----+------------+----------+ 10 rows in set (0.000 sec) // generation_table MariaDB [sample]> SELECT * FROM generation_table; +----+------------+ | id | group_name | +----+------------+ | 1 | 40代 | | 2 | 30代 | | 3 | 20代 | | 4 | 10代 | +----+------------+ 4 rows in set (0.000 sec)
例えば、この二つのテーブルを使って、20代のみを取得できるようなクエリを組んでみます。
MariaDB [sample]> SELECT * FROM sample_table
-> WHERE group_id IN(
-> SELECT id FROM generation_table
-> WHERE id = 3
-> );
+----+------+-----+------------+----------+
| id | name | age | birth | group_id |
+----+------+-----+------------+----------+
| 1 | 山田 | 29 | 1990-05-27 | 3 |
| 2 | 田中 | 25 | 1994-10-22 | 3 |
| 6 | 竹下 | 23 | 1996-02-21 | 3 |
| 7 | 本田 | 26 | 1993-07-17 | 3 |
| 8 | 中井 | 29 | 1990-08-03 | 3 |
+----+------+-----+------------+----------+
5 rows in set (0.000 sec)
IN句をサブクエリとして使えば、このような方法で検索をすることが出来ます。
LIKE句とIN句を同時に使いたい場合
ではここからが本記事の本題です。
まず結果から言うと、残念ながらLIKE句とIN句は同時使用が認められていないため不可能です。
ただし、別のクエリをいくつか複合的に使用することで、同じような結果を得ることは可能です。
※今回はLIKE句とIN句を同時使用したい場合の解説を行うため、その他のクエリに関しては解説を省略します。
この解説を行うため、次のようなテーブルを用意します。
MariaDB [sample]> SELECT * FROM item_table; +----+---------------------+-----------+-------+ | id | name | item_code | type | +----+---------------------+-----------+-------+ | 1 | Juice Orange(500ml) | djf500 | Drink | | 2 | Bread Strawberry | fbf01 | Food | | 3 | RiceBall Tuna | frw01 | Food | | 4 | Bread Egg Sandwich | fbo01 | Food | | 5 | Juice Apple(500ml) | djf500 | Drink | | 6 | Carbonated Water | dwo1000 | Drink | +----+---------------------+-----------+-------+ 6 rows in set (0.000 sec) MariaDB [sample]> SELECT * FROM code_table; +----+-------------+ | id | code | +----+-------------+ | 1 | djf500-160 | | 2 | dwo1000-200 | | 3 | dwo500-100 | | 4 | fbf01-100 | | 5 | fbo-200 | | 6 | frw01-130 | +----+-------------+ 6 rows in set (0.000 sec)
item_tableには商品と分類、code_tableには商品分類及び価格が入っています。
このcode_tableから、item_tableに存在するドリンクのみ抽出する場合で考えてみましょう。
REGEXPで正規表現の条件検索
まずドリンクに分類されるものはitem_tableの中には3種類あります。
この三つをitem_codeカラムを使ってcode_tableで参照させるには、REGEXP演算子を利用する事で解決します。
MariaDB [sample]> SELECT * FROM code_table
-> WHERE code REGEXP('(^djf500-.*)|(^dwo500-.*)|(^dwo1000-.*)');
+----+-------------+
| id | code |
+----+-------------+
| 1 | djf500-160 |
| 2 | dwo1000-200 |
| 3 | dwo500-100 |
+----+-------------+
3 rows in set (0.000 sec)
正規表現でパターンマッチングを行うことで、codeカラムから該当する全てのcodeを抽出できます。
ただし今回は結果としてitem_tableからドリンクのみを抽出させるため、REGEXPに記述している正規表現自体を別の方法で作る必要があります。
そこで次の方法を併用します。
CONCAT関数とGROUP_CONCAT関数を使用して文字列を作成する
CONCAT関数は、文字列を連結させ一つの文字列に組み替えるための関数です。
またGROUP_CONCAT関数は、指定したカラムに不l組まれる値を連結して表示させるための関数です。
これら二つの関数を使用し、先ほどREGEXPで記述した正規表現文字列を作成していきます。
MariaDB [sample]> SELECT CONCAT('(^', (
-> SELECT GROUP_CONCAT(item_code SEPARATOR '-.*)|(^')
-> FROM item_table
-> WHERE type = 'Drink'
-> ), '-.*)');
+--------------------------------------------+
| (^djf500-.*)|(^djf500-.*)|(^dwo1000-.*) |
+--------------------------------------------+
作成した正規表現文字列をREGEXPと組み合わせる
最後に、CONCAT関数とGROUP_CONCAT関数を使って作成した正規表現文字列をREGEXPのサブクエリとして使用します。
MariaDB [sample]> SELECT * FROM code_table
-> WHERE code REGEXP(
-> SELECT CONCAT('(^', (
-> SELECT GROUP_CONCAT(item_code SEPARATOR '-.*)|(^')
-> FROM item_table
-> WHERE type = 'Drink'
-> ), '-.*)')
-> );
+----+-------------+
| id | code |
+----+-------------+
| 1 | djf500-160 |
| 2 | dwo1000-200 |
+----+-------------+
2 rows in set (0.000 sec)
これでitem_tableにあるドリンクに対応しているcodeを、code_tableから全て抽出することが出来ます。
まとめ
いかがでしたか？
今回は若干難しい内容にはなっていたと思いますが、このようにクエリによっては組み合わせが出来ないものでも、別のクエリをいくつか組み合わせることで代替させることが可能です。
しかしこれに気付くにはまず、出来るだけ多くのクエリを頭に入れておく必要があります。
現在SQLを学習されている方に特に言えることですが、どんなクエリが存在するのか、またそれらにはどういう作用があるのかを一通り調べてみると面白いかもしれません。
