SQL Sub Query NO execute if null

Question

I was wondering if there was a way to not execute my subquery if my @ID1 is NULL?

CREATE PROCEDURE [dbo].[TestTable_Search]
    @Col1 int,
    @Col2 uniqueidentifier,
    @Col3 datetime,
    @Col4 datetime,
    @ID1 varchar(10)
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    [Col1] = COALESCE(@Col1, Col1) AND
    [Col2] = COALESCE(@Col2, Col2) AND
    [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
    [Col4] <= COALESCE(@Col4 + "23:59:59", Col4) AND
    [Col5] IN (SELECT [ID2] FROM [dbo].[TestTable2] WHERE [ID1] =  @ID1)

Answer 1

If you don't execute the subquery then no rows will be returned anyway. So just handle this case with a separate SELECT like so:

IF @ID1 IS NULL
BEGIN

    SELECT *
    FROM dbo.[TestTable]
    WHERE 1 = 0

END
ELSE
BEGIN

    SELECT *

    FROM
        [dbo].[TestTable]
    WHERE
        [Col1] = COALESCE(@Col1, Col1) AND
        [Col2] = COALESCE(@Col2, Col2) AND
        [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
        [Col4] <= COALESCE(@Col4 + "23:59:59", Col4) AND
        [Col5] IN (SELECT [ID2] FROM [dbo].[TestTable2] WHERE [ID1] =  @ID1)

END

Answer 2

Not sure of your meaning, but maybe this is what you're looking for:

SELECT *
FROM
    [dbo].[TestTable]
WHERE
    [Col1] = COALESCE(@Col1, Col1) AND
    [Col2] = COALESCE(@Col2, Col2) AND
    [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
    [Col4] <= COALESCE(@Col4 + "23:59:59", Col4) AND (
    @ID1 IS NULL 
        OR [Col5] IN (SELECT [ID2] FROM [dbo].[TestTable2] WHERE [ID1] =  @ID1))

Answer 3

SQL Server is not very good in handling OR conditions, especially on variables, that's why this probably will be the best decision:

SELECT  *
FROM    [dbo].[TestTable]
WHERE   [Col1] = COALESCE(@Col1, Col1) AND
        [Col2] = COALESCE(@Col2, Col2) AND
        [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
        [Col4] <= COALESCE(@Col4 + "23:59:59", Col4) AND
        AND @id1 IS NULL
UNION ALL
SELECT  *
FROM    [dbo].[TestTable]
WHERE   [Col1] = COALESCE(@Col1, Col1) AND
        [Col2] = COALESCE(@Col2, Col2) AND
        [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
        [Col4] <= COALESCE(@Col4 + "23:59:59", Col4) AND
        [Col5] IN (SELECT [ID2] FROM [dbo].[TestTable2] WHERE [ID1] =  @ID1)

If @id is not NULL, the first subquery will return nothing because of the filter.

If @id is NULL, the second subquery will return nothing, because the comparison to a NULL never matches, the inner subquery will return an empty set, and col5 IN (SELECT …) will be never satisfied.

SQL Server can efficiently detect these things in runtime, that's why either the first of the second subquery will be optimized out almost instantly.

See this article in my blog for performance comparison of OR against UNION ALL:

• IN with a comma separated list: SQL Server