サンプルデータベース world を使った習作。
普通にselect count() from A, B;とかやっちゃうと、
ABぶんだけ数えてしまう。
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)
