通过内部联接返回不同值的选项 [英] options for returning distinct values across an inner join

问题描述

我有两个桌子.

表A包含用户ID，用户名

Table A contains UserID, UserName

表B包含ID，FK_UserID，ClientName

Table B contains ID, FK_UserID, ClientName

我需要返回一个不同的A.UserName列表，其中表B中存在A.Username，并且至少附加了一个ClientName，但是在我的查询中，仅查看不同的B.ClientName.

I need to return a list of distinct A.UserName where A.Username exists in table B and has at least one ClientName attached to them, but in my query, only look at distinct B.ClientName.

我的想法是:

Select Distinct A.UserName from A as A
Inner Join B as B
on A.UserID = B.FK_UserID

但这只是表A上的区别

我的下一个想法是:

Select Distinct Username from A
where UserID In 
(
Select FK_UserID, distinct ClientName from B)

有人告诉我，有一种方法可以对联接中的两个表进行区分，因为如果表A为300行，而表B为30亿行，那么我的原始查询将花费一些时间.

I was told that there is a way to do a distinct on both tables in the join, because if table A is 300 rows, and table B is 3 BILLION rows, my original query is going to take awhile.

该人特别希望我使用内部联接...

The person specifically wants me to use an inner join...

推荐答案

您的原始查询是:

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

如果b中有许多匹配项，则可能是一个问题.使用in并不是正确的解决方案.而是使用exists:

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
where exists (select 1
              from b
              where b.fk_UserID = a.UserId
             )

然后，请确保您在b(fk_UserId)上有一个索引.

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

这应该在索引中执行约300次查找.那应该很快.

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

我对告诉您使用内部联接的人的建议:为该人编写一个特殊版本.在运行过程中需要花费几分钟或几小时的时间，让其他人通过exists使用更快的版本.

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.

这篇关于通过内部联接返回不同值的选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！