MYSQL、JOIN（内部結合・外部結合）事始め #MySQL

表題：MYSQL、JOIN（内部結合・外部結合）

概要

MYSQLのJOIN構文について基本に立ち返って実際SQL文を叩いて学ぼうという趣旨です。

背景

ECサイトの開発プロジェクトのPM補佐をしています。

現在のプロジェクトのリリース直前にSQLのチューニングをエンジニアにお願いすることが多くなってきたのですが、自分でもどのsqlのここがイケていないってことがわかるようにしようということで実際にSQL文を書いた備忘録です。

データベースとテーブルを作成

CREATE DATABASE TEST;

購入履歴テーブルの作成

CREATE TABLE purchase(
    id_p int unsigned NOT NULL auto_increment,
    id_c int unsigned NOT NULL,
    id_g int unsigned NOT NULL,
    quality tinyint unsigned,

    PRIMARY KEY(id_p)
);

上記のSQL文を実行すると下記のテーブルが作成されます。

mysql> DESC purchase;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id_p    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| id_c    | int(10) unsigned    | NO   |     | NULL    |                |
| id_g    | int(10) unsigned    | NO   |     | NULL    |                |
| quality | tinyint(3) unsigned | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

補足：Type（データ型）

int(10) unsigned
intは-2147483648から2147483647の整数型です。ただし、今回はunsignedがついているので正の数しか格納できなくなります。その代わりに格納できる正の数の値は0から4294967295までに広がります。
tinyint(3) unsigned
tinyintは-128から127の整数型です。ただし、今回はunsignedがついているので正の数しか格納できなくなります。その代わりに格納できる正の数の値は0から255までに広がります。

データを挿入する

INSERT purchase SET id_c=3, id_g=2, quality=1;
INSERT purchase SET id_c=1, id_g=1, quality=3;
INSERT purchase SET id_c=4, id_g=1, quality=2;
INSERT purchase SET id_c=2, id_g=4, quality=1;
INSERT purchase SET id_c=4, id_g=2, quality=1;
INSERT purchase SET id_c=2, id_g=1, quality=1;

上記のSQL文を実行すると作成した購入履歴テーブルにデータが挿入されます。

mysql> select * from purchase;
+------+------+------+---------+
| id_p | id_c | id_g | quality |
+------+------+------+---------+
|    1 |    1 |    1 |       3 |
|    2 |    4 |    1 |       2 |
|    3 |    2 |    4 |       1 |
|    4 |    4 |    2 |       1 |
|    5 |    2 |    1 |       1 |
|    6 |    3 |    2 |       1 |
+------+------+------+---------+
6 rows in set (0.00 sec)

顧客テーブルの作成

購入履歴テーブルと同様に顧客テーブルの作成していきます。

CREATE TABLE customer(
    id_c int unsigned NOT NULL auto_increment,
    fullname varchar(40) NOT NULL,
    age tinyint unsigned NOT NULL,
    sex tinyint unsigned NOT NULL,
    email varchar(50),

    PRIMARY KEY(id_c)
);

mysql> DESC customer;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id_c     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| fullname | varchar(40)         | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned | NO   |     | NULL    |                |
| sex      | tinyint(3) unsigned | NO   |     | NULL    |                |
| email    | varchar(50)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

続けて、データの挿入。

INSERT customer SET fullname='山田花子', age='48', sex='2', email='hanako.yamada@sample.ne.jp';
INSERT customer SET fullname='マイケル・トムソン', age='35', sex='1', email='tomoson.micheal@sample.ne.jp';
INSERT customer SET fullname='ディーン・ドナルド', age='38', sex='1', email='dean.donald@sample.ne.jp';
INSERT customer SET fullname='佐藤優', age='18', sex='1', email='masaru.sato@sample.ne.jp';
INSERT customer SET fullname='佐々木ゆう子', age='28', sex='2', email='yuko.sasaki@sample.ne.jp';
INSERT customer SET fullname='レナ・ミッチェル', age='23', sex='2', email='rena.micheal@sample.ne.jp';
INSERT customer SET fullname='大島加奈子', age='58', sex='2', email='kanako.oshima@sample.ne.jp';

mysql> select * from customer;
+------+-----------------------------+-----+-----+------------------------------+
| id_c | fullname                    | age | sex | email                        |
+------+-----------------------------+-----+-----+------------------------------+
|    1 | 山田花子                    |  48 |   2 | hanako.yamada@sample.ne.jp   |
|    2 | マイケル・トムソン          |  35 |   1 | tomoson.micheal@sample.ne.jp |
|    3 | ディーン・ドナルド          |  38 |   1 | dean.donald@sample.ne.jp     |
|    4 | 佐藤優                      |  18 |   1 | masaru.sato@sample.ne.jp     |
|    5 | 佐々木ゆう子                |  28 |   2 | yuko.sasaki@sample.ne.jp     |
|    6 | レナ・ミッチェル            |  23 |   2 | rena.micheal@sample.ne.jp    |
|    7 | 大島加奈子                  |  58 |   2 | kanako.oshima@sample.ne.jp   |
+------+-----------------------------+-----+-----+------------------------------+
7 rows in set (0.00 sec)

商品テーブルの作成

購入履歴テーブルと同様に商品テーブルの作成していきます。

    CREATE TABLE goods(
        id_g tinyint(3) unsigned NOT NULL auto_increment,
        name varchar(30),
        price decimal(9,0),

        PRIMARY KEY(id_g)
    );

CREATE TABLE goods(
    id_g tinyint(3) unsigned NOT NULL auto_increment,
    name varchar(30),
    price decimal(9,0),

    PRIMARY KEY(id_g)
);

INSERT goods SET name='饅頭', price='200';
INSERT goods SET name='みたらし団子', price='430';
INSERT goods SET name='八つ橋', price='600';
INSERT goods SET name='抹茶団子', price='150';

mysql> SELECT * from goods;
+------+--------------------+-------+
| id_g | name               | price |
+------+--------------------+-------+
|    1 | 饅頭               |   200 |
|    2 | みたらし団子       |   430 |
|    3 | 八つ橋             |   600 |
|    4 | 抹茶団子           |   150 |
+------+--------------------+-------+
4 rows in set (0.00 sec)

補足：Type（データ型）

decimal(9, 0) 小数点を扱う際に使用することができます。
decimal(M, D)という形で指定します。
M・・・扱う小数の最大桁数
D・・・小数点以下の桁数

今回のdecimal(9,0)の場合は、全体の桁数が9桁で小数点以下は0桁なので、123456789といった整数値を扱う事ができることになります。

JOINを試してみる

複数のテーブルから情報を取得する必要がある場合、テーブルを連結することでクエリの発行から情報の取得までを一度に済ませることができます。複数のテーブルを連結させるには、FROMで複数のテーブルを指定することで実現することができます。複数のテーブルを指定するには、テーブル名とテーブル名の間にカンマ、もしくはJOINをはさみます。

基本文法

SELECT * FROM table1, table2;

SELECT * FROM table1 JOIN table2;

例：

mysql> SELECT id_p, fullname FROM purchase JOIN customer ON purchase.id_c=customer.id_c;
+------+-----------------------------+
| id_p | fullname                    |
+------+-----------------------------+
|    1 | 山田花子                    |
|    2 | 佐藤優                      |
|    3 | マイケル・トムソン          |
|    4 | 佐藤優                      |
|    5 | マイケル・トムソン          |
|    6 | ディーン・ドナルド          |
+------+-----------------------------+
6 rows in set (0.00 sec)

顧客履歴テーブルと顧客テーブルを結合するためにJOINを使っています。

ON purchase.id_c=customer.id_cは、「顧客履歴テーブルのid_cと顧客テーブルのid_cフィールドの値が一致したレコードを選択する」という意味です。

内部結合

JOINした２つのテーブルを比較し、結合条件に一致した行だけを返すことを内部結合と呼びます。他の結合方法と区別しやすいようにINNER JOINと記述することも可能です。

例：

mysql> SELECT purchase.id_g, name FROM goods INNER JOIN purchase ON purchase.id_g=goods.id_g;
+------+--------------------+
| id_g | name               |
+------+--------------------+
|    1 | 饅頭               |
|    1 | 饅頭               |
|    1 | 饅頭               |
|    2 | みたらし団子       |
|    2 | みたらし団子       |
|    4 | 抹茶団子           |
+------+--------------------+
6 rows in set (0.00 sec)

顧客履歴テーブルに記録のない商品であるid_g=3のレコードは表示されません。

mysql> select id_p, customer.id_c, fullname FROM customer JOIN purchase ON purchase.id_c = customer.id_c;
+------+------+-----------------------------+
| id_p | id_c | fullname                    |
+------+------+-----------------------------+
|    1 |    1 | 山田花子                    |
|    2 |    4 | 佐藤優                      |
|    3 |    2 | マイケル・トムソン          |
|    4 |    4 | 佐藤優                      |
|    5 |    2 | マイケル・トムソン          |
|    6 |    3 | ディーン・ドナルド          |
+------+------+------------

外部結合

結合条件に一致した行に加え、指定したテーブルに関しては結合条件に一致しなくてもレコードを返す結合方法です。

LEFT JOINキーワードとRIGHT JOINキーワードの2種があります。

LEFT JOIN

JOINの左側のテーブルが結合条件に一致しなくてもレコードをは返します。

下のSQLの場合は、goodsテーブルは条件に一致しなくてもレコードが返されます。

mysql> SELECT purchase.id_g,name FROM goods LEFT OUTER JOIN purchase ON purchase.id_g=goods.id_g;
+------+--------------------+
| id_g | name               |
+------+--------------------+
|    1 | 饅頭               |
|    1 | 饅頭               |
|    4 | 抹茶団子           |
|    2 | みたらし団子       |
|    1 | 饅頭               |
|    2 | みたらし団子       |
| NULL | 八つ橋             |
+------+--------------------+
7 rows in set (0.00 sec)

RIGHT JOIN

mysql> SELECT purchase.id_g,name FROM goods RIGHT JOIN purchase ON purchase.id_g=goods.id_g;
+------+--------------------+
| id_g | name               |
+------+--------------------+
|    1 | 饅頭               |
|    1 | 饅頭               |
|    1 | 饅頭               |
|    2 | みたらし団子       |
|    2 | みたらし団子       |
|    4 | 抹茶団子           |
+------+--------------------+

JOINするテーブルにも同じフィールド名がある場合

フィールド名がある場合どちらかのテーブル名で修飾する必要があります。

mysql> SELECT id_c, id_p, fullname FROM customer JOIN purchase;
ERROR 1052 (23000): Column 'id_c' in field list is ambiguous
mysql> SELECT purchase.id_c, id_p, fullname FROM customer JOIN purchase;

2つ以上のテーブルの結合

mysql> SELECT id_p, fullname, name FROM purchase JOIN customer JOIN goods ON purchase.id_c = customer.id_c And purchase.id_g = goods.id_g;
+------+-----------------------------+--------------------+
| id_p | fullname                    | name               |
+------+-----------------------------+--------------------+
|    1 | 山田花子                    | 饅頭               |
|    2 | 佐藤優                      | 饅頭               |
|    5 | マイケル・トムソン          | 饅頭               |
|    4 | 佐藤優                      | みたらし団子       |
|    6 | ディーン・ドナルド          | みたらし団子       |
|    3 | マイケル・トムソン          | 抹茶団子           |
+------+-----------------------------+--------------------+
6 rows in set (0.00 sec)

テーブルのエイリアス

テーブルの結合を行う際は、テーブルのエイリアス名をつけることがよく有ります。

mysql> SELECT id_p, fullname, name FROM purchase AS p, customer, goods ON p.id_c = customer.id_c And p.id_g = goods.id_g;

→エラー

自己結合

mysql> SELECT C1.fullname, C2.age, C2.fullname, C2.age FROM customer AS C1 JOIN customer AS C2 ON C1.age=C2.age AND C1.id_c < C2.id_c;

※再帰結合とは、同一のテーブルを別名を使って結合することです。

参考URL

*SELECT構文：JOINを使ってテーブルを結合する(http://rfs.jp/sb/sql/s03/03_3.html)
* 整数型(http://www.dbonline.jp/mysql/type/index1.html)
* MySQLでdecimalを使うときの注意点(https://liginc.co.jp/programmer/archives/1643)

MYSQL、JOIN（内部結合・外部結合）事始め

概要

背景

目次

データベースとテーブルを作成

購入履歴テーブルの作成

補足：Type（データ型）

データを挿入する

顧客テーブルの作成

商品テーブルの作成

補足：Type（データ型）

JOINを試してみる

基本文法

内部結合

外部結合

LEFT JOIN

RIGHT JOIN

JOINするテーブルにも同じフィールド名がある場合

2つ以上のテーブルの結合

テーブルのエイリアス

自己結合

参考URL