全外部結合

全外部結合は、両方の表の一致しない行が結果に含まれるように表を結合する方式です。

2 つの表を結合し、結果セットに両方の表からの一致しない行が含まれるようにする場合は、FULL OUTER JOIN 文節を使用します。マッチングは結合条件に基づいて行われます。値を持たない結果表の列があると、その列の結果表には NULL 値が入ります。

全外部結合の結合条件は、2 つの列を比較する単純な検索条件であるか、またはその引数として列名を持つ cast 関数呼び出しである必要があります。

フルアウタージョインの例

この例では、PARTSとPRODUCTSテーブルに以下の行が含まれていると仮定します

          PARTS table                             PRODUCTS table
PART      PROD#   SUPPLIER              PROD#     PRODUCT        PRICE
=======   =====   ============          =====     ===========    =====
WIRE      10      ACWF                  505       SCREWDRIVER    3.70
OIL       160     WESTERN_CHEM          30        RELAY          7.55
MAGNETS   10      BATEMAN               205       SAW            18.90
PLASTIC   30      PLASTIK_CORP          10        GENERATOR      45.75
BLADES    205     ACE_STEEL

以下のクエリは、PARTSテーブルとPRODUCTSテーブルの完全外部結合を実行します

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

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

PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       -----------
-------     ------------   ---       SCREWDRIVER

COALESCEまたはVALUEを使用した完全外部結合の例

COALESCE は、SQL 標準で VALUE 関数のシノニムとして指定されているキーワードです。この関数は、いずれの名前でも、結合列のペアから最初の非 NULL 値を戻すので、全外部結合操作では特に便利です。

フル・アウター・ジョインの例の結果では、PRODUCTSテーブルにSCREWDRIVERの製品番号が含まれているにもかかわらず、SCREWDRIVERの製品番号はnullです。その代わりに PRODUCTS.PROD# を選択した場合、OIL に対して PROD# が NULL になります。 PRODUCTS.PROD# および PARTS.PROD# の両方を選択すると、その結果には、どちらもいくつかの NULL 値を含む 2 つの列が入ります。 COALESCE 関数を使用すれば、NULL 値を除去して、両方の列からデータをマージして単一の列に入れることができます。

次の例では、上記と同じ PARTS 表と PRODUCTS 表を用いて、PROD# 列から非 NULL データをマージします。

SELECT PART, SUPPLIER,
   COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM, PRODUCT
    FROM PARTS FULL OUTER JOIN PRODUCTS
       ON PARTS.PROD# = PRODUCTS.PROD#;

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

PART        SUPPLIER       PRODNUM   PRODUCT
=======     ============   =======   ===========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       -----------
-------     ------------   505       SCREWDRIVER

AS 文節 (AS PRODNUM) を使用すれば、COALESCE 関数の結果に名前を付けることができます。