ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

asはカラムに別名をつける

select sales as tokyosales from uriage where branch='tokyo';
select (kokugo + eigo) / as average from test;

なるほど。

select name as namae, price as kakaku, design as design, quality as hinshitsu, price+design+quality as gokei from product;

なるほど、int型ならこんなこともできるのね。
selectした後に実行するのと、selectの中で実行するの、どちらがいいのかはよくわらかりませんな。

mysql> select * from staff inner join dept on staff.deptid = dept.id;
+——+——–+——–+——+———–+
| id | name | deptid | id | name |
+——+——–+——–+——+———–+
| 1 | yamada | 1 | 1 | Develop |
| 3 | kudou | 6 | 6 | Marketing |
| 4 | nishi | 1 | 1 | Develop |
| 5 | tagawa | 3 | 3 | Legal |
+——+——–+——–+——+———–+
4 rows in set (0.00 sec)

mysql> select * from dept inner 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 |
+——+———–+——+——–+——–+
4 rows in set (0.00 sec)

nullは返ってこないのかな。

まず、テーブルを作ります。

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;