tech_memo / ORACLE / Table_Join

tech_memo/ORACLE

外部結合 †

Oracle独自方式 †

• (+)をつけた方が内部表になると覚えておけばよい
• 二つのテーブルT1、T2があったときに、T1を外部表(LEFT表)として結合する場合は、以下のようになる

  select
    t1.id as t1_id,
    t2.id as t2_id
  from t1, t2
  where
    t1.id = t2.id(+)

• 以下、例。
  • Table1

    ID
    1
    2
    3

  • Table2

    ID
    1
    2
    4

  • 結果

    ROW_NUM	T1_ID	T2_ID
    1	1	1
    2	2	2
    3	3	NULL
    レコードなし	NULL	4

WHERE句の絞り込み条件の(+)の有無による挙動の違い †

• WHERE句の絞り込み条件に(+)を指定する場合とそうでない場合の挙動

  (+)	挙動
  あり	絞り込み条件に一致したものだけ結合する（不一致のレコードは結合されないのでNULLになる）
  なし	結合した後、絞り込み条件のレコードだけを返却

• 例。テーブルは上記 Oracle独自方式と同じとする。
  • 絞り込みに(+)あり

    select
      t1.id as t1_id,
      t2.id as t2_id
    from t1, t2
    where
      t1.id = t2.id(+)
      and t2.id(+) = 1; -- ★ここ

  • 結果。T2.ID=1のものだけ結合される

    ROW_NUM	T1_ID	T2_ID
    1	1	1
    2	2	NULL
    3	3	NULL
    レコードなし	NULL	4

• 絞り込みに(+)なし

  select
    t1.id as t1_id,
    t2.id as t2_id
  from t1, t2
  where
    t1.id = t2.id(+)
    and t2.id = 1; -- ★ここ

• 結果。T2.ID=1のものだけ返却される

  ROW_NUM	T1_ID	T2_ID
  1	1	1
  レコードなし	2	2
  レコードなし	3	NULL
  レコードなし	NULL	4

SQL標準記述と、Oracle独自記述の対応 †

左外部結合 †

• Oracle独自

  select
    t1.id as t1_id,
    t2.id as t2_id
  from t1, t2
  where
    t1.id = t2.id(+);

• SQL標準

  select
    t1.id as t1_id,
    t2.id as t2_id
  from t1
  left join t2
  on
    t1.id = t2.id;

条件の絞り込み †

• T2はID=1のものしか結合しない場合(ID=1以外のT2レコードはNULLで返却される場合)
  • Oracle独自

    select
      t1.id as t1_id,
      t2.id as t2_id
    from t1, t2
    where
      t1.id = t2.id(+)
      and t2.id(+) = 1; -- ★

  • SQL標準

    select
      t1.id as t1_id,
      t2.id as t2_id
    from t1
    left join t2
    on
      t1.id = t2.id
      and t2.id = 1; -- ★

    • 仮にLEFT表で条件を指定しても意味は同じ (t1.id = 1 と指定した場合、 T1のID=1のレコードのみを結合する。T1は全レコード返るが、ID=1以外のT2レコードはNULL）

• 結合後、T2はID=1のものしか返却しない場合
  • Oracle独自

    select
      t1.id as t1_id,
      t2.id as t2_id
    from t1, t2
    where
      t1.id = t2.id(+)
      and t2.id = 1; -- ★

  • SQL標準

    select
      t1.id as t1_id,
      t2.id as t2_id
    from t1
    left join t2
    on
      t1.id = t2.id
    where
      t2.id = 1; -- ★