複数の表にあるデータの結合

例

結合における、ネストされた表の式およびユーザー定義の表関数

結合のオペランドは、単一の表の名前よりも複雑な場合があります。 次の項目のいずれかを結合オペランドとして指定できます。

ネストされた表の式

括弧で囲み、その後に相関名を続けた全選択。 相関名を使用すると、その式の結果を参照できます。

ネストされた表の式を結合で使用すると、結合に使用する一時表を作成する場合に役立ちます。 ネストされた表の式は、どちら側の一致しない行を組み込むかに応じて、結合の左右どちらのオペランドとしても指定できます。

ユーザー定義表関数

表を戻すユーザー定義関数。

ネストされた表の式を結合で使用すると、表の値を別の表に結合する前に、値に対して何らかの操作を実行する場合に役立ちます。

例: 相関参照の使用

次の SELECT ステートメントで、ネストされた表の式に使用される相関名は CHEAP_PARTS です。 この相関名を使用して、式から戻される列を参照できます。 この場合、これらの相関参照は CHEAP_PARTS.PROD# および CHEAP_PARTS.PRODUCT です。

SELECT CHEAP_PARTS.PROD#, CHEAP_PARTS.PRODUCT
   FROM (SELECT PROD#, PRODUCT
            FROM PRODUCTS
            WHERE PRICE < 10) AS CHEAP_PARTS;

結果表は、以下の出力のようになります。

PROD#       PRODUCT
=====       ===========
505         SCREWDRIVER
30          RELAY

CHEAP_PARTS を定義する表式の中で相関参照が発生しないため、これらの相関参照は有効です。 相関参照は、副照会の階層内の高レベルでの表指定を起点とします。

例：ネストされたテーブル式を結合の右オペランドとして使います。

次の照会には、PROJECTS 表との左外部結合の右オペランドとして全選択 (太字) が含まれます。 相関名は TEMP です。 この例では、PROJECTS 表からの一致しない行が組み込まれ、ネストされた表の式からの一致しない行は組み込まれません。

SELECT PROJECT, COALESCE(PROJECTS.PROD#, PRODNUM) AS PRODNUM,
       PRODUCT, PART, UNITS
FROM PROJECTS LEFT JOIN
      (SELECT PART,
          COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM,
          PRODUCTS.PRODUCT
       FROM PARTS FULL OUTER JOIN PRODUCTS
             ON PARTS.PROD# = PRODUCTS.PROD#) AS TEMP
   ON PROJECTS.PROD# = PRODNUM;

例：ネストされたテーブル式を結合の左側のオペランドとして使います。

次の照会には、PRODUCTS 表との左外部結合の左オペランドとして全選択が含まれます。 相関名は PARTX です。 この例では、ネストされた表の式からの一致しない行が組み込まれ、PRODUCTS 表からの一致しない行は組み込まれません。

SELECT PART, SUPPLIER, PRODNUM, PRODUCT
  FROM (SELECT PART, PROD# AS PRODNUM, SUPPLIER
           FROM PARTS
           WHERE PROD# < '200') AS PARTX
        LEFT OUTER JOIN PRODUCTS
           ON PRODNUM = PROD#;

結果表は、以下の出力のようになります。

PART        SUPPLIER         PRODNUM     PRODUCT
=======     ============     =======     ==========
WIRE        ACWF             10          GENERATOR
MAGNETS     BATEMAN          10          GENERATOR
OIL         WESTERN_CHEM     160         ----------

PROD# は文字フィールドであるため、 Db2 は文字を比べ、結果行のセットを決定します。 したがって、文字「30」は「200」より大きいため、PROD# が「30」に等しい行は、結果には現われません。

例: 結合のオペランドとして表関数を使用する

例えば、CVTPRICE は、PRODUCTS 表の価格を指定した通貨に変換し、この通貨単位での価格を入れた PRODUCTS 表を返す表関数であるとします。 次の照会に類似した照会を実行すると、 パーツの表、供給業者表、および選択した通貨の製品価格表を得ることができます。

SELECT PART, SUPPLIER, PARTS.PROD#, Z.PRODUCT, Z.PRICE
   FROM PARTS, TABLE(CVTPRICE(:CURRENCY)) AS Z
   WHERE PARTS.PROD# = Z.PROD#;

結合における表指定の相関参照

ネストされた表の式の結果を参照するには、相関名を使用します。 式の相関名を指定した後でこの相関名を参照する場合、その参照は相関参照 と呼ばれます。

ネストされた表の式の中に、または表関数への引数として、 相関参照を含めることができます。 この場合の両方に適用される基本的な規則は、 相関参照は、副照会の階層内の高レベルでの表指定を起点とする 必要があるということです。 表の指定が相関参照の左にあり、また相関参照が次の文節のいずれかにあれば、 相関参照と、これが参照する表の指定を同じ FROM 文節で使用することもできます。

• キーワード TABLE が前にあるネストされた表の式
• 表関数の引数

相関参照の詳細については、 参照の相関名を参照してください。

同じ FROM 文節で、他の表への相関参照を含む表関数または表式は、 全外部結合または右外部結合に参加できません。 次の例で、表の指定における相関参照の有効な使用方法を示します。

以下の例で、相関参照 T.C2 が有効な理由は、その相関参照が参照する表指定 T がその左にあるためです。

SELECT T.C1, Z.C5
  FROM T, TABLE(TF3(T.C2)) AS Z
  WHERE T.C3 = Z.C4;

逆の順序で結合を指定し、T が TABLE(TF3(T.C2)) の後にある場合は、T.C2 は無効です。

この例で、相関参照 D.DEPTNO が有効な理由は、これが現れるネストされた表の式の前に TABLE があり、表指定 D が FROM 節のネストされた表の式の左に現れるためです。

SELECT D.DEPTNO, D.DEPTNAME,
  EMPINFO.AVGSAL, EMPINFO.EMPCOUNT
  FROM DEPT D,
    TABLE(SELECT AVG(E.SALARY) AS AVGSAL,
            COUNT(*) AS EMPCOUNT
            FROM EMP E
            WHERE E.WORKDEPT=D.DEPTNO) AS EMPINFO;

キーワード TABLE を除去すると、D.DEPTNO は無効になります。