sql - 将子查询设置为要在 IN 运算符中使用的变量

Question

我想使用一个变量来表示将在查询的 WHERE 子句中使用的结果集。

SELECT *
FROM Table1
WHERE 
  Exam1_ID IN (SELECT Id FROM Exam) 
  OR Exam2_ID IN (SELECT Id FROM Exam) 
  OR Exam3_ID IN (SELECT Id FROM Exam) 
  OR Exam4_ID IN (SELECT Id FROM Exam) 

我想用一个变量代替 SELECT Id FROM Exam所以我不必一直重复查询。我尝试声明一个变量，但由于子查询的结果可能包含多个整数，我不确定将变量声明为什么。我继续尝试......

DECLARE @SubQuery INT;
SET @SubQuery = (SELECT Id FROM Exam);

SELECT *
FROM Table1
WHERE 
    Exam1_ID IN (@SubQuery) 
    OR Exam2_ID IN (@SubQuery) 
    OR Exam3_ID IN (@SubQuery) 
    OR Exam4_ID IN (@SubQuery)

我收到以下错误..

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

您可能会发现使用这样的 WHERE EXISTS 性能更好

SELECT *
FROM Table1 t1
WHERE EXISTS (  SELECT * 
                FROM Exam e 
                WHERE t1.Exam1_ID = e.Id
                OR t1.Exam2_ID = e.Id
                OR t1.Exam3_ID = e.Id
                OR t1.Exam4_ID = e.Id)