where exists(select 1 from...)与where a.id=b.id的区别

测试表有student(id,name,psd),grade(stu_id,score)

SELECT * FROM student a,grade b WHERE a.`id`=b.`stu_id`

等价于

SELECT * 
FROM student a INNER JOIN grade b
ON a.`id`=b.`stu_id`

这个写法给出的结果是有重复值的

SELECT * 
FROM student a 
WHERE EXISTS(SELECT 1 FROM grade b WHERE a.id=b.`stu_id`)

这种写法是没有重复值的

等价于：

SELECT * 
FROM student a 
WHERE a.id IN (SELECT b.`stu_id` FROM grade b WHERE a.`id`=b.`stu_id)

即select * from a where a.id in(1,2,3,4,4)  ，等价于where a.id=1 and a.id=2 and a.id=3 and a.id=4 and a.id=4，因此也等价于in(1,2,3,4)

因此两者的区别就是是否存在重复值。