SQL中on和where的区别及分析总结
⼀、基础
连接操作
当我们在数据库中进⾏查询操作时常会通过连接两张或者多张表。下⾯以连接两表为例叙述。
数据库在通过连接两张表来返回我们需要的记录时，常常是根据过滤条件，连接两张表，⽣成⼀个临时表呈现给⽤户。
连接操作分为inner join、left outer join和right outer join
在多表查询过程中，使⽤on、where来规定过滤的条件。
join的过程理解：先对两表做笛卡尔积，on后⾯的条件对笛卡尔积结果进⾏过滤并且⽣成⼀张临时表。如果没有where则直接返回该临时表作为结果。如果有where则会根据where设置的过滤条件，对前⾯得到的临时表进⾏进⼀步过滤，再求得查询结果。
join的⽐较：
内连接（inner join），只有两表存在匹配才会返回对应的⾏。笛卡尔积中没有匹配的⾏会被删去。
左外连接（left join），始终会保留左边表的所有⾏，若右边表不存在对应匹配的位置填null
⼆、实例测试
建⽴两张表以⽤于测试，分别为学⽣表student和学⽣选课表 SC（sno学号、Cno课程号）
现在希望连接两表但是，过滤掉学号Sno为2015122的学⽣的记录。分别对内连接和外连接中使⽤on 或 where过滤的结果进⾏⽐较
2.1. test inner join：
1.⽤on
SELECT*from student stu INNER JOIN sc on stu.sno=sc.Sno
AND stu.sno!=201215122AND sc.Sno!=201215122;
2.⽤on连接，⽤where过滤
SELECT*from student stu INNER JOIN sc on stu.sno=sc.Sno
WHERE stu.sno!=201215122AND sc.Sno!=201215122;
这两句运⾏⼏结果均为：
由此可见：在内连接中使⽤on和where可以实现相同的过滤效果。
2.2 test left (outer) join:
1.⽤on左连接并过滤
SELECT*FROM student stu LEFT JOIN sc on stu.sno=sc.sno AND stu.sno!=201215122AND sc.sno!=201215122;
结果：出现了sno=201215122的记录，是我们不希望看到的
2.⽤on连接，⽤where设置过滤条件
SELECT*FROM student stu LEFT JOIN sc on stu.sno=sc.sno
多表left joinWHERE stu.sno!=201215122AND sc.sno!=201215122;
三、思考与分析
on 和where都可以⽤于过滤，其中on ⽐ where先执⾏
对于inner join.
使⽤on或where来过滤没有区别，这是因为inner join不存在left join的特性，前者直接⽤求笛卡尔积再利⽤on的过滤条件⽣成临时表。后者在on得到的通过学号匹配的临时表后，再⽤when进⾏进⼀步过滤。
对于left join.
使⽤on: 由于left join特殊性，on的过滤条件对左边的表（驱动表）是不起作⽤的，因为⽆论如何左边
的悬浮元组都会保留，on的条件只对右边的表起作⽤。从结果可以看到sno=201215122的Sno(1)、Cno、Grade都被过滤掉了。因为是left join 所以都填null。尽管如此还是出现了该条记录，这是我们不希望看到的。
使⽤where: on⾸先通过学号连接两表，得到⼀张临时的表，接着where⼦句会对这个临时的表进⼀步过滤，因此最终结果不会出现
sno=201215122的记录。
sum: on 后⾯跟的是⽣成临时表的条件，⽽where后⾯跟的是对临时表进⾏进⼀步过滤的条件！
四、one more think…
结合课程学习和查资料过程中的启发，给出⼀个可能的优化
SELECT*from(SELECT*from student where student.sno!=201215122) stu
inner join(SELECT*from sc WHERE sc.sno!=201215122) sc
on stu.sno=sc.sno
在根据两表学号连接之前，先利⽤两个⼦查询将两表中需剔除的记录剔除，得到的表命名为stu、sc。可以减⼩表的规模。接着再将stu和sc做笛卡尔积。
tips：在做连接之前设法减⼩表的规模，在数据量很庞⼤时可以节省很多运算时间，提⾼查询效率。
最近在学习数据库，写博客记录学习过程中遇到的⼀些疑惑和解决后的总结思考。因为现在还是学⽣，知识体系可能还不完善，如果有什么错漏欢迎及时指出~
如果有补充的也希望各位⼤佬评论补充，这样帮助更多有疑惑的同学。当然啦 如果⽂章帮到你，希望能求个⼀键三连哈哈。谢谢~

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。