それ以前に言及された表のいずれかを参照できる副照会のことを相関副照会 といいます。 また、その副照会にはメインの照会の表への相関参照 があるともいいます。
次の例では、相関副照会ではない副照会を使って、 部署 '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