集合演算子

集合演算子UNION、UNION ALL、INTERSECT、INTERSECT ALL、EXCEPT、EXCEPT ALL、MINUS、MINUS ALLを使用して、複数の問合せを組み合せることができます。集合演算子の優先順位はすべて同じです。SQL文に複数の集合演算子がある場合、カッコによって明示的に別の順序が指定されないかぎり、Oracle Databaseは左から右の順に評価します。

複合問合せを構成する各問合せと、それに対応するSELECT構文のリスト内の各式は、数値が一致し、データ型グループ(数値や文字など)が同じである必要があります。

集合演算子によって結合された2つの問合せが文字データを選択する場合、戻される値のデータ型は次のようにして決定されます。

両方の問合せが同じ長さのCHARデータ型の値を選択する場合、戻される値のデータ型はその長さのCHARになります。両方の問合せが異なる長さのCHARデータ型の値を選択する場合、戻される値は、長い方のCHAR値の長さを使用したVARCHAR2になります。
問合せのどちらか一方または両方が、VARCHAR2データ型の値を選択する場合、戻される値のデータ型はVARCHAR2になります。

集合演算子によって結合された2つの問合せが数値データを選択する場合、戻される値のデータ型は数値の優先順位によって決定されます。

すべての問合せがBINARY_DOUBLE型の値を選択する場合、戻される値のデータ型はBINARY_DOUBLEになります。
いずれの問合せもBINARY_DOUBLE型の値を選択せず、BINARY_FLOAT型の値を選択する場合、戻される値のデータ型はBINARY_FLOATになります。
すべての問合せがNUMBER型の値を選択する場合、戻される値のデータ型はNUMBERになります。

集合演算子を使用する問合せでは、データ型グループ間の暗黙的な変換は行われません。そのため、複合問合せの対応する式が文字データと数値データの両方になる場合は、エラーが戻されます。

キーワードALLを指定したINTERSECT演算子は2つ以上のSELECT文の結果を返し、行がすべての結果セットに表示されます。INTERSECT ALLのコンポーネント問合せに共通しているnull値は、結果セットの最後に返されます。

キーワードALLを指定したMINUS演算子は2つのSELECT文の結果を返しますが、行は最初の結果セットに表示され2番目の結果セットには表示されません。

最初の問合せにx個のnullがあり、2番目の問合せにy個のnullがあり、xがyより大きい場合、結果問合せセットの最後にxマイナスy個のNULLが返されます。MINUS ALLは、最初のSELECT文によって返された結果セットが2番目のSELECTによって返された結果セットのサブセットである場合は、行を返しません。

EXCEPT演算子はMINUSのシノニムであり、同じセマンティクスを持ちます。EXCEPT ALLは、最初の結果セットには存在するが、2番目の結果セットには存在しない行を戻します。ただし、最終的な結果に重複行が存在する場合があります。

EXCEPT ALL、MINUS ALL INTERSECT ALLは、SQL標準でNLS_SORT=BINARY_CI[AI]を使用できる場合、元の値ではなく同等の値を返します。

関連項目:

暗黙的な変換の詳細は、表2-9を参照してください。数値の優先順位の詳細は、「数値の優先順位の詳細」を参照してください

集合演算子の有効および無効なデータ型変換の例

次の問合せは有効です。

SELECT 3 FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;

この問合せは、次の複合問合せに暗黙的に変換されます。

SELECT TO_BINARY_FLOAT(3) FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;

次の問合せはエラーを戻します。

SELECT '3' FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;

集合演算子の制限事項

集合演算子には、次の制限事項があります。

集合演算子は、データ型がBLOB、CLOB、BFILE、VARRAYまたはネストした表である列に対しては無効になります。
UNION、INTERSECT、EXCEPTおよびMINUS演算子は、LONG列では有効でありません。
集合演算子の前のSELECT構文のリストに式が含まれている場合、order_by_clauseでその式を参照するには、式に列の別名を指定する必要があります。
for_update_clauseは、集合演算子とともに指定できません。
これらの演算子の副問合せには、order_by_clauseを指定できません。
TABLEコレクション式を含むSELECT文では、これらの演算子を使用できません。

ノート:

SQL規格に準拠するために、Oracleの今後のリリースでは、他の集合演算子より優先順位の高いINTERSECT演算子が提供されます。したがって、INTERSECT演算子と他の集合演算子を使用する問合せでは、カッコを使用して評価順序を指定してください。

UNIONの例

次の文は、UNION演算子によって2つの問合せの結果を結合しています。結果に重複行は含まれません。次の文は、他の表に存在していない列がある場合に、(TO_CHARファンクションを使用して)データ型を一致させる必要があることを示しています。

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

LOCATION_ID Department                     Warehouse
----------- ------------------------------ ---------------------------
       1400 IT
       1400                                Southlake, Texas
       1500 Shipping
       1500                                San Francisco
       1600                                New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction
       1700 Contracting
       1700 Control And Credit
...

UNION ALLの例

UNION ALL演算子がすべての行を戻すのに対して、UNION演算子は重複しない行のみを戻します。UNION ALL演算子は、重複行も対象に含めます。

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories
ORDER BY product_id;

SELECT location_id  FROM locations 
UNION ALL 
SELECT location_id  FROM departments
ORDER BY location_id;

問合せで複数回戻されるlocation_id値(1700など)は、UNION演算子では1回のみ戻されますが、UNION ALL演算子では複数回戻されています。

INTERSECTの例

次の文は、INTERSECT演算子によって2つの結果を結合しています。この場合、両方の問合せによって共通に戻される一意の行のみが戻されます。

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items
ORDER BY product_id;

MINUSの例

次の文は、MINUS演算子を使用して2つの結果を結合します。この場合、最初の問合せでは戻されるが、2番目の問合せでは戻されない一意の行のみが戻されます。

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items
ORDER BY product_id;

EXCEPTの例

最終結果セットから結果セットを除外する場合は、EXCEPTまたはMINUSを使用できます。この例では、2番目の問合せの結果は無視されます。

次の文は、EXCEPT演算子を使用して2つの結果を結合します。この場合、最初の問合せで返される一意の行のみを返し、2番目の問合せで返されるものは返しません。

SELECT product_id FROM inventories
EXCEPT
SELECT product_id FROM order_items
ORDER BY product_id;