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 | 菜鸟教程 | 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)