MSSQL query with join and where clause
My only real comment to make is that the subquery is irrelevant. You can achieve the same result without it. 



When specifying your column aliases in the outer query. There is no need for you to use AS picid as the column is already called picid.



You could also use table aliases as well to shorten some of the statements. I personally prefer to see aliases than fully qualified table names. Assuming everyone sticks to a naming convention of course. My normal rule of thumb is that each capital letter of the table becomes the alias.



Also, as we can remove the subquery, we can change the WHERE clause so it is no longer filtering out the attachment name but instead the PK (WHERE a.Id IS NOT NULL).



For the columns being returned, can we make them more explicit? i.e. CandidateName instead of CName, etc.

Here is the modified version of your query using the changes above:

SELECT
     c.Name     AS CandidateName
    ,c.picid    AS PicId
    ,a.Name     AS AttachmentName
FROM Candidates c
LEFT JOIN AttachmentCandidate ac ON c.Id = ac.CandidateId
LEFT JOIN Attachments a ON ac.AttachmentId = a.Id
WHERE c.picid IS NOT NULL
OR a.Id IS NOT NULL




I'm not sure whether the formatting is just bad when you created your question or whether your SQL query is like that? If so, it's badly formatted and difficult to read. I try and break up each part of the query into separate segments so it's easy to read and maintain by others:

SELECT
     r.cname
    ,r.PicId
    ,r.attName
FROM
(
    SELECT
        Candidates.Name     AS cname
        ,Candidates.PicId   AS picid
        ,Attachments.Name   AS attname
    FROM Candidates
    LEFT JOIN AttachmentCandidate ON Candidates.Id = AttachmentCandidate.CandidateId
    LEFT JOIN Attachments ON AttachmentCandidate.AttachmentId = Attachments.Id
) AS r
WHERE (r.PicId IS NOT NULL OR r.attName IS NOT NULL)


As you requested an alternative, i'll give you one. Essentially it's pretty much the reverse of what you're doing. I'm gathering all of the candidates with attachments first and then I RIGHT JOIN back onto the candidates table and filter out the ones without a PicId.

SELECT
    c.Name      AS CandidateName
    ,c.PicId    AS PicId
    ,a.Name     AS AttachmentName
FROM AttachmentCandidate ac
INNER JOIN Attachments a ON a.Id = ac.AttachmentId
RIGHT JOIN Candidates c ON c.Id = ac.CandidateId
WHERE c.picid IS NOT NULL
OR a.Id IS NOT NULL