SQL中on和where的区别
on 和 where 的区别主要在join中体现。
- inner join :无区别
- left join、right join:
on条件在生成临时表中使用,无论on后的条件是否为真,都会返回记录。
where是指在生成临时表之后,再对临时表进行过滤。
on:“id=4”的记录返回,且有“count = NULL”的记录
mysql> select w.id, w.name, w.url, a.count from Websites as w left join access_log as a on w.id = a.site_id and w.id != 4 and a.site_id != 4;
+----+----------+---------------------------+-------+
| id | name | url | count |
+----+----------+---------------------------+-------+
| 1 | Google | https://www.google.cm/ | 45 |
| 1 | Google | https://www.google.cm/ | 230 |
| 2 | 淘宝 | https://www.taobao.com/ | 10 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 100 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 220 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 201 |
| 4 | 微博 | http://weibo.com/ | NULL |
| 5 | Facebook | https://www.facebook.com/ | 205 |
| 5 | Facebook | https://www.facebook.com/ | 545 |
| 6 | 百度 | http://www.baidu.com | NULL |
+----+----------+---------------------------+-------+
10 rows in set (0.00 sec)
where:“id=4”的记录不返回,且没有“count=NULL”的记录
mysql> select w.id, w.name, w.url, a.count from Websites as w right join access_log as a on w.id = a.site_id where w.id != 4 and a.site_id != 4;
+------+----------+---------------------------+-------+
| id | name | url | count |
+------+----------+---------------------------+-------+
| 1 | Google | https://www.google.cm/ | 45 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 100 |
| 1 | Google | https://www.google.cm/ | 230 |
| 2 | 淘宝 | https://www.taobao.com/ | 10 |
| 5 | Facebook | https://www.facebook.com/ | 205 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 220 |
| 5 | Facebook | https://www.facebook.com/ | 545 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 201 |
+------+----------+---------------------------+-------+
8 rows in set (0.00 sec)