SQL 概説

相関副照会

それ以前に言及された表のいずれかを参照できる副照会のことを相関副照会 といいます。 また、その副照会にはメインの照会の表への相関参照 があるともいいます。

次の例では、相関副照会ではない副照会を使って、 部署 'A00' の従業員のうち、 その部署の平均給与より高い給与を受け取る従業員の従業員番号と名前を出力しています。

 
     SELECT EMPNO, LASTNAME
        FROM EMPLOYEE
        WHERE WORKDEPT = 'A00'
          AND SALARY > (SELECT AVG(SALARY)
                           FROM EMPLOYEE
                           WHERE WORKDEPT = 'A00')

このステートメントの結果は、次のとおりです。

	EMPNO  LASTNAME
	------ ---------------
	000010 HAAS
	000110 LUCCHESSI

すべての部署の平均給与を調べたい場合、 すべての部署ごとにこのような副照会を評価することが必要になります。 それには、SQL の相関機能を使います。その場合、 外側の照会で使用されている表の各行ごとに 1 回ずつ繰り返して実行される副照会を 1 つ作成することができます。

次の例では、相関副照会を使って、 給与がその部署の平均給与より高い従業員をすべて表示しています。

 
     SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT
        FROM EMPLOYEE E1
        WHERE SALARY > (SELECT AVG(SALARY)
                           FROM EMPLOYEE E2
                           WHERE E2.WORKDEPT = E1.WORKDEPT)
        ORDER BY E1.WORKDEPT

この照会では、すべての部署についてそれぞれ 1 回ずつ評価されます。 結果は、次のとおりです。

     EMPNO  LASTNAME        WORKDEPT
     ------ --------------- --------
     000010 HAAS            A00     
     000110 LUCCHESSI       A00     
     000030 KWAN            C01     
     000060 STERN           D11     
     000150 ADAMSON         D11     
     000170 YOSHIMURA       D11     
     000200 BROWN           D11     
     000220 LUTZ            D11     
     000070 PULASKI         D21     
     000240 MARINO          D21     
     000270 PEREZ           D21     
     000090 HENDERSON       E11     
     000280 SCHNEIDER       E11     
     000100 SPENSER         E21     
     000330 LEE             E21     
     000340 GOUNOT          E21     

相関副照会を含む照会を作成するには、 副照会を含む外側の照会の通常の形式と同じ基本的な形式を使います。 しかし、外側の照会の FROM 文節の中で、 表名の直後に相関名を指定します。 副照会には、その相関名によって修飾した列参照を含めることができます。 たとえば、E1 が相関名なら、 E1.WORKDEPT は、外側の照会の中での表の現在行の WORKDEPT の値ということです。 副照会は、(概念的には) 外側の照会の表の各行ごとに再評価できます。

相関副照会を使うことによって、システムに作業をまかせて、 アプリケーションの中でのコーディング量を少なくすることができます。

DB2 では、修飾なしの相関参照が可能です。 たとえば、表 EMPLOYEE には LASTNAME という列があるのに対して、 表 SALES には SALES_PERSON という列はあっても LASTNAME という列がないとします。

 
     SELECT LASTNAME, FIRSTNME, COMM
        FROM EMPLOYEE
        WHERE 3 > (SELECT AVG(SALES)
                      FROM SALES
                      WHERE LASTNAME = SALES_PERSON)

この例では、 システムは一番内側の FROM 文節で LASTNAME 列があるかどうかを調べます。 それが見つからないなら、その外側の FROM 文節 (この例では一番外側の FROM 文節) を調べます。 必ずしも常に必要ではありませんが、 照会を読みやすくし、目的の結果を間違いなく得るようにするため、 できるだけ相関参照を修飾するようにしてください。

相関副照会のインプリメント

相関副照会を使うのは、どんな場合でしょうか? 1 つの手掛かりとなるのは列関数が使われるかどうかです。

ここで、 教育レベルが部署平均より高い従業員のリストを作成するとしましょう。

まず、選択リスト項目を決定する必要があります。 ここでの課題では「従業員のリストを作成する」ということです。 したがって、EMPLOYEE 表の LASTNAME によって、 従業員が一意に確定することになります。 また、この課題では教育レベル (EDLEVEL) と従業員の部署 (WORKDEPT) も条件の中に含まれています。 この課題には明示的にそれらの列を表示することが述べられていませんが、 それらを選択リストに含めるならわかりやすくなるでしょう。 ここまでで照会の一部分を次のように構成できます。

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE

次に、検索条件 (WHERE 文節) が必要です。 ここでの課題では「教育レベルが部署平均より高い」となっています。 つまり、表に含まれているすべての従業員について、 その従業員の属する部署の平均教育レベルを計算する必要があります。 このことは、相関副照会の説明に当てはまっています。 なんらかの未知の特性値 (現在の従業員の属する部署の教育レベルの平均) を各行ごとに計算する、ということです。 EMPLOYEE 表のための相関名が必要となります。

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE E1

必要な副照会は簡単なものです。 各部署ごとに教育レベルの平均を計算します。 SQL ステートメントは、全体として次のようになります。

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE E1
        WHERE EDLEVEL > (SELECT AVG(EDLEVEL)
                            FROM EMPLOYEE  E2
                            WHERE E2.WORKDEPT = E1.WORKDEPT)

結果は、次のとおりです。

     LASTNAME        WORKDEPT EDLEVEL
     --------------- -------- -------
     HAAS            A00           18
     KWAN            C01           20
     PULASKI         D21           16
     HENDERSON       E11           16
     LUCCHESSI       A00           19
     PIANKA          D11           17
     SCOUTTEN        D11           17
     JONES           D11           17
     LUTZ            D11           18
     MARINO          D21           17
     JOHNSON         D21           16
     SCHNEIDER       E11           17
     MEHTA           E21           16
     GOUNOT          E21           16

従業員の部署番号を出力するのではなく部署名を出力する場合を考えてみましょう。 必要な情報 (DEPTNAME) は別の表 (DEPARTMENT) に含まれています。 相関変数を定義する一番外側の照会は、結合照会にすることも可能です (複数の表からのデータ選択を参照)。

外側の照会で結合を使う場合、 結合する表のリストを FROM 文節に指定し、 該当表名の後に相関名を指定します。

前述の照会を、 部署の番号ではなく部署の名前を出力するよう変更するには、 選択リストの中の WORKDEPT を DEPTNAME に置き換えます。 FROM 文節には DEPARTMENT 表も含めることが必要であり、WHERE 文節には該当する結合条件を指定する必要があります。

変更後の照会は次のようになります。

     SELECT LASTNAME, DEPTNAME, EDLEVEL
        FROM EMPLOYEE E1, DEPARTMENT
        WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO
        AND EDLEVEL > (SELECT AVG(EDLEVEL)
                          FROM EMPLOYEE E2
                          WHERE E2.WORKDEPT = E1.WORKDEPT)

このステートメントの結果は、次のとおりです。

	LASTNAME        DEPTNAME                      EDLEVEL
	--------------- ----------------------------- -------
	HAAS            SPIFFY COMPUTER SERVICE DIV.       18
	LUCCHESSI       SPIFFY COMPUTER SERVICE DIV.       19
	KWAN            INFORMATION CENTER                 20
	PIANKA          MANUFACTURING SYSTEMS              17
	SCOUTTEN        MANUFACTURING SYSTEMS              17
	JONES           MANUFACTURING SYSTEMS              17
	LUTZ            MANUFACTURING SYSTEMS              18
	PULASKI         ADMINISTRATION SYSTEMS             16
	MARINO          ADMINISTRATION SYSTEMS             17
	JOHNSON         ADMINISTRATION SYSTEMS             16
	HENDERSON       OPERATIONS                         16
	SCHNEIDER       OPERATIONS                         17
	MEHTA           SOFTWARE SUPPORT                   16
	GOUNOT          SOFTWARE SUPPORT                   16

上記の例は、 副照会で使われる相関名は、 その相関副照会を含む照会の FROM 文節で定義しなければならないことを示しています。 しかし、そのような包含関係には複数のレベルのネストが関係することがあります。

一部の部署は従業員がごく少数である場合には、 そのためその平均教育レベルは正しい結果を導くものとはならないかもしれません。 平均教育レベルが従業員の比較の対象として意味のある数値となるようにするため、 部署には最低 5 人の従業員が含まれていなければならない、ということにしてみます。 それで、5 人以上の従業員が所属している部署だけを考慮しつつ、 従業員のうち所属する部署の平均教育レベルよりも高い教育レベルの従業員のリストを出力することになります。

一番外側の照会の中の各従業員ごとに、 その従業員の所属する部署の従業員数を数える必要があるため、 さらにもう 1 つの副照会が関係してきます。

     SELECT COUNT(*)
        FROM EMPLOYEE E3
        WHERE E3.WORKDEPT = E1.WORKDEPT

カウント結果が 5 以上の場合に限って平均を計算するようにします。

     SELECT AVG(EDLEVEL)
        FROM EMPLOYEE E2
        WHERE E2.WORKDEPT = E1.WORKDEPT
        AND 5 <= (SELECT COUNT(*)
                     FROM EMPLOYEE  E3
                     WHERE E3.WORKDEPT = E1.WORKDEPT)

最後に、教育レベルが、その部署の平均より高い従業員だけを含めます。

     SELECT LASTNAME, DEPTNAME, EDLEVEL
        FROM EMPLOYEE E1, DEPARTMENT
        WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO
        AND EDLEVEL >
        (SELECT AVG(EDLEVEL)
            FROM EMPLOYEE E2
            WHERE E2.WORKDEPT = E1.WORKDEPT
            AND 5 <=
            (SELECT COUNT(*)
                FROM EMPLOYEE E3
                WHERE E3.WORKDEPT = E1.WORKDEPT))

このステートメントの結果は、次のとおりです。

     LASTNAME        DEPTNAME                      EDLEVEL
     --------------- ----------------------------- -------
     PIANKA          MANUFACTURING SYSTEMS              17
     SCOUTTEN        MANUFACTURING SYSTEMS              17
     JONES           MANUFACTURING SYSTEMS              17
     LUTZ            MANUFACTURING SYSTEMS              18
     PULASKI         ADMINISTRATION SYSTEMS             16
     MARINO          ADMINISTRATION SYSTEMS             17
     JOHNSON         ADMINISTRATION SYSTEMS             16
     HENDERSON       OPERATIONS                         16
     SCHNEIDER       OPERATIONS                         17


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]