まず、テーブルを作ります。
mysql> create table staff(
-> id int,
-> name varchar(10),
-> deptid int
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> insert into staff values(1, ‘yamada’, 1);
Query OK, 1 row affected (0.11 sec)
mysql> insert into staff values(2, ‘honda’, 4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values(3, ‘kudou’, 6);
Query OK, 1 row affected (0.03 sec)
mysql> insert into staff values(4, ‘nishi’, 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values(5, ‘tagawa’, 3);
Query OK, 1 row affected (0.06 sec)
mysql> select * from staff;
+——+——–+——–+
| id | name | deptid |
+——+——–+——–+
| 1 | yamada | 1 |
| 2 | honda | 4 |
| 3 | kudou | 6 |
| 4 | nishi | 1 |
| 5 | tagawa | 3 |
+——+——–+——–+
5 rows in set (0.00 sec)
更にもう一つテーブルを作ります。
mysql> create table dept(id int, name varchar(10));
Query OK, 0 rows affected (0.15 sec)
mysql> insert into dept values(1, ‘Develop’);
Query OK, 1 row affected (0.07 sec)
mysql> insert into dept values(3, ‘Legal’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into dept values(5, ‘Planning’);
Query OK, 1 row affected (0.02 sec)
mysql> insert into dept values(6, ‘Marketing’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+——+———–+
| id | name |
+——+———–+
| 1 | Develop |
| 3 | Legal |
| 5 | Planning |
| 6 | Marketing |
+——+———–+
4 rows in set (0.00 sec)
さーそれではleft.joinを使ってみましょう。
mysql> select * from staff left join dept on staff.deptid = dept.id;
+——+——–+——–+——+———–+
| id | name | deptid | id | name |
+——+——–+——–+——+———–+
| 1 | yamada | 1 | 1 | Develop |
| 4 | nishi | 1 | 1 | Develop |
| 5 | tagawa | 3 | 3 | Legal |
| 3 | kudou | 6 | 6 | Marketing |
| 2 | honda | 4 | NULL | NULL |
+——+——–+——–+——+———–+
5 rows in set (0.05 sec)
おおおおおおおおおおおおお。逆バージョン
mysql> select * from dept left join staff on dept.id = staff.deptid;
+——+———–+——+——–+——–+
| id | name | id | name | deptid |
+——+———–+——+——–+——–+
| 1 | Develop | 1 | yamada | 1 |
| 6 | Marketing | 3 | kudou | 6 |
| 1 | Develop | 4 | nishi | 1 |
| 3 | Legal | 5 | tagawa | 3 |
| 5 | Planning | NULL | NULL | NULL |
+——+———–+——+——–+——–+
5 rows in set (0.00 sec)
並び順がidではないだね。んーん。
カラムを指定する場合は、
mysql> select * from staff.id, staff.name, dept.name left join dept on staff.deptid = dept.id;
ERROR 1146 (42S02): Table ‘staff.id’ doesn’t exist
あれ、ちゃう。こうやな。
select staff.id, staff.name, dept.name from staff left join dept on staff.deptid = dept.id;
