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

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

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

説明:

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

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

単純な外部結合の例

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

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

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

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

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

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

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

元のテーブル定義では、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つのテーブルの外部結合のコンポーネントは、以下のように定義します。

外部結合のタイプ

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

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

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

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

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

内部/外部テーブルの例

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

     (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で拡張する機能です。

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

外部テーブルの集合について報告されている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関数を検索条件として使用する場合のルール

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;