How to only run left join when the variable is not null?

Question

I'm trying to simplify my stored procedure and I have one that is only using left join based on the user id that is passed in. If the user id is null, don't do left join, but if it is null, left join it with another table. How should I re-write it ? Thank you

CREATE OR ALTER PROCEDURE [dbo].[GetRoom] 
    @RoomId UNIQUEIDENTIFIER NULL,
    @UserId UNIQUEIDENTIFIER
AS
BEGIN   
    IF (@UserId IS NULL)
    BEGIN   
        SELECT r.Id, r.DisplayName            
        FROM Room r
        INNER JOIN Game g ON r.GameId = g.Id
        INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id                         
        WHERE r.Id = @RoomId
    END
    ELSE
        SELECT 
            r.Id, r.DisplayName, 
            ru.Description, -- this is coming from the left join table
            ru.Tags         -- from left join table   
        FROM Room r
        INNER JOIN Game g ON r.GameId = g.Id
        INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id          
        LEFT JOIN RoomUser ru ON ru.RoomId = r.Id
        WHERE r.Id = @RoomId AND ru.UserId = @UserId
END

With `ru.UserId = @UserId` at the `WHERE` clause, you are effectively doing a `INNER JOIN` to `RoomUser`, not a `LEFT JOIN` — Squirrel, Dec 17 '21 at 05:45
Does this answer your question? [Why is my t-sql left join not working?](https://stackoverflow.com/questions/40093809/why-is-my-t-sql-left-join-not-working) — Charlieface, Dec 17 '21 at 11:39
There is simply NO REASON to provide a default value of NULL for your first parameter. That is just nonsense and your query will return nothing in that situation. Don't encourage lazy coding. — SMor, Dec 17 '21 at 12:22

score 1 · Accepted Answer · answered Dec 17 '21 at 05:56

Currently your stored procedure return different number columns depending on the @UserId.

You may remove the IF condition, and combined it as one single query by moving ru.UserId = @UserId to ON condition. This will make it a true LEFT JOIN to table RoomUser.

This will means it always return 4 columns as result

    SELECT r.Id, 
           r.DisplayName, 
           ru.Description,
           ru.Tags        
    FROM   Room r
           INNER JOIN Game g               ON r.GameId     = g.Id
           INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id          
           LEFT JOIN RoomUser ru           ON ru.RoomId    = r.Id
                                          AND ru.UserId    = @UserId
    WHERE  r.Id = @RoomId

It works. Awesome. Thank you very much – Hoang Minh Dec 17 '21 at 07:47 — Hoang Minh, Dec 17 '21 at 07:47

score 1 · Answer 2 · answered Dec 17 '21 at 06:00

1

Try something like below,

        SELECT 
            r.Id, r.DisplayName, 
            ru.Description, -- this is coming from the left join table
            ru.Tags         -- from left join table   
        FROM Room r
        INNER JOIN Game g ON r.GameId = g.Id
        INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id          
        LEFT JOIN RoomUser ru ON ru.RoomId = r.Id  
                              AND r.Id IS NOT NULL 
                              AND r.Id = @RoomId 
                              AND ru.UserId IS NOT NULL
                              AND ru.UserId = @UserId

answered Dec 17 '21 at 06:00

Jaydip Jadhav

12,179
6
24
40

1

Those `IS NOT NULL` checks are redundant, the `=` will do that anyway. The key is moving the conditions to the `ON` instead of `WHERE` – Charlieface Dec 17 '21 at 11:40
@Charlieface Thanks to highlight :) – Jaydip Jadhav Dec 20 '21 at 04:52

score 0 · Answer 3 · edited Dec 17 '21 at 07:03

0

You can use ISNULL function to check the NULL value of the parameter. (Assuming ru.userId will not be NULL)

WHERE r.Id = @RoomId AND 
ru.UserId = ISNULL(@UserId, ru.UserId)

edited Dec 17 '21 at 07:03

Ramil Aliyev 007

4,437
2
31
47

answered Dec 17 '21 at 06:07

Binita

26
3

How to only run left join when the variable is not null?

3 Answers3