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を付けた例は行順が決まっています。
| id | name | dept | salary |
|---|---|---|---|
| 1 | 青木 | 営業 | 300000 |
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 開発 | 350000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 総務 | 320000 |
| 6 | 森 | 総務 | NULL |
ANY・ALLの基本構文
SELECT *
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE dept = '営業'
);= ANYはINと同じ意味
expression = ANY (サブクエリ)は、サブクエリの結果のどれかと等しければtrueです。給与400000円以上の社員がいる部署は営業と開発なので、その部署に所属する青木・北野・中央・大和が残ります。dept IN (...)と書いた場合と同じ結果で、PostgreSQLの公式ドキュメントでもINは= ANYと同等と説明されています。
SELECT *
FROM employees
WHERE dept = ANY (
SELECT dept
FROM employees
WHERE salary >= 400000
)
ORDER BY id;| id | name | dept | salary |
|---|---|---|---|
| 1 | 青木 | 営業 | 300000 |
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 開発 | 350000 |
| 4 | 大和 | 開発 | 500000 |
> ANYは「どれか1つより大きい」
営業の給与は300000円と400000円です。salary > ANYは、そのどちらか一方より大きければtrueなので、実質的に最小の300000円より大きい社員が残ります。北野(400000円)、中央、大和、石井が該当します。青木は自分と同額の300000円より大きくないので外れ、給与NULLの森は比較が不明で残りません。
SELECT *
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE dept = '営業'
)
ORDER BY id;| id | name | dept | salary |
|---|---|---|---|
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 開発 | 350000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 総務 | 320000 |
> ALLは「すべてより大きい」
salary > ALLは、営業の300000円にも400000円にも勝つ必要があります。実質的に最大の400000円より大きい社員だけが残るので、結果は大和(500000円)の1人です。北野は自分と同額の400000円に勝てず、中央の350000円も400000円に届かないため外れます。ANYとは大きく結果が変わります。
SELECT *
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE dept = '営業'
)
ORDER BY id;| id | name | dept | salary |
|---|---|---|---|
| 4 | 大和 | 開発 | 500000 |
MAX・MINとの対応を1行ずつ確かめる
値が空でなくNULLもない集合なら、> ANYは> MIN、> ALLは> MAXと同じ結果です。営業の給与(300000円と400000円)で、ANY・ALLの式とMIN・MAXの式を同じ行に並べます。4つの列は、どの社員でも対応するANYとMIN、ALLとMAXが一致します。森のsalaryがNULLなので全列がNULLです。
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;| id | salary | any_result | min_result | all_result | max_result |
|---|---|---|---|---|---|
| 1 | 300000 | false | false | false | false |
| 2 | 400000 | true | true | false | false |
| 3 | 350000 | true | true | false | false |
| 4 | 500000 | true | true | true | true |
| 5 | 320000 | true | true | false | false |
| 6 | NULL | NULL | NULL | NULL | NULL |
サブクエリに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です。
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_null | any_true | any_unknown | all_false |
|---|---|---|---|
| NULL | true | NULL | false |
空集合では> ALLはtrue、> ANYはfalse
人事部には社員がいないので、サブクエリは0行です。ALLは「すべての行が条件を満たす」を調べるため、0行なら反例がなく、trueになります。ANYは条件を満たす行が1つもないのでfalseです。MAXやMINは0行のときNULLを返すため、> MAXと> MINはNULLになり、> ALL・> ANYとは結果が異なります。
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_result | max_result | any_result | min_result |
|---|---|---|---|
| true | NULL | false | NULL |
ANY・ALLでよくある間違い
NULLを含む列に> ALLを使うと0件になる
総務の給与は320000円とNULLです。salary > ALLでその部署の全員より高い社員を探すと、候補にNULLがあるため、どの社員もtrueにならず0行になります。NULLが入りうる列では、サブクエリにsalary IS NOT NULLを付けてから比較します。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:ANY・SOME(サブクエリ) — ANY・SOMEの真偽値、INとの対応、NULLと空集合の結果
- PostgreSQL公式:ALL(サブクエリ) — ALLの真偽値、NOT INとの対応、NULLと空集合の結果
- PostgreSQL公式:ANY・SOME(配列) — 配列に対するANY・SOMEの使い方