join performance

I have some problems with understanding why this code return millions of rows when in group table 400000 rows. RID loop up and Index seek on t2 should return 1 row, they return all rows from group table.

Create table #group (IDperson uniqueidentifier, MGroup_Idx int, 
NGroup_Idx int)
create index i1 on #group (NGroup_Idx); 

-- insert some data 

SELECT * FROM #group t 
LEFT JOIN #group t2  ON t.NGroup_Idx = t2.NGroup_Idx 
AND t2.MGroup_Idx IS not NULL
WHERE ISNULL(t.MGroup_Idx, t2.MGroup_Idx) IS NOT NULL

This is what I believe is happening.

You're joining group to itself. Therefore, you're guaranteed to match all rows between t and t2 at least once (as I don't see anything in your where clause to limit that). Can there be multiple rows on group that have the exact same NGroup_Idx? If so, each t row would match multiple t2 rows.

If this is not the case, please update your question with some sample data that reproduces the unwanted behavior.

StackExchange Database Administrators Q#200532, answer score: 6