How to conditionally join a table?

I am implementing a searching mechanism in a website and stumbled upon the SQL aspect of it.

The user can search for stories by any combination of these filters: story title, story tag, or the username of the story's author. If no filter is provided, then just return all stories.

My immediate solution to this is this stored procedure:

(
@TitleFilter varchar(50) = NULL
,@TagFilter varchar(30) = NULL
,@UserFilter varchar(30) = NULL
)

SELECT
    story.Title
    ,story.AddedDTS

FROM
    Stories story
    INNER JOIN FREETEXTTABLE(Stories, Title, @TitleFilter) ft
        ON ft.[key] = story.ID
    LEFT JOIN StoryTags st
        ON st.StoryID = story.ID
    LEFT JOIN Tags tag
        ON tag.ID = st.TagID
    LEFT JOIN StoryUser su
        ON su.StoryID = story.ID
    LEFT JOIN Users u
        ON u.ID = su.UserID

WHERE
    1=1
    AND (
            (@TagFilter IS NULL AND @UserFilter IS NULL)
            OR (@TagFilter IS NOT NULL AND tag.Name = @TagFilter)
            OR (@UserFilter IS NOT NULL AND u.Username = @UserFilter)
        )

There's a couple of problems with this, though, and I am yet to find a better approach.

First off, the Stories table has full-text search enabled, so I have to use the FREETEXTTABLE mechanism, which mandates the predicate to not be NULL, so this stored procedure will not work with @TitleFilter set to NULL.

Secondly, if I only search by title, then joining to StoryTags, Tags, StoryUsers, and Users tables are merely a useless overhead.

So the question i开发者_运维问答s for both points I stated: can I conditionally omit joins to optimize the execution time?

If there's a whole different approach to this, you are more than welcome to share it; I advocate thinking out of the box.

Nothing says that your comparisons in your joins and where clauses have to actually involve table columns. Try something like this:

declare @useTable1 bit -- set to 0/1 to indicate whether it should be used.
declare @useTable2 bit -- set to 0/1 to indicate whether it should be used.
declare @useTable3 bit -- set to 0/1 to indicate whether it should be used.

select *
from      requiredTable   t
left join optionalTable_1 t1 on t1.requiredTableID = t.ID and @useTable1 = 1
left join optionalTable_2 t2 on t2.requiredTableID = t.ID and @useTable2 = 1
left join optionalTable_3 t3 on t3.requiredTableID = t.ID and @useTable3 = 1

SQL Server's optimizer, at least, is smart enough to short-circuit things based on the comparison to an invariant.

Works like a charm.

Given that when @TitleFilter is null it doesn't work with FREETEXTTABLE I would either break it up with an if statement like so.

IF @TitleFilter is not null

    SELECT

        story.Title
        ,story.AddedDTS

    FROM
        Stories story
        INNER JOIN FREETEXTTABLE(Stories, Title, @TitleFilter) ft
         ON ft.[key] = story.ID
ELSE 

    SELECT
        story.Title
        ,story.AddedDTS

    FROM
        Stories story
        LEFT JOIN StoryTags st
        ON st.StoryID = story.ID
        LEFT JOIN Tags tag
        ON tag.ID = st.TagID
        LEFT JOIN StoryUser su
        ON su.StoryID = story.ID
        LEFT JOIN Users u
        ON u.ID = su.UserID
    WHERE

        (@TagFilter IS NULL AND @UserFilter IS NULL)
         OR (@TagFilter IS NOT NULL AND tag.Name = @TagFilter)
        OR (@UserFilter IS NOT NULL AND u.Username = @UserFilter)

Or if that offends you for some reason there's always The Curse and Blessings of Dynamic SQL

As far as I can see, there are 3 solutions (at least):

    --Solution #1
DECLARE @TitleFilter varchar(50) = NULL
        ,@TagFilter varchar(30) = NULL
        ,@UserFilter varchar(30) = NULL

IF (@TitleFilter IS NOT NULL)
    SELECT story.Title
          ,story.AddedDTS
    FROM   Stories AS story
            INNER JOIN FREETEXTTABLE(Stories, Title, @TitleFilter) AS ft
                ON story.ID = ft.[key]
        LEFT OUTER JOIN StoryTags AS st 
            ON story.ID = st.StoryID AND (@TagFilter IS NOT NULL)
        LEFT OUTER JOIN Tags AS tag
            ON  st.TagID = tag.ID AND (@TagFilter IS NOT NULL)
        LEFT OUTER JOIN StoryUser AS su
            ON story.ID = su.StoryID AND (@UserFilter IS NOT NULL)
        LEFT OUTER JOIN Users AS u 
            ON su.UserID = u.ID AND (@UserFilter IS NOT NULL)
    WHERE     (@TagFilter IS NULL OR tag.Name = @TagFilter)
          AND (@UserFilter IS NULL OR u.Username = @UserFilter)
    OPTION (RECOMPILE);--Use it in SQL 2008 R2 or later
ELSE
    SELECT story.Title
          ,story.AddedDTS
    FROM   Stories AS story
        LEFT OUTER JOIN StoryTags AS st 
            ON story.ID = st.StoryID AND (@TagFilter IS NOT NULL)
        LEFT OUTER JOIN Tags AS tag
            ON  st.TagID = tag.ID AND (@TagFilter IS NOT NULL)
        LEFT OUTER JOIN StoryUser AS su
            ON story.ID = su.StoryID AND (@UserFilter IS NOT NULL)
        LEFT OUTER JOIN Users AS u 
            ON su.UserID = u.ID AND (@UserFilter IS NOT NULL)
    WHERE     (@TagFilter IS NULL OR tag.Name = @TagFilter)
          AND (@UserFilter IS NULL OR u.Username = @UserFilter)
    OPTION (RECOMPILE);--Use it in SQL 2008 R2 or later
GO


--Solution #2
DECLARE @TitleFilter varchar(50) = NULL
        ,@TagFilter varchar(30) = NULL
        ,@UserFilter varchar(30) = NULL

IF (@TitleFilter IS NOT NULL)
    SELECT story.Title
          ,story.AddedDTS
    FROM   Stories AS story
            INNER JOIN FREETEXTTABLE(Stories, Title, @TitleFilter) AS ft
                ON story.ID = ft.[key]
    WHERE     (@TagFilter IS NULL OR EXISTS(SELECT 1 FROM StoryTags AS st INNER JOIN Tags AS tag ON st.TagID = tag.ID WHERE tag.Name = @TagFilter))
          AND (@UserFilter IS NULL OR EXISTS(SELECT 1 FROM StoryUser AS su INNER JOIN Users AS u ON su.UserID = u.ID WHERE u.Username = @UserFilter))
ELSE
    SELECT story.Title
          ,story.AddedDTS
    FROM   Stories AS story
    WHERE     (@TagFilter IS NULL OR EXISTS(SELECT 1 FROM StoryTags AS st INNER JOIN Tags AS tag ON st.TagID = tag.ID WHERE tag.Name = @TagFilter))
          AND (@UserFilter IS NULL OR EXISTS(SELECT 1 FROM StoryUser AS su INNER JOIN Users AS u ON su.UserID = u.ID WHERE u.Username = @UserFilter))
  --Don't get confused by the execution plan. You will see StoryTags, Tags, StoryUser and Users tables with some persentage. But those tables will be used 
  --only if the corresponding filter will allow to do so (look at the Filter operator).
  --You can use OPTION (RECOMPILE) if you want to recompile the query every time it runs.
 GO

 --Solution #3
 DECLARE @TitleFilter varchar(50) = NULL
        ,@TagFilter varchar(30) = NULL
        ,@UserFilter varchar(30) = NULL

 DECLARE @SqlScript nvarchar(MAX), @ParamDefinition nvarchar(512);
 SET @SqlScript = '
    SELECT story.Title
          ,story.AddedDTS
    FROM   dbo.Stories AS story';
 IF (@TitleFilter IS NOT NULL)
     SET @SqlScript += '
                INNER JOIN FREETEXTTABLE(Stories, Title, @TitleFilter) AS ft
                    ON story.ID = ft.[key]';
 IF (@TagFilter IS NOT NULL)
     SET @SqlScript += '
        INNER JOIN dbo.StoryTags AS st 
            ON story.ID = st.StoryID
        INNER JOIN dbo.Tags AS tag
            ON st.TagID = tag.ID AND tag.Name = @TagFilter';

 IF (@UserFilter IS NOT NULL)
     SET @SqlScript += '
        INNER JOIN dbo.StoryUser AS su
            ON story.ID = su.StoryID
        INNER JOIN dbo.Users AS u 
            ON su.UserID = u.ID AND u.Username = @UserFilter';

 SET @ParamDefinition = '@TitleFilter varchar(50) 
                       ,@TagFilter varchar(30),
                       ,@UserFilter varchar(30)';

 EXEC sp_executesql @SqlScript, @ParamDefinition, @TitleFilter, @TagFilter, @UserFilter;
GO