読者です 読者をやめる 読者になる 読者になる

INNER JOINとCROSS JOINの違い

sql

今までJOIN構文をなんとなくで使ってきたので、INNER JOINとCROSS JOINの違いがよくわからないでいる今日この頃。
いまさらながら調べてみた。

CROSS JOIN:
ON, USING を指定しない場合、左右のテーブルの直積を返します。
つまり、左右のテーブルにそれぞれ2行のレコード、3行のレコードが格納されている場合、結果は全てのレコードの組合せになり合計 2x3=6 行のレコードになります。
PostgreSQLMySQL では ON, USING を指定すると INNER JOIN と等価になります。
SELECT * FROM table1 CROSS JOIN table2
は次の SQL 文と等価です。
SELECT * FROM table1, table2
また、PostgreSQL, MySQL では
SELECT * FROM table1 CROSS JOIN table2 USING(id)
は次の SQL 文と等価です。
SELECT * FROM table1 INNER JOIN table2 USING(id)
SQL 基礎 – JOIN (表結合)の意味の違い|てくめも@ecoop.net

■JOIN構文による結合の分類
 
 単純結合(JOIN ON)
 ・一致するデータがある場合に利用。(例:外部キーで参照)
 ・等価結合、非等価結合を同時に利用できる。

 内部結合(INNER JOIN)
 ●単純結合のJOINと動作は同じ。INNERをつけることによって結合方法を明示的にする。
 ・等価結合、非等価結合を同時に利用できる。

 外部結合(LEFT OUTER JOIN , RIGHT OUTER JOIN)
 ・片方のテーブルに一致しない値が存在し、一致しない値を持つ行も検索する場合に利用。
 ・結合列にNULL値を持つ行を検索する場合に利用。
 ・等しくない値も取得する場合に利用。
 ・OUTERを省略可能。
 ・等価結合、非等価結合を同時に利用できる。

 完全外部結合(FULL OUTER JOIN)
 ・両方のテーブルに一致しない値が存在し、一致しない値を持つ行を検索する場合に利用。
 ・結合列にNULL値を持つ行を検索する場合に利用。
 ・等しくない値も取得する場合に利用。
 ・OUTERを省略可能。
 ・等価結合、非等価結合を同時にも利用できる。

 直積結合(CROSS JOIN)
 ・「SELECT * FROM TEST1,TEST2」と「SELECT * FROM TEST1 CROSS JOIN TEST2」は同等。
 ・結合条件が省略されている。
 ・表内のすべての行を互いの表のすべての行に結合する。(テーブルAの行数×テーブルBの行数→直積の行数)
 ・DB2ではCROSS JOINを明示的に記述すると構文エラー。

 自然結合(NATURAL JOIN、JOIN USING)
 ・oracle9i以降のみの構文。
 ・両方の表で一致する名前とデータ型に基づいて自動的に結合。
 ・共通の列が複数存在すればそれらがすべて結合列として利用。
 ・共通の列が複数存在するが、その中の一部だけ使用する場合はUSING句を使用。
 ・結合列の名前は同じだが、データ型が異なる場合、USING句を使用。
 ・NATURAL JOIN句とUSING句は一緒には使えない。(互いに排他的)
 ・結合列は表名または別名で修飾できない。
単純結合と内部結合 − Database Expert − @IT

エエエェェェェ。

なんだ、INNER JOINで結合条件を指定しない場合と等価なだけなのか。
そういうことをしたい状況は今までなかったなぁ。

まあ、とにかく、覚えた。