MySQL 多表結合クエリの基本
データ準備
#テーブル作成
CREATE TABLE division(
id INT,
name VARCHAR(20)
);
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
gender ENUM('male','female') NOT NULL DEFAULT 'male',
age INT,
div_id INT
);
#データ挿入
INSERT INTO division VALUES
(100,'開発部'),
(101,'マーケティング'),
(102,'営業'),
(103,'人事');
INSERT INTO employee(name,gender,age,div_id) VALUES
('田中','male',35,100),
('佐藤','female',28,101),
('鈴木','male',42,101),
('高橋','female',31,102),
('伊藤','male',55,100),
('山田','female',24,104)
;
#テーブル構造とデータの確認
mysql> DESC division;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)
mysql> DESC employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| gender | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| div_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)
#divisionテーブルとemployeeテーブル
mysql> SELECT * FROM division;
+------+-----------+
| id | name |
+------+-----------+
| 100 | 開発部 |
| 101 | マーケティング |
| 102 | 営業 |
| 103 | 人事 |
+------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM employee;
+----+--------+--------+------+--------+
| id | name | gender | age | div_id |
+----+--------+--------+------+--------+
| 1 | 田中 | male | 35 | 100 |
| 2 | 佐藤 | female | 28 | 101 |
| 3 | 鈴木 | male | 42 | 101 |
| 4 | 高橋 | female | 31 | 102 |
| 5 | 伊藤 | male | 55 | 100 |
| 6 | 山田 | female | 24 | 104 |
+----+--------+--------+------+--------+
6 rows in set (0.00 sec)
結合クエリ
クロス結合 - どのような条件も適用せず、デカルト積を生成
SELECT * FROM table1, table2;
内部結合 (一般的) - 一致する行のみを結合
SELECT * FROM employee INNER JOIN division ON 条件(テーブル1.フィールド=テーブル2.フィールド)
まとめ: 2つのテーブルの共通部分を見つけ、条件を使用してデカルト積の結果から正しい結果をフィルタリングします
外部結合
左外部結合 (一般的) - 左側のテーブルのすべてのレコードを優先して表示
SELECT * FROM employee LEFT JOIN division ON 条件(テーブル1.フィールド=テーブル2.フィールド)
右外部結合 - 右側のテーブルのすべてのレコードを優先して表示
SELECT * FROM employee RIGHT JOIN division ON 条件(テーブル1.フィールド=テーブル2.フィールド)
完全外部結合 - 左右両方のテーブルのすべてのレコードを表示
クエリ (MySQLにはFULL JOINがありませんが、LEFT JOINとRIGHT JOINを組み合わせて実現できます)
SELECT * FROM table1 LEFT JOIN table2 ON 条件((table1.field=table2.field))
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON 条件((table1.field=table2.field));
#完全外部結合: 内部結合の結果に、左側にはあるが右側にはない、右側にはあるが左側にはない結果を追加
#注意: MySQLは完全外部結合FULL JOINをサポートしていません
#強調: MySQLではこの方法で間接的に完全外部結合を実現できます
#注意: UNIONとUNION ALLの違い: UNIONは重複したレコードを削除します
練習
1. 開発部のすべての従業員の情報を見つける
#答:
mysql> SELECT * FROM employee INNER JOIN division ON division.id=employee.div_id WHERE division.name='開発部';
+----+--------+--------+------+--------+------+-----------+
| id | name | gender | age | div_id | id | name |
+----+--------+--------+------+--------+------+-----------+
| 1 | 田中 | male | 35 | 100 | 100 | 開発部 |
| 5 | 伊藤 | male | 55 | 100 | 100 | 開発部 |
+----+--------+--------+------+--------+------+-----------+
2 rows in set (0.00 sec)
2. マーケティング部門のすべての従業員の名前を見つける
#答: (名前が長すぎる場合はエイリアスを使用できます)
mysql> SELECT employee.name FROM employee INNER JOIN division AS div ON div.id=employee.div_id WHERE div.name='マーケティング';
+--------+
| name |
+--------+
| 佐藤 |
| 鈴木 |
+--------+
2 rows in set (0.00 sec)
3. 年齢が42歳以上の従業員の名前と所属部署名を見つける
#答:
mysql> SELECT employee.name,div.name FROM employee INNER JOIN division AS div ON div.id=employee.div_id WHERE age>42;
+--------+-----------+
| name | name |
+--------+-----------+
| 伊藤 | 開発部 |
+--------+-----------+
1 row in set (0.00 sec)
4. 内部結合を使用してemployeeとdivisionテーブルをクエリし、ageフィールドの昇順で表示する
答:
mysql> SELECT * FROM employee INNER JOIN division AS div ON div.id=employee.div_id ORDER BY age;
+----+--------+--------+------+--------+------+-----------+
| id | name | gender | age | div_id | id | name |
+----+--------+--------+------+--------+------+-----------+
| 2 | 佐藤 | female | 28 | 101 | 101 | マーケティング |
| 4 | 高橋 | female | 31 | 102 | 102 | 営業 |
| 1 | 田中 | male | 35 | 100 | 100 | 開発部 |
| 3 | 鈴木 | male | 42 | 101 | 101 | マーケティング |
| 5 | 伊藤 | male | 55 | 100 | 100 | 開発部 |
+----+--------+--------+------+--------+------+-----------+
5 rows in set (0.05 sec)
5. 開発部とマーケティング部門のすべての従業員の名前を見つける
#答:
mysql> SELECT employee.name FROM employee INNER JOIN division AS div ON div.id = employee.div_id WHERE div.name IN ('開発部','マーケティング');
+--------+
| name |
+--------+
| 田中 |
| 佐藤 |
| 鈴木 |
| 伊藤 |
+--------+
4 rows in set (0.00 sec)
サブクエリ
#1: サブクエリは、1つのクエリ文を別のクエリ文の中にネストすることです。
#2: 内側のクエリ文のクエリ結果は、外側のクエリ文にクエリ条件を提供できます。
#3: サブクエリには、IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTSなどのキーワードを含めることができます
#4: 比較演算子:=、!=、>、<なども含めることができます
#5: 結合クエリを多用してください。結合クエリはサブクエリよりも効率的です
1. サブクエリを使用して、営業部門のすべての従業員の名前を見つける
#解決策
①まずdivisionテーブルで営業部門のIDを見つけます
mysql> SELECT id FROM division WHERE name = '営業';
+------+
| id |
+------+
| 102 |
+------+
1 row in set (0.00 sec)
②次にemployeeテーブルでdiv_id = 102を見つけます
mysql> SELECT name FROM employee WHERE div_id = 102;
+--------+
| name |
+--------+
| 高橋 |
+--------+
1 row in set (0.00 sec)
③サブクエリ
mysql> SELECT name FROM employee WHERE div_id =(SELECT id FROM division WHERE name = '営業');
+--------+
| name |
+--------+
| 高橋 |
+--------+
1 row in set (0.00 sec)
2. サブクエリを使用して、営業部門とマーケティング部門のすべての従業員の名前を見つける
①まずdivisionテーブルで営業部門とマーケティング部門のIDを見つけます
mysql> SELECT id FROM division WHERE name = '営業' OR name = 'マーケティング';
+------+
| id |
+------+
| 101 |
| 102 |
+------+
2 rows in set (0.00 sec)
②サブクエリ
mysql> SELECT name FROM employee WHERE div_id IN (SELECT id FROM division WHERE name = '営業' OR name = 'マーケティング');
+--------+
| name |
+--------+
| 佐藤 |
| 鈴木 |
| 高橋 |
+--------+
3 rows in set (0.00 sec)
1. INキーワードを含むサブクエリ
①平均年齢が30歳以上の部署名をクエリします
SELECT id,name FROM division
WHERE id IN
(SELECT div_id FROM employee GROUP BY div_id HAVING AVG(age) > 30);
#結果
+------+-----------+
| id | name |
+------+-----------+
| 100 | 開発部 |
| 101 | マーケティング |
+------+-----------+
2 rows in set (0.00 sec)
②開発部の従業員の名前を見つけます
SELECT name FROM employee
WHERE div_id IN
(SELECT id FROM division WHERE name='開発部');
#結果
+--------+
| name |
+--------+
| 田中 |
| 伊藤 |
+--------+
2 rows in set (0.00 sec)
③1人未満の部署名を見つけます(サブクエリは従業員のある部署IDを返します)
SELECT name FROM division WHERE id NOT IN (SELECT DISTINCT div_id FROM employee);
#結果
+--------+
| name |
+--------+
| 人事 |
+--------+
1 row in set (0.02 sec)
2. 比較演算子を含むサブクエリ
#比較演算子:=、!=、>、>=、<、<=、<>
①すべての従業員の平均年齢より大きい従業員の名前と年齢をクエリします
mysql> SELECT name,age FROM employee WHERE age > (SELECT AVG(age) FROM employee);
+--------+------+
| name | age |
+--------+------+
| 伊藤 | 55 |
+--------+------+
1 row in set (0.00 sec)
②部署内の平均年齢より大きい従業員の名前、年齢をクエリします
SELECT t1.name,t1.age FROM employee t1
INNER JOIN
(SELECT div_id,AVG(age) avg_age FROM employee GROUP BY div_id) t2
ON t1.div_id = t2.div_id
WHERE t1.age > t2.avg_age;
#結果
+--------+------+
| name | age |
+--------+------+
| 鈴木 | 42 |
| 伊藤 | 55 |
+--------+------+
2 rows in set (0.04 sec)
3. EXISTSキーワードを含むサブクエリ
EXISTSキーワードは存在を意味します。EXISTSキーワードを使用する場合、内側のクエリ文はクエリ結果を返しません。
代わりに真偽値(TrueまたはFalse)を返します。
Trueが返された場合、外側のクエリ文がクエリを実行します。Falseが返された場合、外側のクエリ文はクエリを実行しません。
#divisionテーブルにdiv_id=103が存在する場合、True
SELECT * FROM employee
WHERE EXISTS
(SELECT id FROM division WHERE id=100);
#結果
+----+--------+--------+------+--------+
| id | name | gender | age | div_id |
+----+--------+--------+------+--------+
| 1 | 田中 | male | 35 | 100 |
| 2 | 佐藤 | female | 28 | 101 |
| 3 | 鈴木 | male | 42 | 101 |
| 4 | 高橋 | female | 31 | 102 |
| 5 | 伊藤 | male | 55 | 100 |
| 6 | 山田 | female | 24 | 104 |
+----+--------+--------+------+--------+
6 rows in set (0.00 sec)
#divisionテーブルにdiv_id=105が存在しない場合、False
mysql> SELECT * FROM employee
WHERE EXISTS
(SELECT id FROM division WHERE id=104);
Empty set (0.00 sec)