replace NOT IN with JOIN

February 28, 2022

I need a replacement NOT IN with join . How do I do this because the results are different with this query.

SELECT COUNT(*) as total FROM requests 
        WHERE 
            isApproved='1' AND 
            softDelete='0' AND 
            requestID NOT IN ( 
                SELECT 
                    DISTINCT requestID 
                FROM consulting 
                WHERE 
                    softDelete='0' AND 
                    isApproved='1' AND
            )

>Solution :

SELECT COUNT(*) as total FROM requests AS R
   LEFT JOIN
   (
      SELECT 
                DISTINCT requestID 
            FROM consulting 
            WHERE 
                softDelete='0' AND 
                isApproved='1' AND
   ) X ON R.requestID =X.requestID 
    WHERE R.isApproved='1' AND R.softDelete='0' 
       AND X.requestID  IS NULL

Could you please try something like this