MySql 使用exists查询

    select a.id,a.reviewer,a.order_id,a.audit_conclusion
    from `heatedloan_credit`.customer_credit_audit a,
    (select max(created_at) as created_at,order_id,reviewer from
    `heatedloan_credit`.customer_credit_audit where audit_status=6 and
    status=0 and exists
    (
    select realname from (
    select realname from heatedloan_manage.cms_security_user where id in (select user_id from
    heatedloan_manage.cms_security_user_role where role_id =18)
    ) as tmp
    )
    GROUP BY order_id order by null) b
    where a.order_id=b.order_id and a.created_at=b.created_at
    and exists  (select order_id from customer_credit_audit  e where  audit_status=7 and status=1)

上面的sql有exists和无exists语句查询结果一样，结果没有改变不是想要的结果。也就是说exists此时要不查询全部，要不没有数据。
将sql改为下面语句就ok了。

    select a.id,a.reviewer,a.order_id,a.audit_conclusion
    from `heatedloan_credit`.customer_credit_audit a,
    (select max(created_at) as created_at,order_id,reviewer from
    `heatedloan_credit`.customer_credit_audit where audit_status=6 and
    status=0 and exists
    (
    select realname from (
    select realname from heatedloan_manage.cms_security_user where id in (select user_id from
    heatedloan_manage.cms_security_user_role where role_id =18)
    ) as tmp
    )
    GROUP BY order_id order by null) b
    where a.order_id=b.order_id and a.created_at=b.created_at
    and exists  (select order_id from customer_credit_audit  e where a.order_id= e.order_id and audit_status=7 and status=1)

相比之下，加了a.order_id= e.order_id 语句。

原理：
1、exists的返回结果是bool型，只有true或者false；内查询中的id，必须为外查询的id。
2、用in实现也一样，不过效率低一些。（此处sql就是因为in耗时达到2s才进行优化，用exists耗时小于1s）
3、exists的效率比in查询要高，因为IN不走索引，但要看实际情况具体使用，IN适合于外表数据量大而内表数据小的情况；exists适合于外表小而内表大的情况。
4、exists与not exists是相对应的。