database :: SQL/JOIN/いろいろな JOIN

下記のようなテーブル「table1」と「table2」で、いろんな JOIN を試します。

sql> SELECT * FROM table1;
+------+------+
| id   | val  |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
+------+------+
3 rows in set (0.00 sec)

sql> SELECT * FROM table2;
+------+------+
| id   | val  |
+------+------+
|    1 | xxx  |
|    3 | yyy  |
|    5 | zzz  |
+------+------+
3 rows in set (0.00 sec)

■ INNER JOIN .. USING (id カラムは集約される)

sql> SELECT * FROM table1 INNER JOIN table2 USING (id);
+------+------+------+
| id   | val  | val  |
+------+------+------+
|    1 | aaa  | xxx  |
|    3 | ccc  | yyy  |
+------+------+------+
2 rows in set (0.00 sec)

■ INNER JOIN .. ON (id カラムは集約されない)

sql> SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
|    3 | ccc  |    3 | yyy  |
+------+------+------+------+
2 rows in set (0.00 sec)

■ LEFT JOIN .. USING (id カラムは集約される)

sql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
+------+------+------+
| id   | val  | val  |
+------+------+------+
|    1 | aaa  | xxx  |
|    2 | bbb  | NULL |
|    3 | ccc  | yyy  |
+------+------+------+
3 rows in set (0.00 sec)

■ LEFT JOIN .. ON (id カラムは集約されない)

sql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
|    2 | bbb  | NULL | NULL |
|    3 | ccc  |    3 | yyy  |
+------+------+------+------+
3 rows in set (0.00 sec)

■ RIGHT JOIN .. USING (id カラムは集約される)

sql> SELECT * FROM table1 RIGHT JOIN table2 USING (id);
+------+------+------+
| id   | val  | val  |
+------+------+------+
|    1 | xxx  | aaa  |
|    3 | yyy  | ccc  |
|    5 | zzz  | NULL |
+------+------+------+
3 rows in set (0.00 sec)

■ RIGHT JOIN .. ON (id カラムは集約されない)

sql> SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
|    3 | ccc  |    3 | yyy  |
| NULL | NULL |    5 | zzz  |
+------+------+------+------+
3 rows in set (0.00 sec)

■ CROSS JOIN

sql> SELECT * FROM table1 CROSS JOIN table2;
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
|    2 | bbb  |    1 | xxx  |
|    3 | ccc  |    1 | xxx  |
|    1 | aaa  |    3 | yyy  |
|    2 | bbb  |    3 | yyy  |
|    3 | ccc  |    3 | yyy  |
|    1 | aaa  |    5 | zzz  |
|    2 | bbb  |    5 | zzz  |
|    3 | ccc  |    5 | zzz  |
+------+------+------+------+
9 rows in set (0.00 sec)

■ FULL JOIN … ON

sql> SELECT * FROM table1 t1 FULL JOIN table2 t2 ON t1.id = t2.id;
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
|    2 | bbb  | NULL | NULL |
|    3 | ccc  |    3 | yyy  |
| NULL | NULL |    5 | zzz  |
+------+------+------+------+
4 rows in set (0.00 sec)

■ ON 句で JOIN を実行した場合に認識しておくべき注意事項

AND 条件を指定し、かつ ON 句で JOIN を実行した場合、結合に関係ない行もセレクトされます。
この点を認識しておかないと、意図しない結果を取得することになってしまいますので注意が必要です。
下記参照。

sql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id AND t2.val = 'xxx';
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
|    2 | bbb  | NULL | NULL |
|    3 | ccc  | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

sql> SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id AND t2.val = 'xxx';
+------+------+------+------+
| id   | val  | id   | val  |
+------+------+------+------+
|    1 | aaa  |    1 | xxx  |
| NULL | NULL |    3 | yyy  |
| NULL | NULL |    5 | zzz  |
+------+------+------+------+
3 rows in set (0.00 sec)

sql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 USING(id) WHERE t2.val = 'xxx';
+------+------+------+
| id   | val  | val  |
+------+------+------+
|    1 | aaa  | xxx  |
+------+------+------+
1 row in set (0.00 sec)

ちなみに、JOIN .. USING .. AND という構文はエラーになります。

sql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 USING (id) AND t2.val = 'xxx';
ERROR: You have an error in your SQL syntax