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 | 菜鸟教程 | https://www.runoob.com/ | 100 | | 3 | 菜鸟教程 | https://www.runoob.com/ | 220 | | 3 | 菜鸟教程 | https://www.runoob.com/ | 201 | | 4 | 微博 | https://weibo.com/ | null | | 5 | facebook | https://www.facebook.com/ | 205 | | 5 | facebook | https://www.facebook.com/ | 545 | | 6 | 百度 | https://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 | 菜鸟教程 | https://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 | 菜鸟教程 | https://www.runoob.com/ | 220 | | 5 | facebook | https://www.facebook.com/ | 545 | | 3 | 菜鸟教程 | https://www.runoob.com/ | 201 | +------+----------+---------------------------+-------+ 8 rows in set (0.00 sec)