SQL自连接查询使用EXISTS替代

问题：需要使用两个字段去mysql查询一个字段

 

方式一：sql自连表，将一个字段拆开(速度最慢，新手易理解)

SELECT
	* 
FROM
	cmooc_course a 
WHERE
a.valid = 'Y' and  a.id in (
     select DISTINCT a.course_id  from  cmooc_course_belong_scope a inner join cmooc_course_belong_scope b on a.course_id = b.course_id
     and a.belong_type = '5' and b.belong_type = '6'  and  a.belong_code = '86480024' and b.belong_code = 'A' and a.valid = 'Y' and b.valid = 'Y')

 

方式二：使用exists

SELECT
	* 
FROM
	cmooc_course a 
WHERE
	a.valid = 'Y' 
	AND EXISTS ( SELECT 1 FROM cmooc_course_belong_scope c WHERE a.id = c.course_id AND c.belong_type = '5' AND c.belong_code = '86480024' ) 
	AND EXISTS ( SELECT 1 FROM cmooc_course_belong_scope c WHERE a.id = c.course_id AND c.belon