关于not in的优化的问题

 SELECT
                <include refid="contractInList" />
            FROM
                cm_basic_info c
            LEFT JOIN (
                SELECT
                    *
                FROM
                    cm_basic_parties
                WHERE
                    flg_accounting = 1
                AND flg_own = 2
            ) p ON c.contract_id = p.contract_id
            LEFT JOIN cm_sign s ON c.contract_id = s.cm_id
            LEFT JOIN (
                SELECT
                    *
                FROM
                    cm_archive
                WHERE
                    biz_status = '02'
            ) arc ON c.contract_id = arc.cm_id
            WHERE 
                    EXISTS(
                        SELECT
                            contract_id
                        FROM
                            (
                                    SELECT
                                        creater_date AS create_date,
                                        contract_id
                                    FROM
                                        cm_turn
                                    WHERE
                                        turn_toUser =   #{authPersonal}
                                        AND STATUS = '0'
                                        AND biz_status = '02' ##先显示数据为别人移交过来
                            ) xi
                                where  xi.contract_id = c.contract_id
                    )
            and  ##排除掉别人移交过来后被自己完整移交出去的
            c.contract_id   not in (
                    SELECT
                        d.contract_id
                    FROM
                        (
                            SELECT
                                creater_date AS create_date,
                                contract_id
                            FROM
                                cm_turn
                            WHERE
                                turn_toUser =   #{authPersonal}
                            AND STATUS = '0'
                            AND biz_status = '02' ##别人移交过来的所有数据
                        ) d
                    WHERE
                        d.contract_id IN (
                            SELECT
                                contract_id
                            FROM
                                (
                                    SELECT
                                        creater_date AS create_date,
                                        contract_id
                                    FROM
                                        cm_turn
                                    WHERE
                                        turn_user =   #{authPersonal}
                                        AND STATUS = '0'
                                        AND biz_status in('01', '02' )
                     ##移交给他人的所有成功数据

                                ) b
                            WHERE
                                b.create_date > d.create_date
                        )
                )

这里想要把not in替换掉，但是不能使用not exists，否则返回的结果不对，求教大神该怎么改

使用with as试一试

  主表.haha not in (select id from A a where a.name=主表.name)
 exists (select 0 from A a where a.name=主表.name and id=主表.haha)
 这两行应该是天然可替换的啊