NATURAL JOINでは、次のファンクションが暗黙的に実行されます。

• 列を圧縮するために共通列を単一列に結合し、それによって共通列を修飾できないようにします。

• 表参照間の共通列を使用して等価結合を実行します。

NATURALキーワードが問合せに指定されている場合は、明示的な結合条件を指定できません。次に、自然結合の例を示します。共通列C1の表示は1回のみです。他のタイプの結合条件からは、表の参照で発生する回数分の共通列が返されます。

SQL> SELECT * cont> FROM TABLE1 NATURAL LEFT OUTER JOIN TABLE2; C1 TABLE1.C2 TABLE2.C4 10 15 AA 20 25 CC 30 35 NULL 3 rows selected

次の例は、前の例に示すNATURAL LEFT OUTER JOINを明示的に実行する複雑さを示しています。

SQL> SELECT cont> COALESCE (TABLE1.C1, TABLE2.C1) AS C1, cont> TABLE1.C2, TABLE2.C4 cont> FROM TABLE1 LEFT OUTER JOIN TABLE2 cont> ON TABLE1.C1 = TABLE2.C1; C1 TABLE1.C2 TABLE2.C4 10 15 AA 20 25 CC 30 35 NULL 3 rows selected

NATURALキーワードは、INNER結合、LEFT OUTER結合、RIGHT OUTER結合およびFULL OUTER結合に対して指定できます。

名前付きの一致する列を共有しない2つの表参照間の自然結合は、デカルト積になります。

ON predicate

結合の基となる検索条件を指定します。条件には、指定された2つのオペランドの列を指定するか、条件が副問合せ内にある場合は外部参照を指定できます。

OPTIMIZE AS query-name

名前を問合せに割り当てます。RDMS$DEBUG_FLAGS論理名を定義するか、またはSTRATEGYオプションを指定したSET FLAGSを使用して、問合せ結果の作成に使用されるアクセス・メソッドを確認できます。次の例は、OPTIMIZE AS句の使用方法を示しています。

SQL> DELETE FROM EMPLOYEES E cont> WHERE EXISTS ( SELECT * cont> FROM SALARY_HISTORY S cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID cont> AND S.SALARY_AMOUNT > 75000) cont> OPTIMIZE AS DEL_EMPLOYEE; Leaf#01 FFirst RDB$RELATIONS Card=19 . . . ~Query Name : DEL_EMPLOYEE . . . 7 rows deleted

OPTIMIZE FOR

選択式を指定する文に対して優先するオプティマイザ計画を指定します。次のオプションを使用できます。

• FAST FIRST
  FAST FIRST用に最適化された問合せが、これにより全体のスループットが低下しても、できるかぎり速やかにデータをユーザーに返します。
  問合せが早期に取り消されることがある場合は、FAST FIRSTの最適化を指定する必要があります。FAST FIRST最適化の有力な候補は、レコードのグループをユーザーに表示する対話型アプリケーションです。このようなアプリケーションでは、ユーザーは最初の数画面の確認後、問合せを中断することもできます。たとえば、シングルトンSELECT文では、FAST FIRST最適化がデフォルトで設定されます。
  最適化レベルを明示的に設定しない場合は、FAST FIRSTがデフォルトとなります。

• TOTAL TIME
  アプリケーションをバッチで実行し、問合せのすべてのレコードにアクセスして更新またはレポートの書込みを行う場合は、TOTAL TIME最適化を指定する必要があります。TOTAL TIME最適化は、ほとんどの問合せに効果があります。
  次の例は、優先される最適化モードを設定するためのDECLARE CURSOR構文を示しています。

  SQL> DECLARE TEMP1 TABLE CURSOR cont> FOR cont> SELECT * cont> FROM EMPLOYEES cont> WHERE EMPLOYEE_ID > '00400' cont> OPTIMIZE FOR FAST FIRST; SQL> -- SQL> DECLARE TEMP2 TABLE CURSOR cont> FOR cont> SELECT LAST_NAME, FIRST_NAME cont> FROM EMPLOYEES cont> ORDER BY LAST_NAME cont> OPTIMIZE FOR TOTAL TIME;

• SEQUENTIAL ACCESS
  順次アクセスの使用を強制します。このオプションは、厳密なパーティション化機能を使用する表では特に有用です。
  表の記憶域マップの属性がPARTITIONING IS NOT UPDATABLEの場合、記憶域へのデータのマッピングは厳密に守られます。これは厳密なパーティション化と呼ばれます。このような表に対する問合せで順次アクセスを行うと、オプティマイザでは、すべてのパーティションがスキャンされるのではなく、WHERE制限と一致しないパーティションが除外されます。
  次の例は、選択した行を特定のパーティションから削除する問合せを示しています。この表には、オプティマイザで選択される可能性のある索引もいくつか含まれています。このため、OPTIMIZE句によって順次アクセスが強制的に行われます。

  SQL> delete from PARTS_LOG cont> where parts_id between 10000 and 20000 cont> and expire_date < :purge_date cont> optimize for sequential access;

  
  このような問合せで行われるアクセスはすべて順次アクセスです。索引アクセスを使用する同様の問合せを比較して、使用中のI/Oが受け入れられるかに注意します。

OPTIMIZE USING outline-name

選択式とアウトラインでアウトラインIDが異なる場合でも、選択式で使用する問合せアウトラインを明示的に指定します。

次の例では、WOMENS_DEGREESという名前のアウトラインを作成する問合せを示します。

SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE, D.DEGREE_FIELD, D.YEAR_GIVEN cont> FROM EMPLOYEES E, DEGREES D WHERE E.SEX = 'F' cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID cont> ORDER BY LAST_NAME

OPTIMIZE USING句を使用してWOMENS_DEGREESアウトラインを指定すると、次の例に示すように、わずかに異なる問合せを実行する場合でも、Oracle Rdbでは、WOMENS_DEGREESアウトラインの使用が試行されます。

SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE, D.DEGREE_FIELD, D.YEAR_GIVEN cont> FROM EMPLOYEES E, DEGREES D WHERE E.SEX = 'F' cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID cont> ORDER BY LAST_NAME cont> LIMIT TO 10 ROWS cont> OPTIMIZE USING WOMENS_DEGREES; ~S: Outline WOMENS_DEGREES used <-- the query uses the WOMENS_DEGREES outline . . . E.LAST_NAME E.EMPLOYEE_ID D.DEGREE D.DEGREE_FIELD D.YEAR_GIVEN Boyd 00244 MA Elect. Engrg. 1982 Boyd 00244 PhD Applied Math 1979 Brown 00287 BA Arts 1982 Brown 00287 MA Applied Math 1979 Clarke 00188 BA Arts 1983 Clarke 00188 MA Applied Math 1976 Clarke 00196 BA Arts 1978 Clinton 00235 MA Applied Math 1975 Clinton 00201 BA Arts 1973 Clinton 00201 MA Applied Math 1978 10 rows selected

アウトラインの作成方法の詳細は、「CREATE OUTLINE文」を参照してください。

OPTIMIZE WITH

以前のOracle Rdbバージョンで使用されていたDEFAULT、行数が少ない方が選択されると仮定するAGGRESSIVE、問合せにリテラルを使用して索引で予備評価を行うSAMPLEDの3つの最適化制御のいずれかを選択します。

ORDER BY integer

ORDER BY value-expr

結果表の行の順序を指定します。中間結果表の行は、ORDER BY句で指定された式の値によってソートされます。中間結果表とは、選択式の前の句（HAVING、GROUP BY、WHEREまたはFROM）を評価するときにSQLで作成される結果表のことです。

ORDER BY句の列は、次の2つの方法で参照できます。

• 値式

• 列番号。指定する整数は、結果表の列の左から右方向の位置を示します。

選択リストの列がファンクション、演算式、またはUNION、MINUS、EXCEPT、INTERSECT演算子の結果のいずれかから派生している場合は、整数を使用してORDER BY句の列を識別する必要があります。

ORDER BY句の式の識別に名前または数値のいずれを使用しているかに関係なく、式はソート・キーと呼ばれます。

複数のソート・キーを使用すると、最初の式がメジャー・ソート・キー、後続のキーがマイナー・ソート・キーとして処理されます。つまり、行はまず最初の値式に基づいてグループ化されます。次に、2番目の値式を使用して各グループ内で行がソートされ、同様の処理が繰り返されます。結果表のすべての列に対してソート・キーを指定しないかぎり、指定された最後のソート・キーと同じ値のある行は任意の順序になります。

次の例は、値式を使用したORDER BY句の使用方法を示しています。

SQL> SELECT * FROM EMPLOYEES cont> ORDER BY EXTRACT (YEAR FROM BIRTHDAY), cont> TRIM(FIRST_NAME) || TRIM(LAST_NAME); 00190 O'Sullivan Rick G. 78 Mason Rd. NULL Fremont NH 03044 M 12-Jan-1923 1 None 00231 Clairmont Rick NULL 92 Madiso7 Drive NULL Chocorua NH 03817 M 23-Dec-1924 2 None 00183 Nash Walter V. 197 Lantern Lane NULL Fremont NH 03044 M 19-Jan-1925 1 None 00177 Kinmonth Louis NULL 76 Maple St. NULL Etna NH 03750 M 7-Apr-1926 1 None 00240 Johnson Bill R. 20 South St NULL Milford NH 03055 M 13-Apr-1927 2 None . . .

qualified-join

結合表から返された結果を修飾および変更します。修飾結合には、いくつかの種類があります。

• INNER JOIN

• LEFT OUTER JOIN

• RIGHT OUTER JOIN

• FULL OUTER JOIN

• NATURAL JOIN

INNER JOINおよびOUTER JOINでは、最初の表参照のすべての列を2番目の表参照のすべての列に組み合せたものが結果表になります。NATURAL JOINでは、表参照間で共通列（同名の列）を要約したものが結果表になります。詳細は、次の引数を参照してください。

RIGHT OUTER JOIN

右側で指定された表参照のすべての行を保持し、結果の左側で指定された表参照の行と一致させます。左側で指定された表参照に一致する列が存在しない場合は、NULLが表示されます。次に例を示します。

SQL> SELECT * cont> FROM TABLE1 RIGHT OUTER JOIN TABLE2 cont> ON TABLE1.C1 = TABLE2.C1; TABLE1.C1 TABLE1.C2 TABLE2.C1 TABLE2.C4 10 15 10 AA NULL NULL 15 BB 20 25 20 CC 3 rows selected

FULL OUTER JOINの例は、INNER JOINの例の結果にTABLE1およびTABLE2の一致しない行を追加したものと同じになります。

SELECT *

中間結果表のすべての列結果（FROM句で参照されるすべての表参照のすべての列）を使用するようSQLに指示します。選択式にGROUP BY句が含まれている場合、ワイルドカード（*）はGROUP BY句にある式のみを指定すると解釈されます。

SELECT ALL

重複している行を結果表から除去しないことを指定します。デフォルトはALLです。

SELECT DISTINCT

重複している行を結果表から除去するように指定します。

SELECT name.*

表名、ビュー名または相関名で参照される表参照のすべての列を使用するようSQLに指示します。選択式のFROM句で名前を指定する必要があります。この形式のワイルドカード表記法とSELECT *は混在させることはできません。

ワイルドカードを使用するか、または値式を明示的にリストすると、選択リストに指定する列の数は、次の結果表の列の数になります。

select-list

最終的な結果表の（FROM句で名前が付けられた表参照から派生する）値式のリストを識別します。

UNION

UNION DISTINCT

1つの表の列値に別の表の列値を付加することによって、ある選択式の結果と別の選択式を1つの結果表にマージします。

次の例では、給与が$50,000を超え博士号を持っている現在の従業員のEMPLOYEE_IDが抽出されます。重複している行は結果表から除去されます。

SQL> SELECT EMPLOYEE_ID cont> FROM CURRENT_SALARY cont> WHERE SALARY_AMOUNT > 50000 cont> UNION cont> SELECT EMPLOYEE_ID cont> FROM DEGREES cont> WHERE DEGREE = 'PhD'; EMPLOYEE_ID 00164 00166 00168 00169 00172 00182 . . . 00418 00435 00471 38 rows selected

UNION ALL

重複している行を結果表から除去しないことを指定します。デフォルトでは、UNION演算子によって重複している行が削除されます。

次の例では、結果表から重複している行が返されます。

SQL> SELECT LAST_NAME, SEX FROM EMPLOYEES WHERE LAST_NAME = 'Nash' cont> UNION ALL cont> SELECT LAST_NAME, SEX FROM EMPLOYEES WHERE LAST_NAME = 'Lapointe'; LAST_NAME SEX Nash M Nash M Lapointe F Lapointe F 4 rows selected

USING

結合の基となる列を指定します。列名は、修飾結合で指定された両方の表参照で定義する必要があります。USING句では、同名の列間で等価結合条件が暗黙的に指定され、結果として共通列が作成されます。

SQL> SELECT * cont> FROM TABLE1 LEFT OUTER JOIN TABLE2 cont> USING (C1); C1 TABLE1.C2 TABLE2.C4 10 15 AA 20 25 CC 30 35 NULL 3 rows selected

前の例に示すように、結果として共通列は単一列に結合されます。したがって、このような列は修飾できません。結合列は問合せで参照できます。次に例を示します。

SQL> SELECT * cont> FROM TABLE1 LEFT OUTER JOIN TABLE2 cont> USING (C1) cont> WHERE C1 BETWEEN 20 AND 30; C1 TABLE1.C2 TABLE2.C4 20 25 CC 30 35 NULL 2 rows selected

WHERE predicate

中間結果表を生成するためにSQLで評価される条件を指定します。条件は、FROM句で作成された中間結果表の行ごとに評価されます。条件がTRUEとなった表の行は、選択式内で後に指定される句に対する別の中間結果表になります。

WHERE句の条件で指定する列名は、次のいずれかである必要があります。

• FROM句で作成された中間結果表の列を識別すること。

• 外部参照であること（WHERE句が列選択式の一部である場合にのみ可能）。外部参照の詳細は、第2.2.4.2項を参照してください。

通常、WHERE句内の条件では集計関数を参照できません。たとえば、次の文は無効です。

SQL> SELECT * FROM EMPLOYEES WHERE MAX(LAST_NAME) > 'X'; %SQL-F-INVFUNREF, Invalid function reference

この制限範囲の例外については、この項の「使用方法」を参照してください。

使用方法

• 表参照を含むFROM句ですでに指定されている他の相関名と同じもの、またはFROM句で公開されている表名の表識別子と同じ相関名は、表参照で指定できません。この制限は、ANSI/ISO SQL規格に準拠しています。
  この制限により、次の例に示すエラー・メッセージが発生します。

  SQL> SELECT * FROM JOBS, CURRENT_JOB JOBS; %SQL-F-CONVARDEF, Column qualifier JOBS is already defined SQL> -- SQL> SELECT * FROM JOBS J, CURRENT_JOB J; %SQL-F-CONVARDEF, Column qualifier J is already defined

• INNER結合、LEFT OUTER結合、RIGHT OUTER結合およびFULL OUTER結合の順序は、ON条件で決まります。構文をカッコで囲む場合は、対応するON条件もそのカッコ内に配置してください。

• ストアドと非ストアドの両方のプログラムおよびモジュールに埋め込まれた選択式では、プログラムの実行時ではなく、プリコンパイル時に選択リストのワイルドカードが拡張されます。

• 選択式で列名を指定するときに、列名がパラメータと同じ場合は、列名とともに相関名を使用してパラメータと区別する必要があります。

• 通常、WHERE句内の条件では集計関数を参照できません。この制限の唯一の例外は、WHERE句内のファンクションに引数として外部参照が存在する場合です。これは、次のいずれかの列選択式に対してWHERE句が条件となっている場合のみ可能です。

  • 選択リスト項目内の値式

  • HAVING句に対する条件の一部

    SQL> -- Display departments that have total current SQL> -- salaries greater than their projected budget: SQL> SELECT DEPARTMENT cont> FROM CURRENT_INFO cont> GROUP BY DEPARTMENT cont> HAVING DEPARTMENT IN cont> (SELECT DEPARTMENT_NAME cont> FROM DEPARTMENTS cont> WHERE SUM (CURRENT_INFO.SALARY) > BUDGET_PROJECTED); 0 rows selected

• GROUP BY句を使用しない場合は、選択リストが次のいずれかである必要があります。

  • 集計関数のみのリストであること

  • ファンクションを含まれないこと

  
  たとえば、次の問合せはファンクションと列名が混在しているため、評価できません。

  SQL> SELECT EMPLOYEE_ID, AVG(SALARY_AMOUNT) cont> FROM SALARY_HISTORY; %SQL-F-INVSELLIS, Select list cannot mix columns and functions without GROUP BY

  
  GROUP BY句なしでファンクションのリストを指定すると、選択式によって生成される結果表に含まれる行は1行のみになります。

• GROUP BY句またはHAVING句を使用する場合は、選択リストの列名が次のいずれかである必要があります。

  • GROUP BY句にも存在していること

  • 集計関数内で指定されていること

  
  たとえば、次の文は無効です。

  SQL> SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES cont> GROUP BY LAST_NAME ;

  
  ただし、SQLでは、ワイルドカードはGROUP BY句で指定した列名のみを参照すると解釈されるため、選択リストでワイルドカードを使用できます。
  たとえば、次の文は有効です（この場合、ワイルドカードはLAST_NAME列のみを指定します）。

  SQL> SELECT * FROM EMPLOYEES cont> GROUP BY LAST_NAME ;

• 選択式の結果表に存在する列の特性は、選択リストで列がどのように指定されたかによって異なります。

  • 選択リストの列名から直接派生した結果表の列には、CREATE TABLE文で指定したソース列の名前、データ型および他の特性が継承されます。

  • 選択リストの他の値式から派生した列には、AS句を使用して名前を付けることができます。データ型は、値式の結果表と同じものになります。

  • 選択リストのリテラルから派生した列の場合、NULL値は無効です。COUNTファンクションから派生した列の場合も、NULL値は無効です。パラメータから派生した列の場合、パラメータにインジケータ・パラメータが存在していればNULL値は有効です。

• 次の制限は、GROUP BY句で値式を使用する場合に適用されます。

  • 構文の類似している値式が選択リストに存在している必要があります。

  • GROUP BY句を使用する値式では、アスタリスク（*）がサポートされません。

• EXCEPT DISTINCT演算子は、NOT ANY条件を使用して再度記述できます。実際に、RdbサーバーではEXCEPT DISTINCTはこの方法で実装されています。次に例を示します。

  SQL> select manager_id from departments cont> except distinct cont> select employee_id from employees;

  
  この問合せは次のように再度記述することができます。

  SQL> select manager_id cont> from departments d cont> where not exists (select * cont> from employees e cont> where e.employee_id = d.manager_id cont> or (e.employee_id is null cont> and d.manager_id is null));

  
  このように単純な問合せでもEXCEPT書式が読みやすくなります。選択する列数が増加すると、それに応じてNOT EXISTS副問合せも複雑になります。

• INTERSECT DISTINCT演算子は、EXISTS条件を使用して再度記述できます。実際に、RdbサーバーではINTERSECT DISTINCTはこの方法で実装されています。従業員でもあるマネージャをすべて表示する例を次に示します。

  SQL> select manager_id from departments cont> intersect distinct cont> select employee_id from employees;

  
  この問合せは次のように再度記述することができます。

  SQL> select manager_id cont> from departments d cont> where exists (select * cont> from employees e cont> where e.employee_id = d.manager_id cont> or (e.employee_id is null cont> and d.manager_id is null));

  
  このように単純な問合せでもINTERSECT書式が読みやすくなります。選択する列数が増加すると、それに応じてEXISTS副問合せも複雑になります。

• EXCEPTおよびINTERSECTでは、重複行がすべて削除されます。これらの演算子では、最初の選択リストのそれぞれの値が2番目の選択リストの列と一致しているか、これらの列の両方がNULLの場合は、行が重複しているとみなされます。
  重複している一致セマンティクスは、NOT EXISTSおよびEXISTSを使用する問合せに再度記述することで明確にできます。

---

戻る  次へ

Copyright © 1987, 2003, Oracle. All rights reserved.

目次 索引