目次

LEFT JOIN、INNER JOIN違い

下記2つのテーブルでテスト

mysql> SELECT * FROM table1;
+------+------+
| id   | name |
+------+------+
|    1 | A    | 
|    2 | B    | 
|    3 | C    | 
|    4 | D    | 
|    5 | E    | 
|    6 | F    | 
+------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM table2;
+------+------+
| id   | name |
+------+------+
|    1 | A    | 
|    2 | B    | 
|    3 | C    | 
|    7 | G    | 
|    8 | H    | 
|    9 | *    | 
+------+------+
6 rows in set (0.00 sec)

LEFT JOIN

mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | A    |    1 | A    | 
|    2 | B    |    2 | B    | 
|    3 | C    |    3 | C    | 
|    4 | D    | NULL | NULL | 
|    5 | E    | NULL | NULL | 
|    6 | F    | NULL | NULL | 
+------+------+------+------+
6 rows in set (0.00 sec)

INNER JOIN

mysql> SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | A    |    1 | A    | 
|    2 | B    |    2 | B    | 
|    3 | C    |    3 | C    | 
+------+------+------+------+
3 rows in set (0.00 sec)