SQL練習室
← SQLガイド

SQLのANYとALLとは?IN・MAX・MINとの対応と空集合・NULLの挙動

ANYとALLは、比較演算子とサブクエリの結果を組み合わせ、「どれか1つに対して成り立つか」「すべてに対して成り立つか」を調べる式です。社員の給与を使い、INやMAX・MINとの対応を確かめたうえで、空集合とNULLのときの意外な結果まで見ていきます。

例で使う表

employeesは1行が社員1人です。deptは部署、salaryは月給(円)で、総務の森は給与が未入力のNULLです。営業は300000円と400000円、開発は350000円と500000円、総務は320000円とNULLです。人事部の社員は1人もいません。ORDER BYを付けた例は行順が決まっています。

employees:社員6人(森のsalaryはNULL)
idnamedeptsalary
1青木営業300000
2北野営業400000
3中央開発350000
4大和開発500000
5石井総務320000
6森総務NULL

ANY・ALLの基本構文

sql
SELECT *
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE dept = '営業'
);

= ANYはINと同じ意味

expression = ANY (サブクエリ)は、サブクエリの結果のどれかと等しければtrueです。給与400000円以上の社員がいる部署は営業と開発なので、その部署に所属する青木・北野・中央・大和が残ります。dept IN (...)と書いた場合と同じ結果で、PostgreSQLの公式ドキュメントでもINは= ANYと同等と説明されています。

sql
SELECT *
FROM employees
WHERE dept = ANY (
  SELECT dept
  FROM employees
  WHERE salary >= 400000
)
ORDER BY id;
実行結果
idnamedeptsalary
1青木営業300000
2北野営業400000
3中央開発350000
4大和開発500000

> ANYは「どれか1つより大きい」

営業の給与は300000円と400000円です。salary > ANYは、そのどちらか一方より大きければtrueなので、実質的に最小の300000円より大きい社員が残ります。北野(400000円)、中央、大和、石井が該当します。青木は自分と同額の300000円より大きくないので外れ、給与NULLの森は比較が不明で残りません。

sql
SELECT *
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE dept = '営業'
)
ORDER BY id;
実行結果
idnamedeptsalary
2北野営業400000
3中央開発350000
4大和開発500000
5石井総務320000

> ALLは「すべてより大きい」

salary > ALLは、営業の300000円にも400000円にも勝つ必要があります。実質的に最大の400000円より大きい社員だけが残るので、結果は大和(500000円)の1人です。北野は自分と同額の400000円に勝てず、中央の350000円も400000円に届かないため外れます。ANYとは大きく結果が変わります。

sql
SELECT *
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE dept = '営業'
)
ORDER BY id;
実行結果
idnamedeptsalary
4大和開発500000

MAX・MINとの対応を1行ずつ確かめる

値が空でなくNULLもない集合なら、> ANYは> MIN、> ALLは> MAXと同じ結果です。営業の給与(300000円と400000円)で、ANY・ALLの式とMIN・MAXの式を同じ行に並べます。4つの列は、どの社員でも対応するANYとMIN、ALLとMAXが一致します。森のsalaryがNULLなので全列がNULLです。

sql
SELECT
  id,
  salary,
  salary > ANY (SELECT salary FROM employees WHERE dept = '営業') AS any_result,
  salary > (SELECT MIN(salary) FROM employees WHERE dept = '営業') AS min_result,
  salary > ALL (SELECT salary FROM employees WHERE dept = '営業') AS all_result,
  salary > (SELECT MAX(salary) FROM employees WHERE dept = '営業') AS max_result
FROM employees
ORDER BY id;
実行結果
idsalaryany_resultmin_resultall_resultmax_result
1300000falsefalsefalsefalse
2400000truetruefalsefalse
3350000truetruefalsefalse
4500000truetruetruetrue
5320000truetruefalsefalse
6NULLNULLNULLNULLNULL

サブクエリにNULLがあると> ALLはtrueにならない

総務の給与は320000円とNULLです。500000 > ALLは、320000円には勝つものの、NULLとの比較が不明になります。falseが1つも見つからないため、結果はtrueでもfalseでもなくNULLです。一方、500000 > ANYは、320000円に勝つ時点でtrueが確定します。100000 > ANYは、trueが見つからず、NULLの比較が残るのでfalseではなくNULLです。100000 > ALLは、320000円に負ける時点でfalseです。

sql
SELECT
  500000 > ALL (SELECT salary FROM employees WHERE dept = '総務') AS all_with_null,
  500000 > ANY (SELECT salary FROM employees WHERE dept = '総務') AS any_true,
  100000 > ANY (SELECT salary FROM employees WHERE dept = '総務') AS any_unknown,
  100000 > ALL (SELECT salary FROM employees WHERE dept = '総務') AS all_false;
実行結果
all_with_nullany_trueany_unknownall_false
NULLtrueNULLfalse

空集合では> ALLはtrue、> ANYはfalse

人事部には社員がいないので、サブクエリは0行です。ALLは「すべての行が条件を満たす」を調べるため、0行なら反例がなく、trueになります。ANYは条件を満たす行が1つもないのでfalseです。MAXやMINは0行のときNULLを返すため、> MAXと> MINはNULLになり、> ALL・> ANYとは結果が異なります。

sql
SELECT
  100 > ALL (SELECT salary FROM employees WHERE dept = '人事') AS all_result,
  100 > (SELECT MAX(salary) FROM employees WHERE dept = '人事') AS max_result,
  100 > ANY (SELECT salary FROM employees WHERE dept = '人事') AS any_result,
  100 > (SELECT MIN(salary) FROM employees WHERE dept = '人事') AS min_result;
実行結果
all_resultmax_resultany_resultmin_result
trueNULLfalseNULL

ANY・ALLでよくある間違い

NULLを含む列に> ALLを使うと0件になる

総務の給与は320000円とNULLです。salary > ALLでその部署の全員より高い社員を探すと、候補にNULLがあるため、どの社員もtrueにならず0行になります。NULLが入りうる列では、サブクエリにsalary IS NOT NULLを付けてから比較します。

sql
SELECT *
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE dept = '総務'
)
ORDER BY id;

実際に1問解いてみる

= ANY

給与が450000円以上の社員がいる部署に所属する社員の全列を取得してください。部署の候補をサブクエリで作り、= ANYで比較します。行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

ANY・ALLについてよくある質問

ANYとINはどう違いますか?
INは= ANYと同じ意味です。ANYの利点は、>、<、<>など = 以外の比較演算子も使えることです。「どれかより大きい」はINでは書けません。
> ALLと> (SELECT MAX(...))は同じですか?
サブクエリが1行以上返し、NULLがなければ同じ結果です。0行のときは、ALLがtrue、MAXがNULLなので結果が変わります。NULLを含むと、falseになる比較がない限りALLはNULLになり、MAXはNULLを無視した最大値と比べます。
ANYとALLのどちらを使うか迷います。
「どれか1つ」に勝てばよいならANY、「全員」に勝つ必要があるならALLです。> ANYは最小値、> ALLは最大値との比較と考えると、結果を予想しやすくなります。
ANY・ALLにサブクエリが2列以上返ると?
スカラーの左辺と比べる形では、サブクエリは1列だけを返す必要があります。複数列で比べたいときは、左辺に行コンストラクタ((a, b)のような形)を使います。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。