LoginSignup
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@daikumatan(長尾 太介 (Daisuke Nagao))

MySQL の サンプルデータセット "world" を用いて学ぶSQLの基礎

Last updated at Posted at 2022-09-01

事前準備

DockerによるMYSQL環境の構築

データベースの選択

データベースの確認

SHOW databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
| world              |
+--------------------+

データベースの変更

USE world;

Database changed

デーブルの存在確認

SHOW tables;

+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+

Hands-on

デーブルの概要確認

countryテーブル

DESCRIBE country;

+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | decimal(10,2)                                                                         | NO   |     | 0.00    |       |
| IndepYear      | smallint                                                                              | YES  |     | NULL    |       |
| Population     | int                                                                                   | NO   |     | 0       |       |
| LifeExpectancy | decimal(3,1)                                                                          | YES  |     | NULL    |       |
| GNP            | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| GNPOld         | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int                                                                                   | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+

countrylanguageテーブル

DESCRIBE countrylanguage;

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+

cityテーブル

DESCRIBE city;

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

データの取得

データの読み込み例1

countryテーブルから国名と人口を取り出す

SELECT Name, Population FROM country;

+----------------------------------------------+------------+
| Name                                         | Population |
+----------------------------------------------+------------+
| Aruba                                        |     103000 |
| Afghanistan                                  |   22720000 |
| Angola                                       |   12878000 |
| Anguilla                                     |       8000 |
| Albania                                      |    3401200 |
(省略)

countrylanguageテーブルからすべての列を取り出す

SELECT * FROM countrylanguage;

+-------------+---------------------------+------------+------------+
| CountryCode | Language                  | IsOfficial | Percentage |
+-------------+---------------------------+------------+------------+
| ABW         | Dutch                     | T          |        5.3 |
| ABW         | English                   | F          |        9.5 |
| ABW         | Papiamento                | F          |       76.7 |
| ABW         | Spanish                   | F          |        7.4 |
(省略)

countrylanguageテーブルからLanguageのカラムがEnglishであるデータを取得

SELECT * FROM countrylanguage WHERE Language = 'English';

+-------------+----------+------------+------------+
| CountryCode | Language | IsOfficial | Percentage |
+-------------+----------+------------+------------+
| ABW         | English  | F          |        9.5 |
| AIA         | English  | T          |        0.0 |
| ANT         | English  | F          |        7.8 |
| ASM         | English  | T          |        3.1 |
| ATG         | English  | T          |        0.0 |
(省略)

比較演算子

人口が1億以上の国と人口

SELECT Name,Population FROM country WHERE Population >= 100000000;

+--------------------+------------+
| Name               | Population |
+--------------------+------------+
| Bangladesh         |  129155000 |
| Brazil             |  170115000 |
| China              | 1277558000 |
| Indonesia          |  212107000 |
| India              | 1013662000 |
| Japan              |  126714000 |
| Nigeria            |  111506000 |
| Pakistan           |  156483000 |
| Russian Federation |  146934000 |
| United States      |  278357000 |
+--------------------+------------+

worldのデータベースには無いが、日付も比較演算子が使えることを覚えておこう。

参考
SELECT *
FROM purchases
WHERE purchased_at <= "2017-08-01";

LIKE 演算子

「ある文字を含むデータ」を取得したい場合は、「〜のような」という意味を持つ、「LIKE演算子」

  • LIKE演算子では％をワイルドカードとして扱う
  • %を調整することで、前方一致、後方一致を調整可能

以下はSourthを含む国名の例。

SELECT Name FROM country WHERE Name LIKE '%South%';

+----------------------------------------------+
| Name                                         |
+----------------------------------------------+
| French Southern territories                  |
| South Korea                                  |
| South Georgia and the South Sandwich Islands |
| South Africa                                 |
+----------------------------------------------+

NOT演算子

Continentに North Americaを含まないもの

SELECT Name,Continent FROM country WHERE NOT Continent = 'North America';

+----------------------------------------------+---------------+
| Name                                         | Continent     |
+----------------------------------------------+---------------+
| Afghanistan                                  | Asia          |
| Angola                                       | Africa        |
| Albania                                      | Europe        |
| Andorra                                      | Europe        |
| United Arab Emirates                         | Asia          |
| Argentina                                    | South America |
| Armenia                                      | Asia          |
(省略)

国名にSouthを含まないもの

mysql> SELECT Name FROM country WHERE NOT Name LIKE '%South%';
+---------------------------------------+
| Name                                  |
+---------------------------------------+
| Aruba                                 |
| Afghanistan                           |
| Angola                                |
| Anguilla                              |
| Albania                               |
(省略)

NULLのデータを取得する

平均寿命のデータ LifeExpectancy のデータがない国々

NULLのデータ
SELECT Name FROM country WHERE LifeExpectancy IS NULL;

+----------------------------------------------+
| Name                                         |
+----------------------------------------------+
| Antarctica                                   |
| French Southern territories                  |
| Bouvet Island                                |
| Cocos (Keeling) Islands                      |
| Christmas Island                             |
(省略)

平均寿命のデータ LifeExpectancy のデータがある国々

NULLのデータ
SELECT Name FROM country WHERE NOT LifeExpectancy IS NULL;

+---------------------------------------+
| Name                                  |
+---------------------------------------+
| Aruba                                 |
| Afghanistan                           |
| Angola                                |
| Anguilla                              |
| Albania                               |
(省略)

AND/OR 演算子

1億人以上いる国でアジアの国

SELECT Name,Population,Continent FROM country 
WHERE Population >= 100000000
AND Continent = 'Asia';

+------------+------------+-----------+
| Name       | Population | Continent |
+------------+------------+-----------+
| Bangladesh |  129155000 | Asia      |
| China      | 1277558000 | Asia      |
| Indonesia  |  212107000 | Asia      |
| India      | 1013662000 | Asia      |
| Japan      |  126714000 | Asia      |
| Pakistan   |  156483000 | Asia      |
+------------+------------+-----------+

1億人以上でアジアかヨーロッパの国々

OR
SELECT Name,Population,Continent FROM country 
WHERE Population >= 100000000
AND (Continent = 'Asia' OR Continent = 'Europe');

+--------------------+------------+-----------+
| Name               | Population | Continent |
+--------------------+------------+-----------+
| Bangladesh         |  129155000 | Asia      |
| China              | 1277558000 | Asia      |
| Indonesia          |  212107000 | Asia      |
| India              | 1013662000 | Asia      |
| Japan              |  126714000 | Asia      |
| Pakistan           |  156483000 | Asia      |
| Russian Federation |  146934000 | Europe    |
+--------------------+------------+-----------+

ORDER BY によるデータの並び替え

データを並び替えるためには、「〜順に並べる」という意味の「ORDER BY」を用います。
またデータを並び替えるためには、ORDER BY 並べ替えたいカラム名 並べ方 とします。

並べ方について

  • 昇順: ASC
  • 降順: DESC

人口が多い順

降順(DESC)
SELECT Name, Population FROM country 
ORDER BY Population DESC;

+----------------------------------------------+------------+
| Name                                         | Population |
+----------------------------------------------+------------+
| China                                        | 1277558000 |
| India                                        | 1013662000 |
| United States                                |  278357000 |
| Indonesia                                    |  212107000 |
| Brazil                                       |  170115000 |
(省略)

人口が少ない順

昇順(ASC)
SELECT Name, Population FROM country 
ORDER BY Population ASC;

+----------------------------------------------+------------+
| Name                                         | Population |
+----------------------------------------------+------------+
| Antarctica                                   |          0 |
| French Southern territories                  |          0 |
| Bouvet Island                                |          0 |
| Heard Island and McDonald Islands            |          0 |
| British Indian Ocean Territory               |          0 |
(省略)

LIMITによるデータ件数の制限

「最大で何件取得するか」を指定するためには、「制限する」という意味の「LIMIT」を用います。「LIMIT」は左の図のように「 データの件数」を指定します。

人口が多い順、Best5

基本
SELECT Name, Population FROM country 
ORDER BY Population DESC
LIMIT 5;

+---------------+------------+
| Name          | Population |
+---------------+------------+
| China         | 1277558000 |
| India         | 1013662000 |
| United States |  278357000 |
| Indonesia     |  212107000 |
| Brazil        |  170115000 |
+---------------+------------+

アジアで人口が多い順ベスト5

WHEREと組み合わせ可能
SELECT Name, Population FROM country 
WHERE Continent = 'Asia'
ORDER BY Population DESC
LIMIT 5;

+------------+------------+
| Name       | Population |
+------------+------------+
| China      | 1277558000 |
| India      | 1013662000 |
| Indonesia  |  212107000 |
| Pakistan   |  156483000 |
| Bangladesh |  129155000 |
+------------+------------+

検索結果の加工

DISTINCT による重複の削除

世界で使われている言語を抽出。例えば、スペイン, アルゼンチンはどちらもSpanishである。

重複するデータを除いたnameカラムのデータを取得
SELECT DISTINCT(Language) FROM countrylanguage;

+---------------------------+
| Language                  |
+---------------------------+
| Dutch                     |
| English                   |
| Papiamento                |
| Spanish                   |
| Balochi                   |
| Dari                      |
(省略)

四則演算

GNPの110%を計算

消費税を含めたpriceを追加表示
SELECT Name, GNP, GNP * 1.1 FROM country;

+----------------------------------------------+------------+-------------+
| Name                                         | GNP        | GNP * 1.1   |
+----------------------------------------------+------------+-------------+
| Aruba                                        |     828.00 |     910.800 |
| Afghanistan                                  |    5976.00 |    6573.600 |
| Angola                                       |    6648.00 |    7312.800 |
| Anguilla                                     |      63.20 |      69.520 |
| Albania                                      |    3205.00 |    3525.500 |
(省略)

SUM関数

世界の人口

SELECT SUM(Population) FROM country;

+-----------------+
| SUM(Population) |
+-----------------+
|      6078749450 |
+-----------------+

アジアの人口

SELECT SUM(Population) FROM country
WHERE Continent = 'Asia';

+-----------------+
| SUM(Population) |
+-----------------+
|      3705025700 |
+-----------------+

AVG関数 (平均)

Sourth America のGNPの平均

SELECT AVG(GNP) FROM country 
WHERE Continent = 'South America';

+---------------+
| AVG(GNP)      |
+---------------+
| 107991.000000 |
+---------------+

COUNT関数

英語を母国語とする国の数

SELECT COUNT(*) FROM countrylanguage
WHERE Language = 'English';

+----------+
| COUNT(*) |
+----------+
|       60 |
+----------+

LifeExpectancyがNullでない国の数。
LifeExpectancyのデータに関して、COUNT関数でカラム名を指定した場合、nullになっているデータの数は計算されないことに注意

purchasesテーブルのnameカラムのデータの数を取得
SELECT COUNT(LifeExpectancy) FROM country;

+-----------------------+
| COUNT(LifeExpectancy) |
+-----------------------+
|                   222 |
+-----------------------+

MAX・MIN関数

GNPの最大

SELECT MAX(Population) FROM country;
+------------+
| MAX(GNP)   |
+------------+
| 8510700.00 |
+------------+

GNPの最小

SELECT MIN(GNP) FROM country;
+----------+
| MIN(GNP) |
+----------+
|     0.00 |
+----------+

GROUP BYによるグループ化と集計

GROUP BYを用いる場合、SELECTで使えるのは、GROUP BYに指定しているカラム名と、集計関数のみです。
図ではSELECTで集計関数を使っていないため、日付ごとに集計された値が取得できません。

大陸毎の国数

SELECT Continent,COUNT(*) FROM country
GROUP BY Continent;

+---------------+----------+
| Continent     | COUNT(*) |
+---------------+----------+
| North America |       37 |
| Asia          |       51 |
| Africa        |       58 |
| Europe        |       46 |
| South America |       14 |
| Oceania       |       28 |
| Antarctica    |        5 |
+---------------+----------+

大陸毎の人口

purchased_atごとのお金を使った数を取得
SELECT Continent,SUM(Population) FROM country
GROUP BY Continent;

+---------------+-----------------+
| Continent     | SUM(Population) |
+---------------+-----------------+
| North America |       482993000 |
| Asia          |      3705025700 |
| Africa        |       784475000 |
| Europe        |       730074600 |
| South America |       345780000 |
| Oceania       |        30401150 |
| Antarctica    |               0 |
+---------------+-----------------+

複数のカラムを使ってグループ化

LanguageとIsOfficialでグループ化

SELECT Language, IsOfficial, COUNT(*) FROM countrylanguage
GROUP BY Language, IsOfficial;

+---------------------------+------------+----------+
| Language                  | IsOfficial | COUNT(*) |
+---------------------------+------------+----------+
| Dutch                     | T          |        4 |
| English                   | F          |       16 |
| Papiamento                | F          |        1 |
| Spanish                   | F          |        8 |
| Balochi                   | F          |        4 |
| Dari                      | T          |        1 |
| Pashto                    | T          |        1 |
| Turkmenian                | F          |        2 |
(省略)

WHEREとGROUP BY

Percentageが90%の国で、LanguageとIsOfficialを使ってグループ化し、その国数をもとめる。

SELECT Language, IsOfficial, COUNT(*) FROM countrylanguage
WHERE Percentage > 90.0
GROUP BY Language, IsOfficial;

+----------------+------------+----------+
| Language       | IsOfficial | COUNT(*) |
+----------------+------------+----------+
| Albaniana      | T          |        1 |
| Spanish        | T          |       12 |
| Armenian       | T          |        1 |
| Samoan         | T          |        1 |
| Creole English | F          |        7 |
| German         | T          |        2 |
(省略)

HAVINGを用いてグループ化したデータを更に絞り込む

GROUP BYでグループ化したデータを更に絞り込みたい場合には、HAVINGを用います。図のように「GROUP BY カラム名 HAVING 条件」のようにすることで、条件を満たすグループを取得することができます。

以下の順番でSQLは実行されます。

  1. 検索: WEHERE
  2. GROUP BY
  3. 関数: COUNT, SUM, AVG, MIN, MAX
  4. HAVING

HAVINGはグループ化された後のテーブルから検索するため、条件文で使うカラムは必ずグループ化されたテーブルのカラムを使います。

Percentageが90%の国で、LanguageとIsOfficialを使ってグループ化し、その国数が5カ国以上の条件を抽出する

SELECT Language, IsOfficial, COUNT(*) FROM countrylanguage
WHERE Percentage > 90.0
GROUP BY Language, IsOfficial
HAVING COUNT(*) >= 5; -- <==これに注意

+----------------+------------+----------+
| Language       | IsOfficial | COUNT(*) |
+----------------+------------+----------+
| Spanish        | T          |       12 |
| Creole English | F          |        7 |
| Arabic         | T          |        7 |
+----------------+------------+----------+

SELECT SUM(price), purchased_at, character_name
FROM purchases
GROUP BY purchased_at, character_name
HAVING SUM(price) > 3000; -- <==これに注意

サブクエリ

サブクエリの基本

サブクエリを含むクエリの場合の順番

  1. サブクエリが実行
  2. 外側にあるクエリが実行

韓国のGNP以上の国々

SELECT Name,GNP FROM country
WHERE GNP > (
  SELECT GNP FROM country
  WHERE Name = 'South Korea'
);

+----------------+------------+
| Name           | GNP        |
+----------------+------------+
| Argentina      |  340238.00 |
| Australia      |  351182.00 |
| Brazil         |  776739.00 |
| Canada         |  598862.00 |
| China          |  982268.00 |
| Germany        | 2133367.00 |
| Spain          |  553233.00 |
| France         | 1424285.00 |
| United Kingdom | 1378330.00 |
| India          |  447114.00 |
| Italy          | 1161755.00 |
| Japan          | 3787042.00 |
| South Korea    |  320749.00 |
| Mexico         |  414972.00 |
| Netherlands    |  371362.00 |
| United States  | 8510700.00 |
+----------------+------------+

ヨーロッパの平均GNPよりGNPが大きい国

平均得点数より得点数が多い選手名
SELECT Name,GNP FROM country
WHERE GNP > (
  SELECT AVG(GNP) FROM country
  WHERE Continent = 'Europe'
);

+--------------------+------------+
| Name               | GNP        |
+--------------------+------------+
| Argentina          |  340238.00 |
| Australia          |  351182.00 |
| Austria            |  211860.00 |
| Belgium            |  249704.00 |
| Brazil             |  776739.00 |
| Canada             |  598862.00 |
| Switzerland        |  264478.00 |
| China              |  982268.00 |
| Germany            | 2133367.00 |
| Spain              |  553233.00 |
| France             | 1424285.00 |
| United Kingdom     | 1378330.00 |
| India              |  447114.00 |
| Italy              | 1161755.00 |
| Japan              | 3787042.00 |
| South Korea        |  320749.00 |
| Mexico             |  414972.00 |
| Netherlands        |  371362.00 |
| Russian Federation |  276608.00 |
| Sweden             |  226492.00 |
| Turkey             |  210721.00 |
| Taiwan             |  256254.00 |
| United States      | 8510700.00 |
+--------------------+------------+

AS によるColumn名の変更

ASを使うことでカラム名などに別名を定義することができます。
「カラム名 AS "名前"」で、カラム名に定義する名前を指定します。

SELECT Language, COUNT(*) AS 'Number of Countries' FROM countrylanguage
WHERE Percentage > 90.0
GROUP BY Language
HAVING COUNT(*) >= 5;

+----------------+---------------------+
| Language       | Number of Countries |
+----------------+---------------------+
| Spanish        |                  12 |
| Creole English |                   7 |
| Arabic         |                   8 |
+----------------+---------------------+

複数テーブルの結合

JOINによるテーブルの結合の説明

JOINは複数のテーブルを1つに結合したいときに使います。ONで条件を指定して、テーブルAにテーブルBを結合します。結合したテーブルは1つのテーブルとしてデータを取得することができます。

はじめにJOINが実行されます。その次に、結合されたテーブルに対してSELECTが実行されます

JOINを使ったテーブルの結合
SELECT * FROM countrylanguage
-- 結合するテーブル名を追加してください
JOIN country
-- 結合条件を追加してください
ON countrylanguage.CountryCode = country.Code

複数テーブルにおける同名カラムにおける指定方法

複数のテーブルに同じカラム名が存在するときは、「テーブル名.カラム名」で指定しなければなりません。例えばnameカラムが複数のテーブルにあるときは、「それぞれのテーブル名.name」で指定しましょう。

以下の順番でSQLは実行されます。

  1. テーブルの指定: FROM
  2. 結合: ON, JOIN
  3. 取得条件: WEHERE
  4. グループ化: GROUP BY
  5. 関数: COUNT, SUM, AVG, MIN, MAX
  6. HAVING
  7. 検索: SELECT, DISTINCT
  8. ORDER BY
  9. LIMIT

テーブルの結合例1: 日本で使用されている言語比率と人口

SELECT Name, Language, Percentage, Population * Percentage AS 'Population' FROM countrylanguage
JOIN country
ON countrylanguage.CountryCode = country.Code
WHERE country.Name = 'Japan';

+-------+----------------------+------------+---------------+
| Name  | Language             | Percentage | Population2   |
+-------+----------------------+------------+---------------+
| Japan | Ainu                 |        0.0 |           0.0 |
| Japan | Chinese              |        0.2 |    25342800.0 |
| Japan | English              |        0.1 |    12671400.0 |
| Japan | Japanese             |       99.1 | 12557357400.0 |
| Japan | Korean               |        0.5 |    63357000.0 |
| Japan | Philippene Languages |        0.1 |    12671400.0 |
+-------+----------------------+------------+---------------+

テーブルの結合例2: 世界における使用人数が多い言語TOP5

出身国ごとの合計得点を取得
SELECT Language, SUM(Population * Percentage) AS 'Population' FROM countrylanguage
JOIN country
ON countrylanguage.CountryCode = country.Code
GROUP BY countrylanguage.Language
ORDER BY SUM(Population * Percentage) DESC
LIMIT 5;

+----------+----------------+
| Language | Population     |
+----------+----------------+
| Chinese  | 119184353900.0 |
| Hindi    |  40563307000.0 |
| Spanish  |  35502946200.0 |
| English  |  34707786730.0 |
| Arabic   |  23383923870.0 |
+----------+----------------+

テーブルの結合例3: NULLを含んだ場合のJOIN実行結果例

JOINを使った結合は、FROMで指定したテーブルを基準に実行されるが、外部キーがNULLのレコードは、実行結果に表示されない。
それを実際に試してみよう。

日本の場合は首都は東京だがcountry テーブルには首都のIDが記されている。

SELECT Name,Capital from country
WHERE Name = 'Japan';

+-------+---------+
| Name  | Capital |
+-------+---------+
| Japan |    1532 |
+-------+---------+

しかし、country テーブルにはCapitalがNULLの国が７カ国ある。

SELECT Name,Capital from country
WHERE Capital IS NULL;

+----------------------------------------------+---------+
| Name                                         | Capital |
+----------------------------------------------+---------+
| Antarctica                                   |    NULL |
| French Southern territories                  |    NULL |
| Bouvet Island                                |    NULL |
| Heard Island and McDonald Islands            |    NULL |
| British Indian Ocean Territory               |    NULL |
| South Georgia and the South Sandwich Islands |    NULL |
| United States Minor Outlying Islands         |    NULL |
+----------------------------------------------+---------+

JOINする前にいったん国数を数えておく。countryテーブルにはCapitalがNULLの国含め、合計239カ国の情報が存在することがわかる。

SELECT COUNT(*) from country;
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.01 sec)

ではこの、Capitalがコードのままだと、都市名がわからないので、cityのテーブルとJOINして実際の都市名にしてみよう。

SELECT country.Name,city.Name FROM country 
JOIN city ON country.Capital = city.ID;

+---------------------------------------+------------------------------------+
| Name                                  | Name                               |
+---------------------------------------+------------------------------------+
| Aruba                                 | Oranjestad                         |
| Afghanistan                           | Kabul                              |
| Angola                                | Luanda                             |
| Anguilla                              | The Valley                         |

       ~~~~ (省略) ~~~~

| Zimbabwe                              | Harare                             |
+---------------------------------------+------------------------------------+
232 rows in set (0.02 sec)

あれ、232 rows in set となっており232カ国しかない。つまり９カ国分がないことがわかる。
実際に国数をもう一度ちゃんとカウントしてやる

SELECT COUNT(*) FROM country
JOIN city ON country.Capital = city.ID;

+----------+
| COUNT(*) |
+----------+
|      232 |
+----------+

これはCapitalがNULLのデータは自動的に削除されているためである。
CapitalがNULLで抽出してやると以下のように、確かにゼロである。

SELECT COUNT(*) FROM country
JOIN city ON country.Capital = city.ID
WHERE country.Capital IS NULL;

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

NULLも含めて表示したいとき、このケースではLEFT JOINを用いる。239 rows となっていることがわかる。

SELECT country.Name,city.Name FROM country 
LEFT JOIN city ON country.Capital = city.ID;

+---------------------------------------+------------------------------------+
| Name                                  | Name                               |
+---------------------------------------+------------------------------------+
| Aruba                                 | Oranjestad                         |
| Afghanistan                           | Kabul                              |
| Angola                                | Luanda                             |
| Anguilla                              | The Valley                         |

       ~~~~ (省略) ~~~~

| Zimbabwe                              | Harare                             |
+---------------------------------------+------------------------------------+
239 rows in set (0.02 sec)

今度はNULLのデータも確かにあることが確認できる

SELECT COUNT(*) FROM country
LEFT JOIN city ON country.Capital = city.ID
WHERE country.Capital IS NULL;

+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

テーブルの結合例4: 3つのテーブルの結合

日本の母国語と首都の情報を得る

SELECT country.Name, Language AS "MainLang", percentage, city.Name AS "Capital" FROM country
LEFT JOIN city
ON country.Capital = city.ID
JOIN countrylanguage 
ON country.Code = countrylanguage.CountryCode
WHERE country.Name = 'Japan'
ORDER BY percentage DESC
LIMIT 1;

+-------+----------+------------+---------+
| Name  | Language | percentage | Capital |
+-------+----------+------------+---------+
| Japan | Japanese |       99.1 | Tokyo   |
+-------+----------+------------+---------+

上記を日本だけでなくすべての国でやってみる
(233カ国のデータはでるけど6カ国分のデータが無くなっている問題があるけど、今回は無視)

SELECT country.Name, Language AS "MainLang", percentage, city.Name AS "Capital" FROM country
LEFT JOIN city
ON country.Capital = city.ID
JOIN (SELECT ROW_NUMBER() OVER (
          PARTITION BY CountryCode
          ORDER BY Percentage DESC) AS LangRank, CountryCode, Language, Percentage
      FROM countrylanguage) AS tmplang
ON country.Code = tmplang.CountryCode
WHERE tmplang.LangRank = 1;

+---------------------------------------+----------------------+------------+------------------------------------+
| Name                                  | MainLang             | percentage | Capital                            |
+---------------------------------------+----------------------+------------+------------------------------------+
| Aruba                                 | Papiamento           |       76.7 | Oranjestad                         |
| Afghanistan                           | Pashto               |       52.4 | Kabul                              |
| Angola                                | Ovimbundu            |       37.2 | Luanda                             |
| Anguilla                              | English              |        0.0 | The Valley                         |                         |
            ~~~~ (省略) ~~~~
1
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?