SELECT语句具有证明选择值-SQL Server的情况

Question

我想从用户表中选择USERTYPE，USERID，FIRSTNAME，LASTNAME，用户名。如果可以从照片表中获得Photourl，请选择。如果记录不存在（USERTYPE-管理员仅在照片表中具有记录），则我应该发送空格。

查询在下面。如果您想到更好的查询，请建议。

Select UserType, UserID, FirstName, LastName, UserName,
CASE 
    WHEN EXISTS(
        SELECT PhotoURL FROM Photos WHERE Photos.UserID = Users.UserID AND UserType = 'admin' AND Photos.PhotoNum = 1
    )
    THEN (
        SELECT PhotoURL FROM Photos WHERE Photos.UserID = Users.UserID AND UserType = 'admin' AND Photos.PhotoNum = 1
    ) 
    ELSE '' 
END AS PhotoURL
from Users

Answer 1

为什么不使用 left join

    Select UserType, UserID, FirstName, LastName, UserName, ISNULL(P.PhotoURL,'')
    from Users U left join Photos P
    ON U.UserID = P.UserID
    where 
    U.UserType = 'admin' and
    (P.PhotoNum is null OR P.PhotoNum = 1) -- No match in the photos table or if there is a match, PhotoNum should be 1

Answer 2

SELECT UserType, Users.UserID, FirstName, LastName, UserName, ISNULL(Photos.PhotoURL, '') as PhotoURL
FROM Users
LEFT JOIN Photos on Users.UserID = Photos.UserID 
    AND Photos.PhotoNum = 1
    AND Users.UserType = 'admin'