05_サブクエリによるデータの利用

概要

• サブクエリによるデータの利用方法をまとめる。

サブクエリ(副問い合わせ)

概要

• 主問い合わせと副問い合わせの二段階のクエリを一括で処理する。
• 副問い合わせのクエリを実行したのち，主問い合わせを実行する。

サンプル

WHERE句による利用

最大値の行を表示

• サンプルデータベースworldを使用

SELECT Name, Population FROM city 
WHERE Population = 
(SELECT MAX(Population) FROM city);

• 実行結果

MariaDB [world]> SELECT Name, Population FROM city WHERE Population = (SELECT MAX(Population) FROM city);
+-----------------+------------+
| Name            | Population |
+-----------------+------------+
| Mumbai (Bombay) |   10500000 |
+-----------------+------------+
1 row in set (0.07 sec)

最小値の行を表示

SELECT Name, Population FROM city 
WHERE Population = 
(SELECT MIN(Population) FROM city);

• 実行結果

MariaDB [world]> SELECT Name, Population FROM city WHERE Population = (SELECT MIN(Population) FROM city);
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Adamstown |         42 |
+-----------+------------+
1 row in set (0.01 sec)

(日本の)平均人口より多い年を表示

SELECT Name, Population FROM city 
WHERE Population > 
(SELECT AVG(Population) FROM city
 WHERE CountryCode = 'JPN')
AND CountryCode = 'JPN' 
LIMIT 3;

• 実行結果

MariaDB [world]> SELECT Name, Population FROM city WHERE Population > (SELECT AVG(Population) FROM city WHERE CountryCode = 'JPN')AND CountryCode = 'JPN' LIMIT 3;
+---------------------+------------+
| Name                | Population |
+---------------------+------------+
| Tokyo               |    7980230 |
| Jokohama [Yokohama] |    3339594 |
| Osaka               |    2595674 |
+---------------------+------------+
3 rows in set (0.00 sec)

---

FROM句による利用

• サブクエリに対して別名を指定しなければいけないので注意

大陸ごとの国数の最小値，最大値，平均値を表示

SELECT MIN(cnt),MAX(cnt),AVG(cnt) 
FROM 
(SELECT Continent, COUNT(*) AS cnt FROM country 
GROUP BY Continent) AS cont;

• 実行結果

MariaDB [world]> SELECT MIN(cnt),MAX(cnt),AVG(cnt) FROM (SELECT Continent, COUNT(*) AS cnt FROM country GROUP BY Continent) AS cont;
+----------+----------+----------+
| MIN(cnt) | MAX(cnt) | AVG(cnt) |
+----------+----------+----------+
|        5 |       58 |  34.1429 |
+----------+----------+----------+
1 row in set (0.00 sec)