t-sql include inner join on a table only if records present

Question

I have a very big table with tons and tons of records.

[HugeTable](id, col1, col2, col3...)

There is a page on the front end application showing this [HugeTable] data based on many filters. One of the filters will give a subset of [HugeTable], if not null

#HugeTable_subset(id)

if this filter is present, #HugeTable_subset would have records. I would like to narrow down [HugeTable] data to only matching records in #HugeTable_subset.

so right now, in the t-sql, I am doing an if-else kind of query

IF (SELECT Count(*) FROM #HugeTable_subset) > 0
    BEGIN
        SELECT HugeTable.* FROM [HugeTable] h
        JOIN #HugeTable_subset  t
        ON h.id = t.id
        WHERE h.params = @searchParams
    END
ELSE
    BEGIN
        SELECT * FROM [HugeTable] h
        WHERE h.params = @searchParams
    END

Is there a way I could merge these two selects into one?

Answer 1

To join the two selects in one you can just use a LEFT OUTTER JOIN instead of a INNER JOIN. You probably already know that, yes, maybe you don't knows you already doing it in the most optimized way. sql-server ill create two sub-query plans for each select inside the IF-ELSE and use each properly.

You can acid teste it to see if there are any difference and if the IF-ELSE really beats up the LEFT JOIN option

Also there's still two point I can point out.

1) Good Indexes over the filters can really improve your performance.

2) You can use pagination to return just a few results, improving performance and user experience when the result returns a ton of records

Answer 2

    SELECT HugeTable.* FROM [HugeTable] h
    WHERE ((SELECT Count(*) FROM #HugeTable_subset) = 0) OR 
           h.id IN (SELECT t.id from #HugeTable_subset  t))