Joining SQL Server to Subquery Performance Question

Question

I found that in some cases, a query such as

select 
   usertable.userid,
   (select top 1 name from nametable where userid = usertable.userid) as name 
from usertable 
where active = 1

takes longer to complete in SS2008R2 than equivalent connection request

select 
   usertable.userid,
   nametable.name 
from usertable 
left join nametable on nametable.userid = usertable.userid 
where usertable.active = 1

where both tables are indexed and have more than 100 thousand rows. It is interesting to note that inserting the top sentence into the original request forces it to execute along with the connection request:

select 
    top (select count(*) from usertable where active = 1) usertable.userid,
    (select top 1 name from nametable where userid = usertable.userid) as name 
from usertable 
where active = 1

Does anyone know why the original request works so badly?

Answer 1

Well, the queries are different - if the column is useridnot a primary key or has a unique constraint, then the second query can return more rows than the first.

, , userid / TOP 1 :

select 
   usertable.userid,
   (select name from nametable where userid = usertable.userid) as name 
from usertable 
where active = 1

Answer 2

, , , .

A JOIN . , .

Answer 3

sub select , , .

JOIN, .