事前準備

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は実行されます。

検索: WEHERE
GROUP BY
関数: COUNT, SUM, AVG, MIN, MAX
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; -- <==これに注意

サブクエリ

サブクエリの基本

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

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

韓国の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は実行されます。

テーブルの指定: FROM
結合: ON, JOIN
取得条件: WEHERE
グループ化: GROUP BY
関数: COUNT, SUM, AVG, MIN, MAX
HAVING
検索: SELECT, DISTINCT
ORDER BY
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                         |                         |
            ~~~~ (省略) ~~~~