SQL中on和where 的区别

在数据库中,很多条件下on和where的作用形式傻傻分不清。

  1. 在整个语句的执行过程中,先执行on,再执行where。

  2. on条件是在生成临时表时使用的条件,而where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

  3. 不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。

下面进行举例说明
测试数据
table:成绩表
sid:学生编号;cid:课程编号;score,课程分数
在这里插入图片描述
1、查询同时存在“01”课程和“02”课程的分数的信息。
2、查询“01”课程和“02”课程的分数的信息,“02”课程分数不存在可以为null。
sql1

SELECT * FROM sc a INNER JOIN sc b ON a.Sid = b.Sid
AND a.Cid = 01 AND b.Cid = 02;

很简单,结果就不展示了
sql2

SELECT * FROM sc a LEFT JOIN sc b ON a.Cid = 01
AND a.Sid = b.Sid AND b.Cid = 02;

结果为

+------+------+-------+------+------+-------+
| Sid  | Cid  | score | Sid  | Cid  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 01   | 02   |  90.0 | NULL | NULL |  NULL |
| 01   | 03   |  99.0 | NULL | NULL |  NULL |
| 02   | 02   |  60.0 | NULL | NULL |  NULL |
| 02   | 03   |  80.0 | NULL | NULL |  NULL |
| 03   | 02   |  80.0 | NULL | NULL |  NULL |
| 03   | 03   |  80.0 | NULL | NULL |  NULL |
| 04   | 02   |  30.0 | NULL | NULL |  NULL |
| 04   | 03   |  20.0 | NULL | NULL |  NULL |
| 05   | 02   |  87.0 | NULL | NULL |  NULL |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
| 06   | 03   |  34.0 | NULL | NULL |  NULL |
| 07   | 02   |  89.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+
17 rows in set (0.00 sec)

但如果把表的连接条件 on b.Sid =02改为 where b.Sid =02,则结果为:

+------+------+-------+------+------+-------+
| Sid  | Cid  | score | Sid  | Cid  | score |
+------+------+-------+------+------+-------+
| 02   | 01   |  70.0 | 02   | 01   |  70.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 02   | 01   |  70.0 | 02   | 03   |  80.0 |
+------+------+-------+------+------+-------+
3 rows in set (0.00 sec)

所以在要求有null的情况下输出要用外连接,并且在表的连接条件时要注意on 和where的区别。

发布了46 篇原创文章 · 获赞 70 · 访问量 7万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 1024 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览