借用菜鸟教程的两个表总结
下面是选自 “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 |
| 7 | stackoverflow| http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
下面是 “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 |
+-----+---------+-------+------------+
一、left join
LEFT JOIN 会返回左表中所有的行,即使右表中没有匹配,如果右表中没有匹配,则结果为 NULL。
以下实例中我们把 Websites 作为左表,access_log 作为右表
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
将会输出以下结果:
+-----------------+---------+-------------+
| name | count | date |
+-----------------+---------+-------------+
| Facebook | 545 | 2016-05-16 |
| Google | 230 | 2016-05-14 |
| 菜鸟教程 | 220 | 2016-05-15 |
| Facebook | 205 | 2016-05-14 |
| 菜鸟教程 | 201 | 2016-05-17 |
| 菜鸟教程 | 100 | 2016-05-13 |
| Google | 45 | 2016-05-10 |
| 微博 | 13 | 2016-05-15 |
| 淘宝 | 10 | 2016-05-14 |
| stackoverflow | NULL | NULL |
+-----------------+---------+-------------+
总结:
- 如果是select * 查询,则会返回两个表的所有列,以左表为主
- 如果两个表中存在相同的列名,我们最好指定取那个表的
比如: select id, a.name, b.age from tab1 as a left join tab2 as b on ...
as 可以省略不写
- 当两个表数据都很大是,我们用left join进行查询速度就会明显感觉到有点慢了,这是最好的解决办法就是尽量给每个表都加上where条件去过滤一下数据
比如: select a.id, a.name, b.name, a.age from (select id, name, age from tab1
where age < 20)a left join (select id, age from tab2 where age < 18)b
on a.id = b.id
- 当我们要同时查多个表时,会从左边先查出前两个表作为主表,在left join下一个表,依次类推