注: 本文是我自己的学习思路。自己的理解还很浅,而且很有可能是错的。文章只是辅助自己理解。若有看官阅读了,请指出我的错误,但请不要侮辱我。
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 |
+----+-----------+--------+
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 |
+----+-----------+--------+