Thanks for bringing this to our attention.
I think this is indeed returning incorrect data. I passed it on up the line.
I would recommend using the syntax below as it returns correct data and, IMO, the readability is better, i.e., it is easier to tell from the SELECT statement what the results will be.
SELECT tbl1.*;
FROM tbl1;
WHERE tbl1.userid NOT in ;
(select tbl2.userid FROM tbl2)
I'm not sure what expected behavior with your SELECT is here, but the presence of the index should not make any difference in the resultset. I suspect the HAVING behavior is correct, and this also happens when not using an index on tbl2.userid.
Thanks again!
Jim
Jim Saunders
Microsoft
This posting is provided “AS IS”, with no warranties, and confers no rights.