SQL Server，根据参数在WHERE语句中使用IF语句从两个表中进行选择

How can I do the following in SQL Server

如何在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
    )

4 个解决方案

#1

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'
)

#2

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.

优化器将优化错误的查询，因此性能将与相应的(剩余的)查询相同。

#3

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'
)

#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.

合并这两个查询意味着SQL server将*使用一个exectuion计划同时尝试和优化这两个查询。这取决于这两个表的不同程度，它可能运行得很好，也可能发生可怕的错误。

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).

例如,如果ATable只包含一行,而TeamTable包含所有的id在My_Table SQL server /选择妥协之间做一个查找(最好使用ATable)和表扫描(最好使用TeamTable)——不管它最终做不可能对SQL server执行两个查询使用最佳execption计划(除非执行计划是相同的)。

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.

实际上，在这种情况下，您最好将ID列表选择到临时表中。