Check existence on other tables: performance advice This is my first post.
I have this data model:


I want fetch some profile, with checking if they have any page or user assigned or not. I have tried multiple queries and finally came to these tows:

Query 1:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

SELECT TOP ( 1 )
        p.[ProfileId] AS [ProfileId] ,
        p.[Username] AS [Username] ,
        p.[UsernameCleaned] AS [UsernameCleaned] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Users] u WITH ( NOLOCK )
                             WHERE  u.UserId = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsUser] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Pages] pg WITH ( NOLOCK )
                             WHERE  pg.[PageId] = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsPage]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token 


Query 2:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

DECLARE @ProfileId BIGINT= NULL ,
    @Username NVARCHAR(256)= NULL ,
    @UsernameCleaned NVARCHAR(256)= NULL ,
    @IsPage BIT= 0 ,
    @IsUser BIT= 0;

SELECT TOP ( 1 )
        @ProfileId = p.[ProfileId] ,
        @Username = p.[Username] ,
        @UsernameCleaned = p.[UsernameCleaned]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token;

IF @ProfileId IS NULL
    BEGIN
        RETURN
    END 

SELECT  @IsUser = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Users] u WITH ( NOLOCK )
                                       WHERE    u.UserId = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT  @IsPage = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Pages] pg WITH ( NOLOCK )
                                       WHERE    pg.[PageId] = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT TOP ( 1 )
        @ProfileId AS [ProfileId] ,
        @Username AS [Username] ,
        @UsernameCleaned AS [UsernameCleaned] ,
        @IsUser AS [IsUser] ,
        @IsPage AS [IsPage];


Which one would be better and would have a higher performance?

p.s.
I'm not an exact SQL-guy. So, it's hard for me to determine which query would have a better performance and executes faster -I mean as fast as possible. Any help/advise would be absolutely helpful. Thanks in advance.
I'd probably write something like the following:

SELECT TOP 1 -- Top command optional and may not actually help speed things up
  p.ProfileId -- No need to alias to the same thing
  , p.Username
  , p.UsernameCleaned
  , IsUser = CAST(CASE WHEN u.UserId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
  , IsPage = CAST(CASE WHEN pg.PageId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM dbo.Profiles p
  LEFT JOIN dbo.Users u ON u.UserId = p.ProfileId -- Left join here instead of exists above
  LEFT JOIN dbo.Pages pg ON pg.PageId = p.ProfileId
WHERE
  p.UsernameCleaned = @Token;


If you notice I basically just traded EXISTS for LEFT JOIN. This should give you reasonable performance and be fairly clear for people who review your code. Of course the standard disclaimer applies for any performance question.


  You should profile this on a representative workload to determine what is fastest, and indexes are your friend.


In general though you want to avoid subqueries in the SELECT portion of your query because they will probably be run once for each row in the result set. Here that isn't a big deal but if you have more results then the difference can be dramatic. 

Try at all costs to avoid the NOLOCK query hint. It looks nice on paper and can give you an apparent boost, but nothing is free. For more detailed information on why this probably isn't a good idea I'd advise you read this blog post by Aaron Bertrand.

Also of note rewriting your query made it clear that a profile can be both a user and a page. I don't know your use cases but that feels wrong to me especially since this query seems to be designed to tell the difference. If that is true then you might need to rework your data model.

Finally as a personal preference I like to have my aliases on the left if the column is computed/derived like IsUser above. My reasoning goes the alias can get lost in the confusion of the calculations so it is best to put it first. Of course you should go with what you and your coworkers find is most legible.