why in operator is very slow in run time?

[Pernia]

Hello,

This sql query is very slow in runtime and takes a long time to return the result, because of the in operator inside query(because the second table includes more than 10000 records)
i think i have to change it by using Join commands to increase the speed in runtime.

but how do i rewrite the following command to turn the Join Command?


Select distinct Fit_R_id from Wels
where Fit_R_id is not null
and
id not in
(
select Wels_id from Fit
)


i tried this :

Select distinct Fit_R_id from Wels
left JOIN Fit ON wels.id = fit.wels_id
where
wels.fit_R_id is not null
GROUP BY wels.fit_R_id



but the result was not equal to the first query!
what is answer?
thanx

 

[vbaInet]

It's not only the subquery that's making it slow, but DISTINCT is slow too. Try something like this:

SELECT DISTINCT Fit_R_id
FROM Wels LEFT JOIN Fit ON Wels.id = Fit.Wels_id
WHERE Not Fit_R_id Is Null
AND Fit.Wels_id Is Null