DB/SQL/複数の表の結合

Top/DB/SQL/複数の表の結合

NATURAL JOIN†

自然結合。データベース側に『これとこれくっつけたい。あとはうまいことやってくれ』とお願いする結合方法。等価結合のみ。内部結合。

二つの表で、結合したい列の列名とデータ型が一致している必要がある。逆にいえば、列名とデータ型が一致している列で勝手に結合される。

  • 方法 
    -- hoge 表と foo 表を結合する
select fuga, piyo, bar
    from hoge natural join foo;
  • 補足
    • 表別名、列別名は使ってもいいけど、結合される列には修飾子は使えない。たとえば、上の例で hoge 表と foo 表が fuga 列で自然結合されるとき、from hoge h などの表別名は付けてもいいが、select h.fuga など、結合列には修飾子を付記できない、ということ
↑

JOIN USING†

自然結合だと意図しない列まで結合されてしまうとき、もしくは列名は一致しているけどデータ型が違うときに使う結合方法。等価結合のみ。内部結合。

二つの表で、結合したい列の列名が一致している必要がある。データ型の一致は（相互に変換可能な、互換性のある組み合わせであれば）不要。

  • 方法 
    -- hoge 表と foo 表を piyo 列で結合する
select fuga, piyo, bar
    from hoge join foo
        using (piyo);
  • 補足
    • 型変換は、見た感じできそうだったらだいたいできるっぽい。文字列として数字が入っている varchar2 型と number 型とか、date 型と date 型のデフォルトのフォーマットで文字列が格納されている varchar2 型とか
    • 表別名、列別名の使用ルールは NATURAL JOIN と同じ。結合列には修飾子は付加できない
↑

JOIN ON†

結合条件を明示する結合方法。だいたい何でもできる。等価結合も不等価結合もできる。内部結合。

結合したい列の名前もデータ型も、一致している必要はない（データ型は相互に変換可能である必要はある）。

  • 方法 
    -- hoge 表と foo 表を双方の piyo 列で結合する
select h.fuga, h.piyo, f.bar
    from hoge h join foo f
        on (h.piyo = f.piyo);

-- 不等価結合の例はこんな
select h.fuga, h.piyo, f.bar
    from hoge h join foo f
        on (h.piyo between f.puni and f.bar);
  • 補足
    • ON の括弧の中にいろいろ書けばいろいろできる
↑

OUTER JOIN†

外部結合をしたいときは OUTER 句を指定する。上で書いたいろいろなパタンにもそれぞれ適用可能。

  • 方法 
    -- 左側外部結合（左の表のレコードは全件出力する）
select h.fuga, h.piyo, f.bar
    from hoge h left outer join foo f
        on (h.piyo = f.piyo);

-- 右側外部結合（右の表のレコードは全件出力する）
select h.fuga, h.piyo, f.bar
    from hoge h right outer join foo f
        on (h.piyo = f.piyo);

-- 完全外部結合（どちらの表のレコードも全件出力する）
select h.fuga, h.piyo, f.bar
    from hoge h full outer join foo f
        on (h.piyo = f.piyo);
↑

CROSS JOIN†

デカルト積。つまるところの、二つの表の全組み合わせの出力。

結合条件を省略したり、結合条件が無効だったりするとこれになる。明示的にこれをする場合は CROSS JOIN 句を用いる。

  • 方法 
    select fuga, piyo, bar
    from hoge cross join foo;
↑

JOIN を使わない結合†

明示的に JOIN 句を記述しなくても、表別名と WHERE 句の組み合わせで結合表は出力可能。

  • 方法 
    -- 単なる内部結合
select h.fuga, h.piyo, f.bar
    from hoge h, foo f
    where h.piyo = f.piyo;

-- 左側外部結合（Oracle 独自）
select h.fuga, h.piyo, f.bar
    from hoge h, foo f
    where h.piyo = f.piyo(+);

-- 右側外部結合（Oracle 独自）
select h.fuga, h.piyo, f.bar
    from hoge h, foo f
    where h.piyo(+) = f.piyo;

-- 完全外部結合は FULL OUTER JOIN でないと書けない
↑

補足†

  • 可能であれば、表別名は付けたほうが良い。パフォーマンスも向上するし、可読性も上がる
  • WHEREによる行の制限が可能
  • 3つ以上の表も結合可能。結合順序は左から順になる
  • パフォーマンスを考える上では結合順序も大事。たとえば 1000 万行の表 + 100 行の表 + 10 行の表 + …… としたときは 1000 万行を最後にしたほうが早い
Last-modified: 2011-10-17 (月) 20:05:03