SQL中on和where的区别

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)