2表基础信息

其中websites，和access_log是一对多的关系，一个website站点，对应一到多条访问日志


mysql> select * from websites;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
|  7 | 百度          | https://www.baidu.com/    |     4 | CN      |
+----+---------------+---------------------------+-------+---------+
7 rows in set (0.00 sec)

mysql> select * from access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
|  10 |       6 |   111 | 2016-03-09 |
|  11 |      66 |   111 | 2016-03-09 |
+-----+---------+-------+------------+
11 rows in set (0.00 sec)

以website为主表，进行left join和 right join

left join

过程分析：

假设on 后添加为 1=1 则，表示一直为true，返回的临时表是，7*11 = 77条数据，（是两表数据条目的笛卡尔乘积）表a数据，每一条都会和表b数据的每一条进行组成，形成一条数据
但是笛卡尔积的过滤结果是，无意义的，两表之间有很多根本没有意义的条目两两组合成一条，两表有关联意义的点，就是website.id 和 access_log.site_id 两者组合，（表示一个站点的信息，和站点一天的日志）
主表是websites，且是left 表示，website的每一条都要匹配，可能会有一到多条，也可能没有，比如下面的百度，没有的，也展示在过滤结果中，join表的字段用null填充


mysql> select * from websites left join access_log on websites.id=access_log.site_id;
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name          | url                       | alexa | country | aid  | site_id | count | date       |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    1 |       1 |    45 | 2016-05-10 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    2 |       3 |   100 | 2016-05-13 |
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    3 |       1 |   230 | 2016-05-14 |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |    4 |       2 |    10 | 2016-05-14 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    5 |       5 |   205 | 2016-05-14 |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |    6 |       4 |    13 | 2016-05-15 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    7 |       3 |   220 | 2016-05-15 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    8 |       5 |   545 | 2016-05-16 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    9 |       3 |   201 | 2016-05-17 |
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |   10 |       6 |   111 | 2016-03-09 |
|  7 | 百度          | https://www.baidu.com/    |     4 | CN      | NULL |    NULL |  NULL | NULL       |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
11 rows in set (0.00 sec)

right join

以access_log 为基准，它的每一条数据，都在websites表中，找到一条对应的条目，进行连接
如果某条access_log数据，在website中找不到对应的条目，那么也进行展示，website部分，用null填充

mysql> select * from websites right join access_log on websites.id=access_log.site_id;
+------+---------------+---------------------------+-------+---------+-----+---------+-------+------------+
| id   | name          | url                       | alexa | country | aid | site_id | count | date       |
+------+---------------+---------------------------+-------+---------+-----+---------+-------+------------+
|    1 | Google        | https://www.google.cm/    |     1 | USA     |   1 |       1 |    45 | 2016-05-10 |
|    3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |   2 |       3 |   100 | 2016-05-13 |
|    1 | Google        | https://www.google.cm/    |     1 | USA     |   3 |       1 |   230 | 2016-05-14 |
|    2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |   4 |       2 |    10 | 2016-05-14 |
|    5 | Facebook      | https://www.facebook.com/ |     3 | USA     |   5 |       5 |   205 | 2016-05-14 |
|    4 | 微博          | http://weibo.com/         |    20 | CN      |   6 |       4 |    13 | 2016-05-15 |
|    3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |   7 |       3 |   220 | 2016-05-15 |
|    5 | Facebook      | https://www.facebook.com/ |     3 | USA     |   8 |       5 |   545 | 2016-05-16 |
|    3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |   9 |       3 |   201 | 2016-05-17 |
|    6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |  10 |       6 |   111 | 2016-03-09 |
| NULL | NULL          | NULL                      |  NULL | NULL    |  11 |      66 |   111 | 2016-03-09 |
+------+---------------+---------------------------+-------+---------+-----+---------+-------+------------+
11 rows in set (0.00 sec)

为access_log为主表，进行left/right join

left join

以access_log为主，为基准，它的每一条数据，都在websites中找到一条匹配的数据，进行展示，
如果某条access_log数据，在website中找不到，就以null填充website字段的部分
不就等价于以website为基准表，right join吗

mysql> select * from access_log left join websites on websites.id=access_log.site_id;
+-----+---------+-------+------------+------+---------------+---------------------------+-------+---------+
| aid | site_id | count | date       | id   | name          | url                       | alexa | country |
+-----+---------+-------+------------+------+---------------+---------------------------+-------+---------+
|   1 |       1 |    45 | 2016-05-10 |    1 | Google        | https://www.google.cm/    |     1 | USA     |
|   2 |       3 |   100 | 2016-05-13 |    3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|   3 |       1 |   230 | 2016-05-14 |    1 | Google        | https://www.google.cm/    |     1 | USA     |
|   4 |       2 |    10 | 2016-05-14 |    2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|   5 |       5 |   205 | 2016-05-14 |    5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|   6 |       4 |    13 | 2016-05-15 |    4 | 微博          | http://weibo.com/         |    20 | CN      |
|   7 |       3 |   220 | 2016-05-15 |    3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|   8 |       5 |   545 | 2016-05-16 |    5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|   9 |       3 |   201 | 2016-05-17 |    3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|  10 |       6 |   111 | 2016-03-09 |    6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
|  11 |      66 |   111 | 2016-03-09 | NULL | NULL          | NULL                      |  NULL | NULL    |
+-----+---------+-------+------------+------+---------------+---------------------------+-------+---------+
11 rows in set (0.00 sec)

right join

以access_log 为基准表，但是right join，
那就是以website为准，它的每一条数据都在access_log中找到匹配一条，进行联合，
如果找不到，也展示，access_log的字段部分以null填充 4.不就是，以websites为主表，left join access_log的结果吗

mysql> select * from access_log right join websites on websites.id=access_log.site_id;
+------+---------+-------+------------+----+---------------+---------------------------+-------+---------+
| aid  | site_id | count | date       | id | name          | url                       | alexa | country |
+------+---------+-------+------------+----+---------------+---------------------------+-------+---------+
|    1 |       1 |    45 | 2016-05-10 |  1 | Google        | https://www.google.cm/    |     1 | USA     |
|    2 |       3 |   100 | 2016-05-13 |  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|    3 |       1 |   230 | 2016-05-14 |  1 | Google        | https://www.google.cm/    |     1 | USA     |
|    4 |       2 |    10 | 2016-05-14 |  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|    5 |       5 |   205 | 2016-05-14 |  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|    6 |       4 |    13 | 2016-05-15 |  4 | 微博          | http://weibo.com/         |    20 | CN      |
|    7 |       3 |   220 | 2016-05-15 |  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|    8 |       5 |   545 | 2016-05-16 |  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|    9 |       3 |   201 | 2016-05-17 |  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|   10 |       6 |   111 | 2016-03-09 |  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
| NULL |    NULL |  NULL | NULL       |  7 | 百度          | https://www.baidu.com/    |     4 | CN      |
+------+---------+-------+------------+----+---------------+---------------------------+-------+---------+
11 rows in set (0.00 sec)

总结：

table1 left join table2 等价于 table2 right join table1
table1 right join tabl2 等价于 table2 left join table1

sql：左右联接查询

2表基础信息

以website为主表，进行left join和 right join

为access_log为主表，进行left/right join

总结：