MySQL IN和EXISTS

IN：

select
	c.*,
	sum(d.money) AS sumMoney,
	REPLACE(GROUP_CONCAT(d.project_name), ',', '/') AS projectName
from test c
left join test_detail d ON c.id = d.fid
where 1=1		
	and c.id IN (
		SELECT e.fid FROM test_detail e WHERE e.id = #{id}
	)

EXISTS：

select
	c.*,
	sum(d.money) AS sumMoney,
	REPLACE(GROUP_CONCAT(d.project_name), ',', '/') AS projectName
from test c
left join test_detail d ON c.id = d.fid
where 1=1	
	and  EXISTS (
		SELECT e.fid FROM test_detail e WHERE e.auditor_id = #{id} AND c.id = e.fid
	)

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。 
in 与 =的区别 
select name from student where name in ('zhang','wang','li','zhao'); 
与 
select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 
的结果是相同的