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 (2586d)