mysqlhibernateexistsnot-existsnotin

SQL Exists command


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!


Solution

  • 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?