17.10 - 外部結合の定義 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-171K-JPN
Language
日本語 (日本)

外部結合は内部結合を拡張したものです。外部結合と内部結合の違いは、外部結合の結果に一致していない行の集合があることです。

複数のテーブルの外部結合は、指定された結合条件に従ってそれらのテーブルの内部結合を実行し、さらに内部結合条件と一致しない左側の結合テーブルおよび右側の結合テーブルの一方または両方のテーブルの行を返します。その際、一致しないフィールドにおいて結果行はnullで拡張されます。

一致していない行とは、内部テーブルに一致するものがない外部テーブルの行のことです。外部結合の結果の一致する行とは、結合条件を満たす行のことで、内部結合の結果にある一致する行とまったく同じです。

通常、外部結合は、コンポーネントの代数UNION ALLとして定義されます。 UNION ALLは重複行を許可するため。これは厳密にはリレーショナル演算子ではありません。

例: SELECTとJoin

例えば、次のようなSELECT文を考えてみます。

     SELECT offerings.course_no, offerings.location, enrollment.emp_no
     FROM offerings
     LEFT OUTER JOIN enrollment
     ON offerings.course_no = employee.course_no;

説明:

オブジェクト 対応構文要素
offerings left_table
enrollment right_table
offerings.course_no=employee.course_no join_condition

ON句のサブクエリーでのSAMPLE句の指定に関するルール

ON句の述部として使用されるサブクエリーでは、SAMPLE句は指定できません。

単純な外部結合の例

テーブルt1およびt2に次のようなテーブルがあるとします。

t1   t2
FK     PK  
x1 y1   x2 y2
1 1   1 1
2 2   3 3
3 3   4 4
4 4   5 5

これらのテーブルの内部結合は次の問合わせで指定されます。

     SELECT x1, y1, x2, y2
     FROM t1, t2
     WHERE x1=x2;
x1 y1 x2 y2
1 1 1 1
3 3 3 3
4 4 4 4

これらのテーブルの左側の外部結合は次の問合わせで指定されます。

     SELECT x1, y1, x2, y2
     FROM t1 LEFT OUTER JOIN t2 ON x1=x2;

この問合わせの結果は次のとおりです。

x1 y1 x2 y2
1 1 1 1
2 2 NULL NULL
3 3 3 3
4 4 4 4

外部結合は選択した外部キー/プライマリ キーのリレーションの内部結合と等価である

元のテーブル定義では、x2はt2のプライマリ キーです。次の参照保全制約がt1について定義されているとします。

     FOREIGN KEY (x1) REFERENCES t2 (x2);

この制約では、t1にあるx1(外部キー)のそれぞれの値が1行のみのプライマリ キーとして、t2テーブルにx2列として存在していなければなりません。したがって、t1の2番目の行を有効にすることはできません。これで、外部結合の結果にある一致していない行が結果から消去されます。単純な外部結合の例を参照してください。外部結合の結果は同じテーブルの内部結合の結果に変換されます。なぜなら、参照保全制約では結合結果に一致しない行が存在できないことを保証しているからです。

数学的に言うと、外部キー/プライマリ キー リレーションシップは、外部キー列の集合にある値がプライマリ キー列の集合にある値の集合の適切な部分集合を形成することを示します。数学的に表現すると、S FK ⊂ S PKとなります。ここでS FKは外部キー値の集合を表わし、S PKはプライマリ キー値の集合を表わします。

したがって、親テーブルのプライマリ キー列の集合と子テーブルの外部キー列の集合の間の等価結合では、外部結合が対応する内部結合と等価になるように、一致しない行の集合は常に空になります。このプロパティは結合インデックスを使った問合わせの最適化に非常に役立ちます。詳細については、<Teradata Vantage™ - SQLデータ定義言語 - 詳細トピック、B035-1184>と<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

外部結合のコンポーネント

以下に示すテーブルの抽象図とベン図を参照してください。



この図は内部結合の構成要素と同じです。 しかし、この図のコンポーネントの結合方法は大きく異なります。

table_Aをセクション1と2の入った行セットとして定義します。table_Aをleft_tableとして参照します。

table_Bをセクション1と3の入った行セットとして定義します。table_Bをright_tableとして参照します。

2つのテーブルの外部結合のコンポーネントは、以下のように定義します。

コンポーネント 定義
1 2つのテーブルの内部結合。すべての結合条件を適用したON句を使って定義されます。また、これらの行は外部結合の一致する行を提供します。
2 left_tableにあるすべての行。left_tableからの一致していない列ごとにnullで拡張されたセクション1に入れられたのではない行に限ります。それらは左側の外部結合の一致しない行です。
3 right_tableにあるすべての行。ただし、right_tableから選択された一致しない列ごとにnullで拡張されてセクション1に入れられたのでない行に限ります。それらは右側の外部結合の一致しない行です。


外部結合のタイプ

結合されるセクションは、外部結合のタイプによって定義されます。

外部結合のタイプ 定義
LEFT セクション1UNION ALLセクション2
RIGHT セクション1UNION ALLセクション3
FULL セクション1UNION ALLセクション2UNION ALLセクション3

外部結合のタイプごとに、UNION ALL演算子を使用して適切なコンポーネントを組み立てる操作を検討します。UNION ALLは重複行を許可するため、厳密にはリレーショナル演算子ではありません。

内部テーブルと外部テーブル

外部結合について述べるときによく使われる「内部テーブル」と「外部テーブル」という語を以下のテーブルに定義してあります。

用語 定義
内部テーブル 内部テーブルは、一致した行だけを外部結合の結果に与えるテーブルです。

内部結合の場合、テーブルは両方とも内部テーブルです。

外部テーブル 外部テーブルは、一致しない行を外部結合の結果に与えるテーブルです。

この説明で、一致しないとは、内部結合の行の一部ではない左または右(あるいは両方の)テーブルにある行のことです。一致する列がないため、行は結果テーブルでnullを使って拡張されています。

この用語は、入れ子にされた結合やスプールにも当てはまります。

内部/外部テーブルの例

以下の入れ子になった外部結合について検討してください。

     (table_A
     LEFT OUTER JOIN
     (table_B
     RIGHT OUTER JOIN table_C ON join_condition
     )
     ON join_condition
     )
     FULL OUTER JOIN table_D ON join_condition

この外部結合の内部テーブルと外部テーブルは次のとおりです。

まず、最も深く入れ子にされた結合と外部に働く結合には、以下の関係が当てはまります。

  1. table_cは、table_bに関する外部テーブルです。
  2. table_aは、ネスト ジョイン(table_b RIGHT OUTER JOIN table_c ON join_condition)に関する外部テーブルです。
  3. (table_a LEFT OUTER JOIN (table_b RIGHT OUTER JOIN table_c ON join_condition) ON join_conditionは、完全外部結合の外部テーブルです。
  4. table_dは、完全外部結合の外部テーブルです。

列の射影リスト

外部結合を作成する場合、結果を有用で解釈可能なものにするには、列の射影リストを慎重に選択します。一般的に、外部結合と同じ側から列を射影しなければなりません。右外部結合の実際的な例は、CourseNoに対する右外部結合です。そのため、右結合列(Enrollment.CourseNo)を射影します。

同様に、完全外部結合の実際的な例にある完全外部結合の例では、これと同じ理由で両方のCourseNo列を射影させます。

Nullと外部結合

nullは、外部結合を含んだ問合わせによって作成されるレポートの基本的なコンポーネントです。外部結合の鍵となる機能は、外部テーブルの集合から行を返す場合に、行内に一致する値がなかったときは、対応するテーブルにそのような不明の「値」があったものとしてその行をnullで拡張する機能です。

例えば、カストマー教育によって開催されるコースのうち、従業員が登録されているコースと、誰も登録されていないコースをリストする場合があります。

左外部結合右外部結合、および完全外部結合の外部結合の実例では、次の3つのテーブルを使用します。
  • offeringsテーブルには、現在開催されている顧客教育コースとその場所が示されます。開催されているコースについては、offeringsをコースの部分集合と考えることができます。
    offerings
    course_no beginning_dates location
    C100 01/05/2006 El Segundo
    C200 07/02/2006 Dayton
    C400 10/07/2006 El Segundo
  • enrollmentテーブルには、コースに登録された従業員が示されています。その中には、開催されていないものもあります。
    enrollment
    emp_no course_no
    236 C100
    236 C300
  • coursesテーブルには、顧客教育によって開発されたすべてのコースがリストされています。その中には、現在開催されていないものもあります(C300など)。
    courses
    course_no name
    C100 Recovery Planning
    C200 Software Architecture
    C300 Teradata Basics
    C400 Introduction to Java Programming

外部テーブルの集合について報告されているnullは、欠落した情報ではなく空のセットを表わしています。SQL言語でのnullの使用法の詳細は、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

例: 左外部結合

2つのテーブル(table_aおよびtable_b)における左外部結合のSELECT文の例を次に示します。

     SELECT column_expression
     FROM table_a
     LEFT OUTER JOIN table_b ON join_conditions
     WHERE join_condition_exclusions;

上記の例の左側のテーブルとは、FROM句のキーワードOUTER JOINの左側に指定されたテーブルを意味します。右側のテーブルとは、キーワードOUTER JOINの右側に指定されたテーブルを意味します。

キーワードLEFTは、左内部結合の結果に返されない、一致しない行のソースを示します。

このように、結合条件に従って複数のテーブルの内部結合を実行する他、上記の例に示すように、左外部結合は左側のテーブル(table_a)の一致しない行をnullで拡張して返します。

右外部結合は右側のテーブルの一致しない行をnullで拡張して返します。

完全外部結合は、両側の結合テーブルの中の一致しない行をnullで拡張して返します。

予約語OUTERはオプションであるため、上記のSELECT文は次のように書くこともできます。

     SELECT ...
     FROM table_A
     LEFT JOIN table_B ON join_condition;

例: 左外部結合のON句内のスカラー サブクエリー

スカラー サブクエリーは、外部結合指定のON句内で、スカラー述部のオペランドとして指定できます。

次に示す例では、ON句内でスカラー サブクエリー(SELECT AVG(price)…)を指定しています。

     SELECT category, title, COUNT(*)
     FROM movie_titles AS t2 LEFT OUTER JOIN transactions AS txn
     ON (SELECT AVG(price) 
         FROM movie_titles AS t1
         WHERE t1.category = t2.category)<(SELECT AVG(price)
                                           FROM movie_titles)
         AND t2.title = txn.title;

外部結合ON句でDEFAULT関数を検索条件として使用する場合のルール

外部結合ON句内で検索条件の一部としてDEFAULT関数を使用する場合、以下のルールが適用されます。
  • DEFAULT関数は、リレーション列を名前で識別する単一の引数を取ります。この関数は、列の現行デフォルト値に等しい値と評価されます。列のデフォルト値が現行のビルトイン システム関数として指定される場合、DEFAULT関数は、リクエストの処理時にシステム変数の現在の値と評価されます。

    この結果として、DEFAULT関数のデータ型は、デフォルトがNULLでない場合、デフォルトとして指定される定数または組み込み関数のデータ型になります。デフォルトがNULLの場合、DEFAULT関数のデータ型は、デフォルトのリクエスト対象である列または式のデータ型と同じになります。

  • DEFAULT関数には2つの形式があります。DEFAULTまたはDEFAULT (column_name)として指定することができます。列名が指定されていない場合、システムは、文脈に基づいて列を派生させます。列の文脈で派生できない場合、要求元にはエラーが返されます。
  • DEFAULT関数は、述部において列名の引数で指定できます。DEFAULT関数は、その引数として指定された列のデフォルト値と評価されます。システムがDEFAULT関数を評価した後、関数は述部の定数として計算されます。
  • 式内で比較演算子の各側に条件として1つの列指定と1つのDEFAULT関数がある場合に限り、述部で列名の引数を使用せずにDEFAULT関数を指定できます。
  • DEFAULT関数がNULLと評価される場合、既存の比較ルールに従って、IS NULLまたはIS NOT NULL以外の比較演算子とともに使用されたDEFAULT関数を含む条件は、不明な値と評価されます。

    DEFAULT関数とNULLとを比較する場合、IS NULLまたはIS NOT NULL以外の条件は、不明な値と評価されます。

DEFAULT関数とNULL 比較結果
IS NULL
  • デフォルトがNULLの場合はTRUE
  • それ以外はFALSE
IS NOT NULL
  • デフォルトがNULLの場合はFALSE
  • それ以外はTRUE

DEFAULT関数の詳細について、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。

外部結合ON条件でのDEFAULT関数の使用

この例では、次のテーブル定義があることを前提にしています。

     CREATE TABLE table15 (
       col1 INTEGER ,
       col2 INTEGER NOT NULL,
       col3 INTEGER NOT NULL DEFAULT NULL,
       col4 INTEGER CHECK (col4 > 10) DEFAULT 9 );
     CREATE TABLE table16 (
       col1 INTEGER,
       col2 INTEGER DEFAULT 10,
       col3 INTEGER DEFAULT 20,
       col4 CHARACTER(60)  );

外部結合では、検索条件のコンポーネントとしてDEFAULT関数を指定できます。次の一連の例では、外部結合の検索条件の一部としてDEFAULT関数を正しく使用する方法を説明しています。

次の例では、DEFAULT関数はtable16.col2のデフォルト値、つまり10と評価されます。

     SELECT *
     FROM table16
     FULL OUTER JOIN table15 ON table15.col1 < DEFAULT(table16.col2);

したがって、上記の例は以下の例と同等です。

     SELECT *
     FROM table16
     FULL OUTER JOIN table15 ON table15.col1 < 10;