SQL Academy · レッスン

IN、ANY、ALLの比較

Q: 「IN、ANY、ALLの比較」で何を学びますか？

値の集合と比較します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

値の集合と比較します。

「IN、ANY、ALLの比較」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

IN、ANY、ALLとは

SQLクエリを作成する際、値を値の集合と比較する必要が生じることがよくあります。SQLには、そのための強力な演算子としてIN、ANY、ALLの3つがあります。

3つとも、値のリストを返すサブクエリと組み合わせて使用できます。見た目は似ていますが動作は異なるため、適切なものを選ぶことで、クエリをより表現力豊かで効率的にできます。

IN演算子

IN演算子は、値がリストまたはサブクエリの結果に含まれるいずれかの値と一致するかを確認します。3つの演算子の中で最も基本的なものです。

以下の例では、Sales、HR、Engineering部門で働く従業員を取得します。

SELECT name, department
FROM employees
WHERE department IN ('Sales', 'HR', 'Engineering');

サブクエリを使用したIN

INは、サブクエリと組み合わせると特に強力です。ハードコードしたリストの代わりに、別のテーブルから比較対象の値を直接取得できます。

ここでは、予算が100,000を超える部門に所属するすべての従業員を探します。

SELECT name, department
FROM employees
WHERE department IN (
  SELECT dept_name
  FROM departments
  WHERE budget > 100000
);

ANY演算子

ANY（SOMEと表記されることもあります）は、=、>、<、>=、<=などの比較演算子を使って、サブクエリが返す各値と値を比較します。

サブクエリの結果に含まれる少なくとも1つの値について比較がTRUEになれば、条件はTRUEになります。

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department = 'HR'
);

= ANYはINと同じ

知っておくべき重要な同値関係があります。= ANY (subquery)は、IN (subquery)と論理的に同一です。どちらも、サブクエリの結果の少なくとも1つと値が一致した場合にTRUEを返します。

次の2つのクエリは同じ結果になります。

-- Using IN
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');

-- Using = ANY
SELECT name FROM employees
WHERE department_id = ANY (SELECT id FROM departments WHERE location = 'NYC');

ALL演算子

ALLは、サブクエリの結果に含まれるすべての値と値を比較します。返されたすべての値について比較が成立する場合にのみ、条件はTRUEになります。

この例では、Intern等級のすべての給与より高い給与を得ている従業員を探します。つまり、最も給与の高いインターンよりも高い給与を得ている従業員です。

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE job_grade = 'Intern'
);

ANYとALLの比較

重要な違いは、ANYでは少なくとも1つの値について条件がTRUEになればよいのに対し、ALLではすべての値についてTRUEになる必要があることです。

次のように考えると分かりやすいでしょう。ANYはすべての比較に対するORのようなもので、ALLはすべての比較に対するANDのようなものです。

-- Returns rows where salary > at least one value in the subquery
SELECT name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Support');

-- Returns rows where salary > every value in the subquery
SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Support');

NOT INと<> ALLの比較

= ANYがINに対応するのと同様に、演算子<> ALLはNOT INと同値です。どちらも、サブクエリ内のどの値とも一致しない場合にのみTRUEを返します。

ただし注意が必要です。サブクエリがNULLを1つでも返すと、SQLの三値論理によりNOT INはまったく行を返しません。

-- Using NOT IN
SELECT name FROM employees
WHERE department_id NOT IN (SELECT id FROM departments WHERE location = 'Remote');

-- Using <> ALL (equivalent)
SELECT name FROM employees
WHERE department_id <> ALL (SELECT id FROM departments WHERE location = 'Remote');

NOT INにおけるNULLの落とし穴

特に注意すべき点があります。NOT INで使用するサブクエリがNULLを返すと、NOT IN条件全体がすべての行についてUNKNOWNと評価され、空の結果セットが返されます。

これを避けるには、サブクエリでNULLを除外するか、NULLを安全に処理できるNOT EXISTSを使用してください。

-- Dangerous: if manager_id has any NULL, no rows are returned
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- Safe: filter NULLs explicitly
SELECT name FROM employees
WHERE id NOT IN (
  SELECT manager_id FROM employees
  WHERE manager_id IS NOT NULL
);

実践例：高収入の従業員を見つける

3つの演算子を現実的なシナリオで使ってみましょう。name、department、salary列を持つemployeesテーブルがあるとします。

いずれか1つの部門の平均給与を上回る従業員、つまり少なくとも1つの部門の平均より高い給与を得ている従業員を探します。

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT AVG(salary)
  FROM employees
  GROUP BY department
)
ORDER BY salary DESC;

どの演算子を使うべきか

3つの演算子を選ぶ際の簡単な指針を示します。

IN — 固定のリストがある場合や、サブクエリに対して単純な等価比較を行う場合に使用します。
= ANY — INと同じですが、>や<など、ほかの比較演算子も使用できます。
ALL — 条件がすべての値に対して成立する必要がある場合に使用します（例：MAXを使わずに最大値より大きい値を求める場合）。
NOT IN — 便利ですが、NULLに注意が必要です。サブクエリにNULLが含まれる可能性がある場合は、NOT EXISTSを優先してください。

理解度チェック

IN、ANY、ALLについての理解度を確認しましょう。

レッスンのまとめ

このレッスンでは、IN、ANY、ALLを使って値を集合と比較する方法を学びました。

主なポイント：

INは集合への所属を確認します。値がリストまたはサブクエリ内のいずれかの項目と一致すればTRUEです。
= ANYはINと同値です。> ANYなどほかの演算子を使うと、少なくとも1つの一致についてTRUEになります。
ALLでは、サブクエリが返すすべての値に対して条件が成立する必要があります。
サブクエリにNULLが含まれていると、NOT INは何も通知せずに行を返さなくなります。必ずこの点に対処してください。
NULLが存在する可能性がある場合は、NOT INより安全な代替手段としてNOT EXISTSを使用してください。

よくある質問