MYSQLのWHEREで他テーブルの値と比較してみた

2024年3月19日

MYSQLなどデータベースの強みとして「検索の多様さ」があります。
固定値で検索するだけでなく、流動的な値でも検索ができます。

ところが今回次のような検索条件が必須となりました。
「他のテーブルの値と比較する。」

他のテーブルの値を条件に検索するというものですが、そんなことできるのだろうか?
と思っていたらできました。

忘備録として記録いたします。

今回やりたいこと

今回やりたいことは次の通りです。

• 他のテーブルの値と一致しないものを抽出する
• 他のテーブルの値と一致するものを抽出する

いずれもMYSQLと他のテーブルとの比較が必要です。
どうやれべできるかググりました。

他テーブルとの比較はサブクエリで実現

他のテーブルと比較する方法をググったところ、サブクエリでできるということがわかりました。
具体的に以下に解説します。

（１）テーブル1の構成

1～10の値が入っています。

（２）テーブル２の構成

中身は、３と６の2つの数字のみ入っています。

テーブル2に入っている値を除外する

まずはテーブル2に入っている値を除外する比較をやってみました。
テーブル1の３、６を除く8データが取得できればOKです。

SQL文

SELECT * FROM base WHERE list_no <> ALL (SELECT chk_no FROM chk);

WHEREで、一致していない値なので比較演算子として<>を使用します。
ちなみに　!= でもOKでした。

ALLは見慣れない記述ですが意味的には「サブクエリで取得した値を１こずつ取り出す」というものです。ALL句をつけることで、1件ずつ比較する処理が成立します。
ちなみにALLを外して記述したところエラーになりました。

かっこの中身はサブクエリなので、比較対象を取り出すSELECT文を記述します。
ちなみに、読み込む項目は1つにしないと文法エラーなります。

＊を指定してみたところ、文法エラーになりました。

実行結果

予定通り３，６を除く8つのデータが抽出できました。

テーブル2の値と一致するもののみ抽出

こんどはテーブル2に入っている値と同じもののみを抽出する処理を実行してみました。
テーブル１の3と6の2データを抽出できればOKです。

SQL文

SELECT * FROM base WHERE list_no = ALL (SELECT chk_no FROM chk);

先ほどは比較演算子を一致しないにしていたので、今回は一致する意味の＝を指定しました。

実行結果

一致する値は存在しているのに、一致している値はないとなりました。
なんでだろうと思い調べてみました。

一致と不一致では文法が異なります

ALL句を=で比較だと文法エラーにはならないですが、期待する動作もしませんでした。
かといって、「同じ」を意味する比較演算子は「=」のみです。
試しに「LIKE」を使ってみたところ、文法エラーになりました。

どうしたものかと思っていたところ、そういえばMYSQLで複数の値の中からどれかと一致しているかを比較する文法があったことを思い出しました。

IN句で、使い方としては　比較項目 IN (3,6) というようにIN句の中に直接値を記述します。
ここでひらめきました。

IN句でサブクエリを指定すればいいのではないか？
ひらめきは正解でした。
以下のように記述すれば、テーブル2と一致するデータを抽出できました。

ALL句の代わりにIN句を指定し、比較演算子を外せばテーブル2と一致する値を抽出できました。

文法的には不思議ですがやりたいことはできます。

今回は、他のテーブルの値で比較をするということをやりましたが、「一致」と「不一致」で文法が異なることが少々分かりにくかったです。

不一致は比較演算子が必要なのに対し、一致の場合は比較演算子の代わりにIN句を使用するのはすぐにはわかりませんでした。

それでも、やりたいことができたのでOKです。
他のテーブルの値で比較ができるということは、流動的な条件で真偽判定ができるということです。
応用すれば、様々な処理に対応できます。

本記事がまたお役に立てば幸いです。