MySQL / PostgreSQL / Oracle すべてのSQLサーバで基準になっているSQL標準のJOIN(LEFT JOINなど)について、基礎からしっかりまとめてみました。
■目次
■JOINの種類
SQL 標準では JOIN 句による結合構文は次のような種類があります。
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
CROSS JOIN
LEFT JOIN, RIGHT JOIN など、よく使われる構文は上記の省略形です。
・ただの JOIN は INNER JOIN の省略形。
・LEFT JOIN は LEFT OUTER JOIN の省略形。
・RIGHT JOIN は RIGHT OUTER JOIN の省略形。
それぞれの JOIN 構文の意味は次のとおりです。
INNER JOIN: 内部結合
指定したカラムについて同じ値を持つレコード同士を結びつける。
内積なので指定したカラムの値がどちらかにしかないレコードについては結果に含まれません。
SELECT * FROM table1 INNER JOIN table2 USING(id)
は、次の SQL 文と同じ結果になります。
SELECT * FROM table1, table2 WHERE table1.id = table2.id
LEFT OUTER JOIN: 外部結合
左のテーブルを基準にして、指定したカラムについて同じ値を持つレコード同士を結びつける。
値が右のテーブルにあり左のテーブルにない場合は INNER JOIN 同様結果に含まれませんが、
値が左のテーブルにあり右のテーブルにない場合は INNER JOIN と異なり 右のテーブルのカラムには全て NULL がパディングされ、結果に含まれます。
このため、
SELECT * FROM table1 LEFT OUTER JOIN table2 USING(id) WHERE table2.id IS NOT NULL
は、
SELECT * FROM table1 INNER JOIN table2 USING(id)
と同じ結果になります。
RIGHT OUTER JOIN: 外部結合
LEFT OUTER JOIN の左右の意味を入れ換えたもの。
SELECT * FROM table1 RIGHT OUTER JOIN table2 USING(id)
は
SELECT * FROM table2 LEFT OUTER JOIN table1 USING(id)
と同じ結果になります。
CROSS JOIN: 交差結合
ON, USING で条件を指定しない場合、左右のテーブルの直積を返します。
つまり、左右のテーブルにそれぞれ2行のレコード、3行のレコードが格納されている場合、結合結果は全てのレコードの組合せになり合計 2×3=6 行のレコードになります。
PostgreSQL や MySQL では 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)
■ON, USING, NATURALによる結合条件指定
CROSS JOIN をのぞき、JOIN 句には ON, USING, NATURAL のいずれかを指定します。
INNER JOIN … ON 結合条件
INNER JOIN … USING(結合カラム, … )
NATURAL INNER JOIN …
ON:
WHERE 句と同じ書式で結合条件を絞り込む。
書式:
SELECT … FROM table1 JOIN table2 ON 条件式
table1, table2 のidが同じカラム同士を結合したい場合は次のようにします。
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
AND, OR など複雑な条件で絞り込む事も可能です。
SELECT * FROM table1 AS t1
LEFT JOIN table2 AS t2 ON (
(t1.id = t2.id OR t1.name = t2.name)
AND t1.salary >= t2.age * 1000
)
ON に指定する結合条件には、 WHERE 同様どんな条件式でも指定することが可能ですが、テーブル結合に関係のない条件式はWHERE句に書くようにしましょう。
USING:
カラム名で結合条件を絞り込む。
書式:
SELECT … FROM table1 JOIN table2 USING(カラム1 , カラム2, …)
両方のテーブルに同じカラム名がある場合に有効です。
SELECT * FROM table1 LEFT JOIN table2 USING(id)
は
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
と同等です。複数指定した場合は全てのカラムの値が一致するレコードを結合します。
NATURAL:
両方のテーブルを比較して名前が同じ全てのカラムを結合条件にして絞り込む。
書式:
SELECT … FROM table1 NATURAL JOIN table2
USING に両テーブルの共通カラムを全て指定したものと同じです。すなわち、
SELECT * FROM table1 NATURAL LEFT JOIN table2
は、両方のテーブルに id, name, age が存在する場合、
SELECT * FROM table1 LEFT JOIN table2 USING (id, name, age)
を指定したのと同等の結果になります。
■複数のJOIN句を組み合わせる
3つ以上のテーブルを複数のJOIN 句で結合する事もできます。
例として、table1, table2. table3 全てのテーブルに同じidが存在するレコードのみ抽出する場合は次のようになります。
SELECT * FROM table1
INNER JOIN table2 USING(id)
INNER JOIN table3 USING(id)
複数のJOIN を組み合わせる時の結合順序は左から順に評価されます。結合順序を制御したい場合、カッコが使用できます。
SELECT * FROM table1 AS t1
LEFT JOIN ( table2 AS t2 INNER JOIN table3 USING(id)) ON t1.value = t2.tag
)
この例では table2, table3の同じidのレコードをINNER JOIN 句を使い結合し、その後 table1 と LEFT JOIN 句で結合されます。
■参考:
– SELECT(PostgreSQL 8.2.5 マニュアル)
– MySQL 5.1 リファレンスマニュアル 12.2.7.1 JOIN 構文(MySQL AB)
– 特殊な結合演算子 SQL(TELCHSCORE by 4DD)
– SQL92