ヘッダーをスキップ

Oracle Database SQLリファレンス
10g リリース2(10.2)

B19201-02
目次
目次
索引
索引

戻る 次へ

9 SQL問合せおよび副問合せ

この章では、SQL問合せおよび副問合せについて説明します。

この章では、次の内容を説明します。

問合せおよび副問合せ

問合せとは、1つ以上の表またはビューからデータを検索する操作のことです。このマニュアルでは、トップレベルのSELECT文を問合せといい、他のSQL文の中でネストされた問合せを副問合せといいます。

この項では、問合せおよび副問合せの種類およびその使用方法について説明します。この章では、トップレベルの構文について説明します。すべての句のすべての構文およびこの文のセマンティクスについては、「SELECT」を参照してください。

select::=

画像の説明

subquery::=

画像の説明

単純な問合せの作成

SELECTキーワードの後、FROM句の前にある式のリストを、SELECT構文のリストといいます。SELECT構文のリストに、1つ以上の表、ビューおよびマテリアライズド・ビューからOracleデータベースが戻す行に含まれる1つ以上の列を指定します。SELECT構文のリストの要素によって、列のデータ型、長さおよび数が決定されます。

複数の表に同じ名前の列がある場合、表の名前でその列名を修飾する必要があります。それ以外の場合は、完全に修飾した列名はオプションとなります。ただし、明示的に表および列の参照を修飾することをお薦めします。表および列名を完全に修飾することで、Oracleの作業が少なくなります。

列の別名c_aliasを使用して、SELECT構文のリストの直前の式にラベルを付けると、列が新しい見出し付きで表示されます。別名によって、問合せ中にSELECT構文のリストの項目名を効果的に変更できます。別名はORDER BY句の中で使用できますが、問合せ内のその他の句には使用できません。

Oracleデータベースのオプティマイザに指示(ヒント)を与えるために、SELECT文中でコメントを使用できます。オプティマイザは、これらのヒントを使用して文の実行計画を選択します。ヒントの詳細は、「ヒントの使用方法」および『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

階層問合せ

表に階層データが含まれる場合、階層問合せ句を使用して階層順に行を選択することができます。

hierarchical_query_clause::=

画像の説明

START WITH句では、階層のルート行を指定します。

CONNECT BY句では、階層の親/子の行の関連を指定します。

CONNECT BY条件とPRIOR式は、いずれも相関関係のない副問合せの形式で指定できます。ただし、PRIOR式は順序を参照できません。そのため、CURRVALおよびNEXTVALは、無効なPRIOR式です。

CONNECT_BY_ROOT演算子を使用してSELECT構文のリスト内の列を問い合せることによって、階層問合せをさらに向上できます。この演算子は、親の行のみでなく、階層内のすべての祖先の行を戻すことによって、階層問合せのCONNECT BY [PRIOR]条件の機能を拡張します。

参照:

この演算子の詳細は、「CONNECT_BY_ROOT」および「階層問合せの例」を参照してください。 

Oracleは次のように階層問合せを処理します。

次に、Oracleはこれらの評価からの情報を使用して、次の手順で階層を形成します。

  1. Oracleは、階層のルート行を選択します。これらの行は、START WITH条件を満たすものです。

  2. Oracleは、各ルート行の子である行を選択します。子である各行は、1つのルート行に関してCONNECT BY条件を満たす必要があります。

  3. Oracleは、子である行の連続生成を選択します。まず、手順2で戻された子である行を選択し、その行にある子を選択します(以降同様に続きます)。現在の親である行に関するCONNECT BY条件を評価することによって、常に子を選択します。

  4. 問合せに結合を含まないWHERE句が含まれる場合、Oracleは、階層からWHERE句の条件を満たさないすべての行を排除します。条件を満たさない子である行をすべて排除するのではなく、各行に関してこの条件をそれぞれ評価します。

  5. Oracleは、図9-1に示す順序で行を戻します。この図では、親である行の下に子である行が表示されます。階層ツリーの詳細は、図3-1「階層ツリー」を参照してください。

    図9-1    階層問合せ


    画像の説明

親である行に対する子を検索するために、Oracleは、親である行のCONNECT BY条件のPRIOR式、および各行の他の式を表の中で評価します。条件がTRUEとなる行が、その親である行の子です。CONNECT BY条件に、問合せによって選択された行をさらにフィルタ処理するための他の条件を含めることができます。CONNECT BY条件に、副問合せを含めることはできません。

CONNECT BY条件が階層のループになった場合、Oracleはエラーを戻します。1つの行が別の行の親(または親の親または祖先)および子(または子の子または子孫)の場合、ループが発生します。


注意:

階層問合せでは、ORDER BYまたはGROUP BYを指定しないでください。指定すると、CONNECT BYの結果の階層順序が壊れます。同じ親の兄弟である行を順序付ける場合は、ORDER SIBLINGS BY句を使用します。「order_by_clause」を参照してください。 


階層問合せの例

CONNECT BY句の例

次の階層問合せは、CONNECT BYを使用して従業員とマネージャの関係を定義しています。

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
...
LEVELの例

次の例は、前述の例と似ていますが、LEVEL疑似列を使用して、親および子である行を表示しています。

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3
...
START WITH句の例

次の例は、START WITH句を追加して階層にルート行を指定し、SIBLINGSキーワードを使用したORDER BY句を追加して階層の順序を保持しています。

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3
...

hr.employees表で、Steven Kingは会社の最高責任者であるため、マネージャはいません。彼の従業員には、部門80のマネージャであるJohn Russellがいます。employees表を更新してRussellをKingのマネージャとして設定すると、データ内にループが作成されます。

UPDATE employees SET manager_id = 145
   WHERE employee_id = 100;

SELECT last_name "Employee", 
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
  2    3    4    5    6    7  ERROR:
ORA-01436: CONNECT BY loop in user data

CONNECT BY条件にNOCYCLEパラメータを指定すると、Oracleはループでも行を戻します。CONNECT_BY_ISCYCLE疑似列には、サイクルを含む行が表示されます。

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;

Employee                   Cycle  LEVEL Path
------------------------- ------ ------ -------------------------
Russell                        1      2 /King/Russell
Tucker                         0      3 /King/Russell/Tucker
Bernstein                      0      3 /King/Russell/Bernstein
Hall                           0      3 /King/Russell/Hall
Olsen                          0      3 /King/Russell/Olsen
Cambrault                      0      3 /King/Russell/Cambrault
Tuvault                        0      3 /King/Russell/Tuvault
Partners                       0      2 /King/Partners
King                           0      3 /King/Partners/King
Sully                          0      3 /King/Partners/Sully
McEwen                         0      3 /King/Partners/McEwen
...
CONNECT_BY_ROOTの例

次の例では、部門110の各従業員の名字、階層内で各従業員の上位に位置するマネージャ、マネージャと従業員間のレベル数および両者間のパスを戻します。

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

Employee        Manager         Pathlen Path
--------------- ------------ ---------- -----------------------------------
Higgins         Kochhar               1 /Kochhar/Higgins
Gietz           Kochhar               2 /Kochhar/Higgins/Gietz
Gietz           Higgins               1 /Higgins/Gietz
Higgins         King                  2 /King/Kochhar/Higgins
Gietz           King                  3 /King/Kochhar/Higgins/Gietz

次の例では、GROUP BY句を使用して、部門110の各従業員と階層内でその従業員の下位に位置する従業員の合計の給与を戻します。

SELECT name, SUM(salary) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT last_name as name, Salary
      FROM employees
      WHERE department_id = 110
      CONNECT BY PRIOR employee_id = manager_id)
      GROUP BY name;

NAME                      Total_Salary
------------------------- ------------
Gietz                             8300
Higgins                          20300
King                             20300
Kochhar                          20300

参照

 

UNION [ALL]、INTERSECTおよびMINUS演算子

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

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

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

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

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

参照:

暗黙的な変換の詳細は、表2-10「暗黙的な型変換のマトリックス」を参照してください。数値の優先順位の詳細は、「数値の優先順位」を参照してください。 

次の問合せは有効です。

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;
集合演算子の制限事項:

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

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
...
UNION ALLの例

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

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id  FROM locations 
UNION ALL 
SELECT location_id  FROM departments;

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

INTERSECTの例

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

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;
MINUSの例

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

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

問合せ結果のソート

ORDER BY句を使用して、問合せによって選択された行を順序付けます。位置のソートは次のような場合に有効です。

ORDER BY句による値のソートは、NLS_SORT初期化パラメータによって明示的に指定するか、NLS_LANGUAGE初期化パラメータによって暗黙的に指定します。ALTER SESSION文を使用すると、ソート方法を1つの言語ソート基準から別の言語ソート基準に動的に変更できます。ORDER BY句のNLS_SORTパラメータとNLSSORTファンクションを使用して、1つの問合せに特定のソート基準を指定することもできます。

参照:

NLSパラメータの詳細は、「NLSSORT」および『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。 

結合

結合とは、2つ以上の表、ビューまたはマテリアライズド・ビューの行を結合する問合せです。複数の表が問合せのFROM句に指定される場合、Oracleデータベースは結合を実行します。問合せのSELECT構文のリストは、これらの表のいずれかの任意の列を選択することができます。これらの表のいずれか2つに共通の列名を持つものがある場合、問合せの間、これらの列に対してすべての参照を明確にするために表の名前を付けて修飾する必要があります。

結合条件

ほとんどの結合問合せには、FROM句またはWHERE句のいずれかに1つ以上の結合条件が含まれます。結合条件によって、異なる表から2つの列が比較されます。結合を実行するために、Oracleデータベースは各表に1つずつ含まれている列を結合し、結合条件がTRUEになるようにします。結合条件の列をSELECT構文のリストに表示する必要はありません。

3つ以上の表を結合するために、Oracleはまず列を比較する結合条件に基づいて2つの表を結合し、結合された表と新規の表の列を含む結合条件に基づいて、さらにもう1つの表を結合します。すべての表が結果に結合されるまで、このプロセスを継続します。オプティマイザは、Oracleが結合条件に基づいて表を結合する順序、表の索引、および任意の使用可能な表の統計を決定します。

結合条件を含むIA WHERE句には、1つの表のみの列を参照する別の条件も含めることができます。これらの条件は、結合問合せによって戻された列をさらに制限することができます。


注意:

結合条件を含むWHERE句には、LOB列を指定できません。WHERE句でのLOBの使用については、他にも制限事項があります。詳細は、『Oracle Databaseアプリケーション開発者ガイド-ラージ・オブジェクト』を参照してください。 


等価結合

等価結合とは、等価演算子を含む結合条件での結合のことです。等価結合は、指定した列に同等の値を持つ行を結合します。オプティマイザが結合の実行を選択する内部アルゴリズムによって、1つの表の等価結合条件における列の合計サイズは、データ・ブロックのサイズ以下に制限される可能性があります。データ・ブロックのサイズは、初期化パラメータDB_BLOCK_SIZEによって指定されます。

参照:

「結合問合せの使用例:」 

自己結合

自己結合とは、自己の表結合のことです。この表はFROM句に2回指定され、結合条件の列名を修飾する表の別名が続きます。自己結合を実行するために、Oracleデータベースは結合条件を満たす表の行を結合して戻します。

参照:

「自己結合の使用例:」 

デカルト積

結合問合せの2つの表に結合条件がない場合、Oracleデータベースはデカルト積を戻します。この場合、1つの表の各行が別の表の各行に結合されます。デカルト積は常に多数の行を生成するため、有効ではありません。たとえば、それぞれが100行を持つ2つの表のデカルト積は10,000行を生成します。特にデカルト積を必要としないかぎり、必ず結合条件を指定してください。問合せが3つ以上の表を結合し、特定の組に対して結合条件を指定しない場合、オプティマイザは、中間のデカルト積を生成しないように結合順序を選択する可能性があります。

内部結合

内部結合単純結合)とは、結合条件を満たす行のみを戻す、複数の表の結合です。

外部結合

外部結合は、単純結合の結果を拡張します。外部結合は、結合条件を満たすすべての行と、結合条件を満たす行を除いた、一方の表のすべての行を戻します。

外部結合を使用すると、疎データ内の欠損を補完できます。このような結合はパーティション化された外部結合と呼ばれ、join_clause構文のquery_partition_clauseを使用して形成されます。疎データとは、時刻や部門などのディメンションの一部の値に対する行を持たないデータです。たとえば、販売データの表には通常、売上のない任意の日付の製品に対する行は存在しません。データの欠損の補完は、データの欠損によって分析計算が複雑になる場合や、疎データを直接問い合せた場合に一部のデータを見逃す可能性がある際に役立ちます。

参照:

  • 外部結合を使用した疎データの欠損の補完方法の詳細は、「join_clause」を参照してください。

  • グループ外部結合および疎データの欠損補完の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

 

Oracleの結合演算子よりも、FROM句のOUTER JOIN構文を使用することをお薦めします。Oracleの結合演算子(+)を使用した外部結合問合せには、次の規則と制限事項があります(これらの規則や制限事項は、FROM句のOUTER JOIN構文にはありません)。

WHERE句に表Bの列と定数を比較する条件が含まれる場合、Oracleがこの列に対してNULLを生成する表Aの列を戻すように、結合演算子(+)をこの列に適用する必要があります。それ以外の場合、Oracleは単純結合の結果のみを戻します。

2組以上の表の外部結合を行う問合せにおいて、単一表は他の1つの表のみに対してNULL生成された表になることができます。そのため、AとBの結合条件およびBとCの結合条件におけるBの列に、結合演算子(+)を適用することはできません。外部結合の構文については、「SELECT」を参照してください。

アンチ結合

アンチ結合は、述語の右側に対応する行を持たない述語の左側の行を戻します。つまり、右側の副問合せに一致しない(NOT IN)行を戻します。

参照:

「アンチ結合の使用例:」 

セミ結合

セミ結合は、述語の右側の複数の行が副問合せの条件を満たす場合に、述語の左側から行を重複させずにEXISTS副問合せに一致する行を戻します。

副問合せがWHERE句のORブランチに指定されている場合、セミ結合およびアンチ結合変換は実行できません。

参照:

「セミ結合の使用例:」 

副問合せの使用方法

副問合せは、複数部分の問合せに応答します。たとえば、Taylorの部門で働いている人を判断するには、まずTaylorが働く部門を判断する副問合せを使用できます。その後、親SELECT文で元の問合せに応答することができます。SELECT文のFROM句の副問合せは、インライン・ビューとも呼ばれます。また、SELECT文のWHERE句の副問合せは、ネストした副問合せとも呼ばれます。

副問合せは、別の副問合せを含むことができます。トップレベル問合せのFROM句内の副問合せレベルの数には、制限がありません。WHERE句には、最大255レベルの副問合せをネストできます。

副問合せにある列が、含まれる文の列と同じ名前を持つ場合、含まれる文の表の列に表名または別名で参照の接頭辞を付ける必要があります。文をさらに読みやすくするには、常に、表、ビューまたはマテリアライズド・ビューの名前または別名で副問合せの列を修飾します。

ネストした副問合せが、その副問合せから任意のレベル上位の親である文で参照する表の列を参照する場合、Oracleは相関副問合せを行います。親である文は、副問合せがネストしているSELECTUPDATEまたはDELETE文のいずれかです。相関副問合せは、親である文によって処理された各行を1回評価します。Oracleは、副問合せで指定された表内を検索した後、親である文で指定された表内を検索することによって、副問合せ内の未修飾列を解決します。

相関副問合せは、応答が親である文によって処理された各列の値に依存する複数部分の問合せに応答します。たとえば、相関副問合せを使用して、部門内で給与が平均給与以上の従業員を判断することができます。この場合、相関副問合せは独自で各部門の平均給与を計算します。

参照:

「相関副問合せの使用例:」 

副問合せは、次の用途に使用します。

ネストされた副問合せのネスト解除

副問合せは、親である文のWHERE句内にあるときはネストされています。ネストされた副問合せを持つ文を評価する場合、Oracleデータベースは、副問合せ部分を複数回評価する必要があり、効果的なアクセス・パスまたは結合を見逃してしまう可能性があります。

副問合せのネスト解除によって、副問合せの本体がネスト解除され、その副問合せを含む文の本体に結合されます。これによって、アクセス・パスおよび結合の評価時に、オプティマイザが副問合せと文を1つのものと判断します。オプティマイザは、ほぼすべての副問合せをネスト解除できますが、いくつか例外があります。これらの例外としては、階層副問合せ、およびROWNUM疑似列、集合演算子の1つ、ネストした集計ファンクション、副問合せの直接的な外部問合せブロックではない問合せブロックへの相関参照を含む副問合せなどがあります。

制約がない場合、オプティマイザは、次のネストされた副問合せを自動的にネスト解除します(ただし、ネスト解除しない場合もあります)。

ネスト解除された拡張副問合せを行うには、次のタイプの副問合せをネスト解除するようにオプティマイザに指示します。

DUAL表からの選択

DUALは、データ・ディクショナリとともにOracleデータベースによって自動的に作成された表です。DUALは、ユーザーSYSのスキーマにありますが、すべてのユーザーがDUALという名前でアクセスすることができます。DUALは、VARCHAR2(1)として定義されているDUMMY列を持ち、「X」値を持つ行を含みます。DUAL表から選択することは、定数式をSELECT文で計算する場合に便利です。DUALには1行以外存在しないため、定数は1回のみ戻されます。一方で、任意の表から定数、疑似列または式を選択できますが、値は表の行の数のみ戻されます。DUALから定数値を選択する例は、「SQLファンクション」を参照してください。

分散問合せ

Oracle分散データベース管理システム・アーキテクチャによって、Oracle NetおよびOracleデータベース・サーバーを使用するリモート・データベースにアクセスできます。名前の最後に@dblinkを追加して、リモート表、ビューまたはマテリアライズド・ビューを識別できます。dblinkは、リモート表、ビューまたはマテリアライズド・ビューを含むデータベースへのデータベース・リンクの完全な名前または部分的な名前である必要があります。

参照:

 

分散問合せの制限事項:

現在、分散問合せには、FOR UPDATE句によってロックされたすべての表、および問合せによって選択されたLONG列を持つすべての表が、同じデータベース上に位置している必要があるという制限があります。たとえば、次の文は、remoteデータベースのprint_media表からpress_releaseLONG値)を選択し、localデータベースのprint_media表をロックするため、エラーになります。

SELECT r.product_id, l.ad_id, r.press_release 
   FROM pm.print_media@remote r, pm.print_media l
   FOR UPDATE OF l.ad_id;

また、Oracleデータベースは現在、リモート表にあるユーザー定義型またはオブジェクトREFのデータ型を選択する分散問合せをサポートしていません。


戻る 次へ
Oracle
Copyright © 2006 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引