T-SQL - Query data based on different filter granularity-CodePudding

-- Data Setup

DECLARE @Table AS TABLE
    (
        [Id]        INT IDENTITY(1, 1)
      , [Type]      TINYINT
      , [TypeOne]   INT
      , [TypeTwo]   INT
      , [TypeThree] INT
    ) ;

INSERT INTO @Table
    ( [Type]
    , [TypeOne]
    , [TypeTwo]
    , [TypeThree] )
VALUES
    ( 1, 1, NULL, NULL )
  , ( 1, 2, NULL, NULL )
  , ( 1, 3, NULL, NULL )
  , ( 2, NULL, 10, NULL )
  , ( 2, NULL, 20, NULL )
  , ( 3, NULL, NULL, 100 )
  , ( 3, NULL, NULL, 200 )
  , ( 3, NULL, NULL, 300 ) ;

-- Query filters

DECLARE @IncludeTypeOne BIT = 1
      , @IncludeTypeTwo BIT = 0 ;

DECLARE @TypeThree_Ids TABLE ( [TypeThree] INT ) ;
INSERT INTO @TypeThree_Ids
VALUES
    ( 200 )
  , ( 300 ) ;

-- Goal: To query @Table based on @IncludeTypeOne, @IncludeTypeTwo, and @TypeThree_Ids values. For first two filters, there's no need to check for specific value of the type in the [TypeOne] and [TypeTwo] columns. However, for the third filter, specific values in the [TypeThree] column must match with the values in @TypeThree_Ids. Is there a way to do this without doing three separate queries and union-ing them all together (the actual table/data is quite large)?

-- Expected output

Id  Type    TypeOne TypeTwo TypeThree
1   1       1       NULL    NULL
2   1       2       NULL    NULL
3   1       3       NULL    NULL
7   3       NULL    NULL    200
8   3       NULL    NULL    300

-- My unsuccessful try thus far

SELECT  *
FROM    @Table
WHERE   ( ( @IncludeTypeOne = 0 AND [Type] <> 1 ) OR [Type] = 1 )
        AND ( ( @IncludeTypeTwo = 0 AND [Type] <> 2 ) OR [Type] = 2 )
        AND ( ( ( SELECT COUNT(1) FROM @TypeThree_Ids ) = 0 AND [Type] <> 3 ) OR [TypeThree] IN ( SELECT [TypeThree] FROM @TypeThree_Ids ) ) ;

-- Actual output

Id  Type    TypeOne TypeTwo TypeThree

CodePudding user response：

Better to use a join than a sub query -- like this:

SELECT *
FROM @Table
LEFT JOIN @TypeThreeIds ON @Table.TypeThree= @TypeThreeIds.TypeThree
WHERE (@includetypeone = 1 AND [Type] = 1)
   OR (@includetypetype = 2 AND [Type] = 2)
   OR ([Type] = 3 AND @TypeThreeIds.TypeThree IS NOT NULL)