7.6. EXISTS, ANY, ALL

演算子 EXISTS, ANY, ALL はサブクエリが返す値に対して、「TRUE」または「FALSE」の評価を行い、メインクエリの WHERE 句で使用されます。サブクエリの返す値の集合を結果セットと呼びます。EXISTS 演算子は結果セットの有無を評価します。ANY 演算子は結果セットのいずれかの値を評価対象とします。ALL 演算子は結果セットの全ての値を評価対象とします。

7.6.1. EXISTS

EXIST 演算子はサブクエリの生成した値が存在する場合は「TRUE」、存在しない場合は「FALSE」を返します。ANY 演算子、ALL 演算子は「=」「<」等の比較演算子と共に用いて、全体で「TRUE」または「FALSE」の評価をしますが、EXISTS 演算子だけは単独で「TRUE」または「FALSE」の評価を行います。また、サブクエリの返す値は複数の行のものでも、複数の列のものでも構いません。

/* EXISTS */

  SELECT * FROM 商品表
         WHERE EXISTS
         (SELECT * FROM 商品表
           WHERE 商品名 = 'BEER');

 商品コード 商品名 単価
 ───────── ───── ────
 101        BEER   500
 102        JUICE  400
 103        TEA    400
 104        WINE   650

sample.19-1 のクエリは、サブクエリで商品表から商品名が 'BEER' と一致するものを出力し、値が出力されれば、商品表の全てを表示するというものです。商品表には商品名が 'BEER' というものがありますので、商品表の全てが参照されます。sample.19-1 のように、EXISTS にはいくつの列が返されても構いません。

相関サブクエリについても EXISTS を使用することが出来ます。sample.19-2 では、受注表から'JUICE'を受注した受注番号と顧客コードを参照します。相関サブクエリでは、メインクエリから受け取った各行ごとに EXISTS 演算子は「TRUE」か「FALSE」の評価を行います。

/* EXISTS */

  SELECT 受注番号,顧客コード FROM 受注表 JJ
         WHERE EXISTS
         (SELECT * FROM 商品表 SS
           WHERE 商品名 = 'JUICE'
                 AND JJ.商品コード = SS.商品コード);

 受注番号 顧客コード
 ─────── ─────────
 10001    001
 10004    004
 10007    003

sample.19-2 では、まず受注表から受注番号が '10001' である行をサブクエリで使用します。この行の商品コードを商品表の商品コードと比較します。また同時にその商品名が 'JUICE' であるかもチェックしています。この行の商品コードは '102' で、同時に商品表の商品名は 'JUICE' となります。この行と商品表の各行の比較が終わったとき、サブクエリは商品表の商品名が 'JUICE' の行について、全ての列の値を出力します。EXISTS 演算子はこれを受け取って「TRUE」と評価します。そして受注番号が '10001' の行は出力されることが決まり、受注番号と顧客コードが表示されるということになります。そして同じことが受注表の全ての行に対して行われ、結局 'JUICE' を注文したときの行についてのみ出力されることにな ります。

通常のサブクエリでは、EXISTS 演算子は結果の存在有無を一度しか評価しませんが、相関サブクエリではそれぞれの行に対して評価を行います。

EXISTS 演算子には、NOT EXISTS と記述することで EXISTS 演算子と反対の役割を果たすようになります。すなわち、サブクエリの値が存在するときは「FALSE」、存在しないときは「TRUE」と評価します。

7.6.2. ANY

ANY 演算子はサブクエリの生成した値のいずれかの値を評価対象とします。ANY 演算子は比較演算子と共に用いられ、結果セットの値のいずれかが比較演算子の関係を満たす場合に「TRUE」、満たさなければ「FALSE」を返します。また、サブクエリが生成する値は複数でも構いません。

次の図はメインクエリの WHERE 句の述語を表しています。サブクエリから生成された値の集合である結果セット [value A, value B, value C, ...] でいずれかが、[列名] の値と比較して比較演算子との関係を満たせば、「TRUE」を返します。満たさない場合はもちろん「FALSE」を返します。

([列名] + 比較演算子 + value A) OR ([列名] + 比較演算子 + value B) OR ([列名] + 比較演算子 + value C) ... というようなイメージです。

sample.19-3 では、受注表において納品日が顧客コードが '002' のものと同じであるものを参照します。

/* ANY */

  SELECT 顧客コード,納品日 FROM 受注表
         WHERE 納品日 = ANY
         (SELECT 納品日 FROM 受注表
           WHERE 顧客コード = '002');

 顧客コード 納品日
 ───────── ─────
 001        20010401
 002        20010401
 004        20010402
 002        20010402
 001        20010402
 003        20010402

サブクエリでは、顧客コードが '002' の納品日を出力します。納品日が [20010401, 20010402] という結果セットが作られます。この結果セットの中のいずれかについてメインクエリの WHERE 句で「TRUE」になるものについて、顧客コードと納品日が出力されます。これは、前に説明した IN を用いた場合と同じ効果が得られます。sample.19-4 は IN を使用して、sample.19-3 と同じ出力を得るものです。

/* IN */

  SELECT 顧客コード,納品日 FROM 受注表
         WHERE 納品日 IN
         (SELECT 納品日 FROM 受注表
           WHERE 顧客コード = '002');

 顧客コード 納品日
 ───────── ─────
 001        20010401
 002        20010401
 004        20010402
 002        20010402
 001        20010402
 003        20010402

しかし、ANY 演算子は IN 演算子では出来なかったことが出来るようになっています。ANY 演算子は比較演算子として、「=」だけでなく「>」「<=」などの不等号も使用できるのです。sample.19-5 では受注表において、受注個数が顧客コード '004' の受注個数の最大よりも小さなものについて、参照します。

/* ANY */

  SELECT 受注番号,顧客コード,受注個数 FROM 受注表
         WHERE 受注個数 < ANY
         (SELECT 受注個数 FROM 受注表
           WHERE 顧客コード = '004');

 受注番号 顧客コード 受注個数
 ─────── ───────── ───────
 10003    004        150
 10004    004        80
 10005    002        110
 10007    003        45
 10008    004        135
 10009    003        65
 10011    003        60

sample.19-5 ではサブクエリで受注表から顧客コードが '004' のものについて、受注個数を出力しています。この結果セットは [80, 135, 150, 175] です。これらの値のうちいずれかについて、受注表の各行の受注個数が小さくなる行が出力されます。受注個数が 175 未満であるものについて出力されることになります。

また ANY 演算子と全く同じ機能を持つ演算子があります。SOME 演算子を用いることにより、ANY 演算子と同じ効果が得られます。

7.6.3. ALL

ALL 演算子はサブクエリの生成した値の全てを評価対象とします。ANY 演算子と同じように、ALL 演算子も比較演算子と共に用いられます。結果セットの値の全てが比較演算子との関係を満たす場合に「TRUE」、満たさなければ「FALSE」を返します。また、ANY 演算子と同様に、サブクエリが生成する値は複数でも構いません。

先ほどの図とほぼ変わりありませんが、次の図で言うと、結果セット [value A, value B, value C, ...] の全てが、[列名] の値と比較して比較演算子との関係を満す時「TRUE」を返します。満たさない場合は「FALSE」を返します。

([列名] + 比較演算子 + value A) AND ([列名] + 比較演算子 + value B) AND ([列名] + 比較演算子 + value C) ... というようなイメージです。

sample.19-6 は、sample.19-5 の ANY を ALL に変えただけです。受注表から受注個数が顧客コード '004' の受注個数の最小値より小さなものについて、受注番号、顧客コード及び受注個数を参照します。

/* ALL */

  SELECT 受注番号,顧客コード,受注個数 FROM 受注表
         WHERE 受注個数 < ALL
         (SELECT 受注個数 FROM 受注表
           WHERE 顧客コード = '004');

 受注番号 顧客コード 受注個数
 ─────── ───────── ─────────
 10007    003        45
 10009    003        65
 10011    003        60

sample.19-6 ではサブクエリで受注表から顧客コードが '004' のものについて、受注個数を出力しています。この結果セットは [80, 135, 150, 175] です。これらの値のうち全てについて、受注表の各行の受注個数が小さくなる行が出力されます。受注個数が 80 未満であるものについて出力されることになります。