やりたいこと
sql文を用いて、あるカラムに指定した単語(文字列)が含まれていたらそれを除外してデータを取得したい！
ex: userテーブルのnameカラムに「サンプル」or「sample」と入っていたら除外したい。
[table:userの構造]
| カラム名 | データ型 |
|---|---|
| id | int |
| name | varchar(255) |
[table:userの中身]
| id | name |
|---|---|
| 1 | 山田太郎 |
| 2 | サンプル1 |
| 3 | qiita |
| 4 | sample2 |
| 5 | キー太 |
| 6 | sample |
実行方法(sql)と実行結果
①
SELECT * FROM users WHERE name not in ('sample') and name not in('サンプル ')
| id | name |
|---|---|
| 1 | 山田太郎 |
| 2 | サンプル1 |
| 3 | qiita |
| 4 | sample2 |
| 5 | キー太 |
②
SELECT * FROM users WHERE name not like 'sample' and name not like 'サンプル';
| id | name |
|---|---|
| 1 | 山田太郎 |
| 2 | サンプル1 |
| 3 | qiita |
| 4 | sample2 |
| 5 | キー太 |
③
SELECT * FROM users WHERE name not in ('%sample%') and name not in('%サンプル% ')
| id | name |
|---|---|
| 1 | 山田太郎 |
| 2 | サンプル1 |
| 3 | qiita |
| 4 | sample2 |
| 5 | キー太 |
| 6 | sample |
④
SELECT * FROM users WHERE name not like '%sample%' and name not like '%サンプル%'
| id | name |
|---|---|
| 1 | 山田太郎 |
| 3 | qiita |
| 5 | キー太 |
やったこと
まず①番②番のsqlでは、not in ('sample')、 not like 'sample'というように、
ワイルドカード(%)で挟むのを忘れていたために上手く除外できていなかったのだと思い、
次にワイルドカードを使って③と④を試した。
⬇︎
しかし、③番のnot in ('%sample%')は上手くいかず、
④番のnot like ('%sample%')でようやく上手くいった!!
解説
①番②番のsqlでは、id=6,name=sample のデータだけ除外された。
これは、このデータがそれぞれnot in('sample'),not like('sample')
にひっかかった結果が実行されたからであった。
③番ではどのデータも除外されず、すべてのデータが取得された。
これは、not in ではワイルドカードが使えないからであった。
④番では完全一致のid=6のデータだけではなく、部分一致のid=2とid=4も合わせて、
つまり「sample」または「サンプル」という文字列が含まれているデータは全て除外され、
今回やりたいことが上手く実行できた！！
まとめ
どうやら、「not in」ではワイルドカードは使えず、
これを使う場合には完全一致の場合のみ使うべきであると思われる。
部分一致の場合のみ除外したい場合は、
ワイルドカードが使える「not like」を使うことをお勧めする。
余談。
実は今回の試みでは最初論理演算子も間違っていたために、
どこで間違えているのかがわかりにくく、④の結果にたどり着くまで非常に時間がかかった。笑
正解：
SELECT * FROM users WHERE name not like '%sample%' and name not like '%サンプル%'
間違い：
SELECT * FROM users WHERE name not like '%sample%' or name not like '%サンプル%'
⬆️[間違い]とされているsqlでは全てのデータが取得されてしまった。
なぜなら、論理演算子「OR(または)」は「 どちらか一方の条件に一致していればよい」わけであるから
例えば、id=2のname=サンプル1については、一つ目の条件である['sample'に部分一致しない]にひっかかりデータを取ってきてしまう。
日本語で考えると”「sample」または「サンプル」を除外”となるから間違いやすいが、
両方の条件に一致しなくてはならない、論理演算子ANDを使うべきであった！
（...ということで以上余談でした。ちゃんちゃん♩）