I have a query that I want to optimize. I want to try with 'EXISTS' command, instead of 'IN' command, but whenever I try to implement 'EXISTS', it returns more rows than it should.
Query with 'IN':
SELECT count(*)
FROM echosign_devbox.participation as p
left join echosign_devbox.agreement as a
on p.agreement_id=a.agreement_id
where a.status not in ('SIGNED', 'ABANDONED', 'EXPIRED', 'ABANDONED_BEFORE_SEND');
Does anyone know how should I change, just to be with 'EXISTS' command? Thanks!
SELECT count(*)
FROM echosign_devbox.participation as p
WHERE NOT EXISTS ( SELECT 1
FROM echosign_devbox.agreement as a
WHERE p.agreement_id = a.agreement_id
AND a.status IN ('SIGNED', 'ABANDONED', 'EXPIRED', 'ABANDONED_BEFORE_SEND' ) );
a
needs INDEX(agreement_id, status)
Would this give the 'correct' answer?
SELECT COUNT(*)
FROM echosign_devbox.agreement
WHERE status NOT IN ('SIGNED', 'ABANDONED', 'EXPIRED', 'ABANDONED_BEFORE_SEND')
What is the relationship between the tables? 1:1? 1:many?