MySQL の ANY・SOME・ALL

ここでは MySQL の ANY・SOME・ALL 演算子についてご説明します。

サンプルデータベースのテーブルを使いますので、実際にスクリプトを実行してみたい方は、こちらのスクリプトを実行して school_db データベースを作成しておいてください。

MySQL の ANY と SOME 演算子

MySQL の ANY 演算子は、比較演算子とサブクエリーと共に次のような構文で使用し、[A] とサブクエリーの結果を指定した比較演算子で評価して、どれかひとつでも True になる時に True になります。

[A] 比較演算子 ANY (サブクエリー)

ANY と共に指定できる比較演算子は =、>、>=、<、<=、<>、!=　のどれかひとつです。

サブクエリーはカラム [A] と比較できる、ひとつのカラムを返すクエリーでなくてはなりません。

SOME は ANY のエイリアスなので、全く同じ方法で使えます。

それでは ANY 演算子を使ってみましょう。

次のような、students テーブルと exam_results テーブルがあります。

例えば、ANY 演算子を使って exam_results の score が 90 点以上をとったことがある students の情報を取得するには、次のようにできます。

SELECT 	*
FROM 	students
WHERE 	student_id = ANY
	  (SELECT 	student_id
	   FROM 	exam_results
	   WHERE 	score >= 90);

[実行結果]

90 点以上をとったことがある student_id が 3 と 4 の学生の情報が取得できていますね。

お気づきかもしれませんが、IN 演算子を使っても同様の結果がえられます。

IN 演算子は = ANY のエイリアスなのです。

MySQL の ALL 演算子は、比較演算子とサブクエリーと共に次のような構文で使用し、[A] とサブクエリーの結果を指定した比較演算子で評価して、全てが True になる時だけ True を返します。

[A] 比較演算子 ALL (サブクエリー)

ANY と同様に、ALL と共に指定できる比較演算子は =、>、>=、<、<=、<>、!=　のどれかひとつです。

サブクエリーも、カラム [A] と比較できる、ひとつのカラムを返すクエリーでなくてはなりません。

サブクエリーが返す値に NULL がひとつでもあると、評価の結果が Unknown になり、全ての評価結果が True になることはないので、結果セットにレコードが返らなくなります。

ALL 演算子を使う場合は、サブクエリーの NULL にご注意ください。

それでは ALL 演算子を使ってみましょう。

例えば、ALL 演算子を使って exam_results に存在しない student_id を持つ students の情報を取得するには、次のようにできます。

SELECT 	*
FROM 	students
WHERE 	student_id <> ALL
	  (SELECT 	student_id
	   FROM 	exam_results
       WHERE 	student_id IS NOT NULL);

[実行結果]

exam_results に存在しない student_id = 6 を持つ students の情報が取得できました。

exam_results テーブルには student_id が NULL のレコードが存在するので、WHERE student_id IS NOT NULL がないと、結果セットのレコード数は 0 になります。

NOT IN は <> ALL のエイリアスなので、代わりに NOT IN を使っても同様の結果が得られます。

以上、MySQL の ANY・SOME・ALL 演算子についてご説明しました。