我将建议一个相关的子查询：

SELECT u.*,
       (SELECT COUNT(*) FROM dbo.UserGroups ug WHERE ug.UserId = u.UserId) as teams
FROM dbo.Users u 
WHERE u.UserId = 27 ;

为什么我更喜欢这种方法？
它使查询更简单。您不必列出列表中的所有列 GROUP BY .
因为没有外部的 GROUP BY ，这可能是大多数数据库上性能最高的（假设 UserGroups(UserId) ).
注意使用表别名来简化查询。

可能是因为你使用了隐式的内部连接。。。使用显式左联接。
试试这个：

SELECT dbo.Users.UserId AS 'userId', 
       dbo.Users.UserName AS 'firstName', 
       dbo.Users.Surname AS 'surname', 
       dbo.Users.Photograph AS 'photograph', 
       dbo.Users.JobTitle AS 'jobTitle', 
       dbo.Users.EmailAddress AS 'emailAddress', 
       dbo.Users.Inactive AS 'inactive', 
       COALESCE(COUNT(dbo.UserGroups.userId), 0) AS 'teams'
 FROM dbo.Users left join
       dbo.UserGroups on dbo.UserGroups.UserId = dbo.Users.UserId
 WHERE dbo.Users.UserId = 27  
 GROUP BY dbo.Users.UserId, 
       dbo.Users.UserName, 
       dbo.Users.Surname, 
       dbo.Users.Photograph, 
       dbo.Users.JobTitle, 
       dbo.Users.EmailAddress, 
       dbo.Users.Inactive

你应该使用 left join 而不是隐式的 inner join :

SELECT dbo.Users.UserId AS 'userId', 
       dbo.Users.UserName AS 'firstName', 
       dbo.Users.Surname AS 'surname', 
       dbo.Users.Photograph AS 'photograph', 
       dbo.Users.JobTitle AS 'jobTitle', 
       dbo.Users.EmailAddress AS 'emailAddress', 
       dbo.Users.Inactive AS 'inactive', 
       COUNT(dbo.UserGroups.userId) AS 'teams'
 FROM dbo.Users
 LEFT JOIN dbo.UserGroups ON dbo.UserGroups.UserId = dbo.Users.UserId
 WHERE dbo.Users.UserId = 27 
 GROUP BY dbo.Users.UserId, 
       dbo.Users.UserName, 
       dbo.Users.Surname, 
       dbo.Users.Photograph, 
       dbo.Users.JobTitle, 
       dbo.Users.EmailAddress, 
       dbo.Users.Inactive