Options For Returning Distinct Values Across An Inner Join

I have two tables. Table A contains UserID, UserName Table B contains ID, FK_UserID, ClientName I need to return a list of distinct A.UserName where A.Username exists in table B an

Solution 1:

Your original query is:

SelectDistinct A.UserName
from A as A Inner Join
     B as B
     on A.UserID = B.FK_UserID;

This can be a problem, if there are many matches in b. Using in isn't quite the right solution. Instead, use exists:

select a.UserName
from a
whereexists (select1from b
              where b.fk_UserID = a.UserId
             )

Then, be sure that you have an index on b(fk_UserId).

This should do about 300 lookups in the index. That should be quite fast.

My advice for the person who told you to use the inner join: Write a special version for that person. For the many minutes or hours that it takes to run, let other people use the faster version using exists.