by shigemk2

当面は技術的なことしか書かない

複数テーブルの取り扱いについて

サンプルデータベース world を使った習作。

普通にselect count() from A, B;とかやっちゃうと、
A
Bぶんだけ数えてしまう。

mysql> select count(*) from Country;
+----------+
| count(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from City;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from City,Country;
+----------+
| count(*) |
+----------+
|   974881 |
+----------+

で、INNER JOINってやると、テーブルを結合できる。

mysql> select * from Country a INNER JOIN City b ON a.Code = b.CountryCode LIMIT 10;
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+-----+----------------+-------------+----------+------------+
| Code | Name        | Continent     | Region                    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP     | GNPOld  | LocalName             | GovernmentForm                               | HeadOfState              | Capital | Code2 | ID  | Name           | CountryCode | District | Population |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+-----+----------------+-------------+----------+------------+
| ABW  | Aruba       | North America | Caribbean                 |      193.00 |      NULL |     103000 |           78.4 |  828.00 |  793.00 | Aruba                 | Nonmetropolitan Territory of The Netherlands | Beatrix                  |     129 | AW    | 129 | Oranjestad     | ABW         | –        |      29034 |
| AFG  | Afghanistan | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 | 5976.00 |    NULL | Afganistan/Afqanestan | Islamic Emirate                              | Mohammad Omar            |       1 | AF    |   1 | Kabul          | AFG         | Kabol    |    1780000 |
| AFG  | Afghanistan | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 | 5976.00 |    NULL | Afganistan/Afqanestan | Islamic Emirate                              | Mohammad Omar            |       1 | AF    |   2 | Qandahar       | AFG         | Qandahar |     237500 |
| AFG  | Afghanistan | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 | 5976.00 |    NULL | Afganistan/Afqanestan | Islamic Emirate                              | Mohammad Omar            |       1 | AF    |   3 | Herat          | AFG         | Herat    |     186800 |
| AFG  | Afghanistan | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 | 5976.00 |    NULL | Afganistan/Afqanestan | Islamic Emirate                              | Mohammad Omar            |       1 | AF    |   4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
| AGO  | Angola      | Africa        | Central Africa            |  1246700.00 |      1975 |   12878000 |           38.3 | 6648.00 | 7984.00 | Angola                | Republic                                     | José Eduardo dos Santos  |      56 | AO    |  56 | Luanda         | AGO         | Luanda   |    2022000 |
| AGO  | Angola      | Africa        | Central Africa            |  1246700.00 |      1975 |   12878000 |           38.3 | 6648.00 | 7984.00 | Angola                | Republic                                     | José Eduardo dos Santos  |      56 | AO    |  57 | Huambo         | AGO         | Huambo   |     163100 |
| AGO  | Angola      | Africa        | Central Africa            |  1246700.00 |      1975 |   12878000 |           38.3 | 6648.00 | 7984.00 | Angola                | Republic                                     | José Eduardo dos Santos  |      56 | AO    |  58 | Lobito         | AGO         | Benguela |     130000 |
| AGO  | Angola      | Africa        | Central Africa            |  1246700.00 |      1975 |   12878000 |           38.3 | 6648.00 | 7984.00 | Angola                | Republic                                     | José Eduardo dos Santos  |      56 | AO    |  59 | Benguela       | AGO         | Benguela |     128300 |
| AGO  | Angola      | Africa        | Central Africa            |  1246700.00 |      1975 |   12878000 |           38.3 | 6648.00 | 7984.00 | Angola                | Republic                                     | José Eduardo dos Santos  |      56 | AO    |  60 | Namibe         | AGO         | Namibe   |     118200 |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+-----+----------------+-------------+----------+------------+
10 rows in set (0.00 sec)

SQL講座 INNER JOINを使った表の結合