SQL Server, choosing from two TABLEs using IF statement inside WHERE statement depending on the parameter

Question

Hello All,

How can I do the following in SQL Server

DECLARE @Local nvarchar(20)
SET @Local = 'True'

    SELECT * FROM My_Table
    WHERE my_ID IN

    (IF @Local = 'True' 
         SELECT AllIDs FROM ATable
    ELSE 
         SELECT TeamIDs FROM TeamTable
    )

Answer 1

SELECT  *
FROM    mytable
WHERE   my_id IN
        (
        SELECT  allids
        FROM    atable
        WHERE   @Local = 'True'
        )
UNION ALL
SELECT  *
FROM    mytable
WHERE   my_id IN
        (
        SELECT  teamids
        FROM    teamtable
        WHERE   COALESCE(@Local, '') <> 'True'
        )

The optimizer will optimize away the wrong query, so the performance will as that of the corresponding (remaining) query.

Answer 2

DECLARE @Local nvarchar(20)
SET @Local = 'True'

SELECT * FROM My_Table
WHERE my_ID IN ( 
     SELECT AllIDs FROM ATable
     WHERE @Local = 'True'
     UNION ALL 
     SELECT TeamIDs FROM TeamTable
     WHERE @Local != 'True'
)

Answer 3

Go for a union :-

SELECT * FROM My_Table WHERE my_id IN
(
  SELECT AllIDs AS MyIDs FROM ATable WHERE @Local = 'True'
  UNION
  SELECT TeamIDs AS MyIDs FROM TeamTable WHERE @Local <> 'True'
)

Answer 4

Don't do this!

(at least not if you care about performance)

There is certainly a way of doing this, but you really shouldn't - the reason being that (in general) a single statement has only 1 execution plan, however you essentially have 2 (potentially very different) requests:

SELECT * FROM My_Table
WHERE my_ID IN (SELECT AllIDs FROM ATable)

SELECT * FROM My_Table
WHERE my_ID IN (SELECT AllIDs FROM TeamTable)

Combining these two queries means that SQL server is forced to try and optimise both of these at the same time using only 1 exectuion plan. Depending on how different the two tables are this might work fine, or it might go horrily horrily wrong.

For example, if ATable contains only 1 row, while TeamTable contains all of the ids in My_Table then SQL server has to choose / compromise between doing a lookup (best if using ATable) and a table scan (best if using TeamTable) - whatever it ends up doing it's not possible for SQL server to execute both queries using the optimal execption plan (unless both execution plans happen to be the same).

You should use 2 statements instead:

DECLARE @Local nvarchar(20)
SET @Local = 'True'

IF @Local = 'True' 
    SELECT * FROM My_Table
    WHERE my_ID IN (SELECT AllIDs FROM ATable)
ELSE
    SELECT * FROM My_Table
    WHERE my_ID IN (SELECT AllIDs FROM TeamTable)

In fact in this particular case you might be better off selecting the list of ID's into a temporary table instead.