SQL - Most performant manner to conditionally exlude a join? (if it's even possible)

Question

If I have the following table structure...

Table 1: BlogPost

PostId |  Name | Text    

Table 2: Tags

TagId  | Tag    

Table 3: BlogPostTag

PostId | TagId

And the following stored procedure...

CREATE PROCEDURE SearchBlogPosts

    @tagstring nvarchar(max),

AS
BEGIN

    DECLARE @searchTags TABLE (Tag varchar(50));

    IF @tagstring IS NOT NULL AND @tagstring <> ''
        BEGIN
            INSERT INTO @tags SELECT s AS tag FROM dbo.Split(',',@tagstring);
        END

    SELECT * FROM BlogPost b
        JOIN BlogPostTags bt on bt.PostId = b.PostId    
        JOIN Tags t on t.TagId = bt.TagId
        JOIN @searchTags st ON st.Tag = t.Tag
            ...
        (Other Joins and where clauses may exist below here)
END

...what is the most "performant" manner in which I could exclude the joins on the tag tables if @tagstring is null or blank?

Answer 1

I don't think specifying a left join would incur a performance penalty (I'm assuming @searchTags is empty if @tagstring is null or blank).

Or do you not want to incur a performance penalty from joining @searchTags on tags?

It's not really clear in your example where data in @searchtags comes from.

LEFT JOIN Tags t on t.TagId = bt.TagId AND @tagstring is not null AND @tagstring <> ''

The server should be smart enough to not attempt to join at all with that condition present. I wouldn't think it's something that should cause any significant performance degradation in any case.

If you're looking to not have the columns show up in the result at all for some reason an IF...ELSE block with two different queries is the easiest way to accomplish that.

Answer 2

I think the best performance you can gain is not to run the query if @tagstring is null or blank. With all inner joins and @searchTags having no rows, you'll never get rows returned.

So, you can move the SELECT statement into the IF statement, when true block.

Answer 3

The only way (and also the best way) to specify conditional joins is to have distinct queries:

IF @tagstring IS NOT NULL AND @tagstring <> '' 
BEGIN
    SELECT * FROM BlogPost b 
        JOIN BlogPostTags bt on bt.PostId = b.PostId     
        JOIN Tags t on t.TagId = bt.TagId 
        JOIN @searchTags st ON st.Tag = t.Tag 
END
ELSE
BEGIN
    SELECT * FROM BlogPost b 
        JOIN BlogPostTags bt on bt.PostId = b.PostId     
        JOIN Tags t on t.TagId = bt.TagId 
END

SQL is a declarative data access language, not your application imperative processing language. Any query you declare has to create an access path that works in all cases. Having conditional logic in the query is the worst thing you can do, it forces access plans that usually scan all possible data because they cannot determine if conditions are true or false at plan creation time.