How to effectively filter between several tables in SQL

Question

I have a main table with another 7 tables attached to it by a key. I have 10 different filter types in the secondary tables I will not always have all types of filters in one QUERY, some of them may be equal to NULL. The problem is that the secondary tables are very large and if they do not need to be accessed by the filter then it is best not to include them in the INNER JOIN, How to make it the most effective?

Answer 1

You can use dynamic SQL to generate a query based on varied requirements. This way, you can exclude joins if you don't need them, like so:

DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT * FROM myTable AS t ';
IF @param1 IS NOT NULL
    SET @sql = @sql + 'JOIN mySecondTable AS s ON s.Id = t.mySecondTableId ';
IF @param2 IS NOT NULL
    SET @sql = @sql + 'JOIN myThirdTable AS x ON x.Id = t.myThirdTableId';
-- repeat as required to build a valid query

DECLARE @filters NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX) = '@filterParam1 INT, @filterParam2 NVARCHAR(128);'

IF @filterParam1 IS NOT NULL
BEGIN
   SET @filters = '[column] = @filterParam1';
END

IF @filterParam2 IS NOT NULL
BEGIN
  SET @filters = CASE WHEN @filters IS NULL 
                      THEN '' 
                      ELSE ',' 
                  END + '[column2] = @filterParam2';
END

DECLARE @where NVARCHAR(MAX);
IF @filters IS NOT NULL
BEGIN
   SET @where = 'WHERE ' + @filters;
   SET @sql = @sql + @where;
END

EXEC sp_ExecuteSql @stmt = @sql
                 , @params = @params
                 , @filterParam1 = @filterParam1
                 , @filterParam2 = @filterParam2;

Assuming that @param1 is null and @filterParam1 is also null, it would generate the following query:

SELECT * 
  FROM myTable AS t
  JOIN myThirdTable AS x on x.Id = t.myThirdTableId
 WHERE [column2] = @filterParam2;

Notice how the join to mySecondTable has not been included and the unnecessary filter has not been included either. You can find more examples of how to use sp_executeSql on Microsoft Docs.