将NOT IN替换为JOIN

我需要用join替换NOT IN。我该怎么做，因为这个查询的结果不同。

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
)

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

你能试试这种吗

相关内容

最新更新

热门标签：