WHATS WRONG WITH THE BELOW QUERY

You can understand the behavior by running the following SELECT statement

SELECT * FROM [dbo].testx WHERE Memberid IN ( 
SELECT Memberid FROM  testy (NOLOCK) GROUP BY [Member ID]
) 

We know that the subselect statement can reach values from outer tables, this is the case and as Visakh said it equals the Memberid column to itself in the WHERE clause

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials