SQL Exists command

Question

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!

*but whenever I try to implement 'EXISTS', it returns more rows than it should.* Show your attempt, even incorrect one. PS. https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html — Akina, Mar 21 '22 at 13:30
Do you get the "correct" answer if you remove the word "LEFT"? — Rick James, Mar 23 '22 at 05:16

score 0 · Accepted Answer · answered Mar 23 '22 at 05:22

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?

SQL Exists command

1 Answers1