自己对mysql中的Join的理解

注： 本文是我自己的学习思路。自己的理解还很浅，而且很有可能是错的。文章只是辅助自己理解。若有看官阅读了，请指出我的错误，但请不要侮辱我。

cityjoin表：
+----+-----------+-------------+-----------+------------+
| id | Name      | CountryCode | District  | Population |
+----+-----------+-------------+-----------+------------+
|  1 | Shanghai  | CHN         | Shanghai  |    9696300 |
|  2 | Peking    | CHN         | Peking    |    7472000 |
|  3 | Chongqing | CHN         | Chongqing |    6351600 |
|  4 | Tianjin   | CHN         | Tianjin   |    5286800 |
|  5 | Wuhan     | CHN         | Hubei     |    4344600 |
+----+-----------+-------------+-----------+------------+
 
 countryjoin表：
 +------+-------------+---------------+-------------+------------+
| Code | Name        | Continent     | SurfaceArea | Population |
+------+-------------+---------------+-------------+------------+
| ABW  | Aruba       | North America |      193.00 |     103000 |
| AFG  | Afghanistan | Asia          |   652090.00 |   22720000 |
| AGO  | Angola      | Africa        |  1246700.00 |   12878000 |
| AIA  | Anguilla    | North America |       96.00 |       8000 |
| ALB  | Albania     | Europe        |    28748.00 |    3401200 |
| CHN  | China       | Asia          |  9600000.00 |  130000000 |
+------+-------------+---------------+-------------+------------+
 
 1. Join
 select cityjoin.id, cityjoin.Name, countryjoin.Name c_Name from cityjoin left join countryjoin on cityjoin.Name=cityjoin.District and countryjoin.Code=cityjoin.CountryCode;
 我们要从 cityjoin 表要选出的记录（不过只显示id和Name）要满足的条件是
 cityjoin.Name=cityjoin.District
 AND
 countryjoin.Code=cityjoin.CountryCode;
 怎么理解？就是说我们把左右两表作了连接，要显示的就是city表中的直辖市，并且，这个直辖市所在的国家要在countryjoin表中对应起来，最后把完全满足于这个条件的左右表需要的记录返回过来。
 
 返回的记录为：
 +----+-----------+--------+
| id | Name      | c_Name |
+----+-----------+--------+
|  1 | Shanghai  | China  |
|  2 | Peking    | China  |
|  3 | Chongqing | China  |
|  4 | Tianjin   | China  |
+----+-----------+--------+

上述是最正确的情况，也满足了需求。现在来破坏条件。
首先试着在cityjoin加上一条数据，满足cityjoin.Name=cityjoin.District，不过让这个直辖市所在的国家在country表中找不到。
INSERT INTO cityjoin VALUES('SaltLake', 'USA', 'SaltLake', 500000)；
此时的cityjoin表：

+----+-----------+-------------+-----------+------------+
| id | Name      | CountryCode | District  | Population |
+----+-----------+-------------+-----------+------------+
|  1 | Shanghai  | CHN         | Shanghai  |    9696300 |
|  2 | Peking    | CHN         | Peking    |    7472000 |
|  3 | Chongqing | CHN         | Chongqing |    6351600 |
|  4 | Tianjin   | CHN         | Tianjin   |    5286800 |
|  5 | Wuhan     | CHN         | Hubei     |    4344600 |
|  6 | SaltLake  | USA         | SaltLake  |     500000 |
+----+-----------+-------------+-----------+------------+


然后重新执行上面的select语句，结果仍旧一样。
+----+-----------+--------+
| id | Name      | c_Name |
+----+-----------+--------+
|  1 | Shanghai  | China  |
|  2 | Peking    | China  |
|  3 | Chongqing | China  |
|  4 | Tianjin   | China  |
+----+-----------+--------+

新加入的SaltLake，满足了直辖市的条件，不过对应的国家在country那边是找不到的。所以不予显示： 考虑我们要列拥有id,Name,c_Name3个列的表出来，下面全是直辖市。不过如果我们将SaltLake的东西要显示出来，那么c_Name这里该显示什么东西？没错，就是Null。显然join不和Null打交道。换而言之，我们用join连接时，左右表中任意可能出现Null的记录，都不会被返回。那么如果我想显示呢？如果想要显示没有对应country的直辖市，left join就派上用场了。

2. Left/Right Join
依然是上面加入SaltLake的表，查询语句中join换为left join.结果如下：
+----+-----------+--------+
| id | Name      | c_Name |
+----+-----------+--------+
|  1 | Shanghai  | China  |
|  2 | Peking    | China  |
|  3 | Chongqing | China  |
|  4 | Tianjin   | China  |
|  5 | Wuhan     | NULL   |
|  6 | SaltLake  | NULL   |
+----+-----------+--------+

恩？按照之前的设想应该把直辖市SaltLake加入进去，在c_Name显示NULL才对。怎么Wuhan也加进来了？

来看看LEFT JOIN的解释：
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

这下清楚了，之前的理解是有误的。我们的join table on 后面的条件是一个整体，如果分开来看，那就大错特错了。

再试试right join
+------+-----------+-------------+
| id   | Name      | c_Name      |
+------+-----------+-------------+
|    1 | Shanghai  | China       |
|    2 | Peking    | China       |
|    3 | Chongqing | China       |
|    4 | Tianjin   | China       |
| NULL | NULL      | Aruba       |
| NULL | NULL      | Afghanistan |
| NULL | NULL      | Angola      |
| NULL | NULL      | Anguilla    |
| NULL | NULL      | Albania     |
+------+-----------+-------------+

我们可以总结出一句话: join是精确筛选，而left/right join则是在精确筛选的基础之上，将左/右 表的记录再单独塞进去，然后在 右/左表的列名下面留NULL。

我们可以用where字句把left join的结果集进行逆向筛选：
 select cityjoin.id, cityjoin.Name, countryjoin.Name c_Name
 from cityjoin
 left join countryjoin
 on cityjoin.Name=cityjoin.District and countryjoin.Code=cityjoin.CountryCode
 where cityjoin.Name=cityjoin.District and countryjoin.Code=cityjoin.CountryCode;
 
结果如下：
+----+-----------+--------+
| id | Name      | c_Name |
+----+-----------+--------+
|  1 | Shanghai  | China  |
|  2 | Peking    | China  |
|  3 | Chongqing | China  |
|  4 | Tianjin   | China  |
+----+-----------+--------+