Setting subquery to variable to be used within the IN operator

Question

I would like to use a variable to represent a result set that will be used in the WHERE clause of a query.

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) 

I would like to use a variable in place of SELECT Id FROM Exam so I don't have to keep repeating the query. I tried declaring a variable but since the results of the subquery could contain multiple integers I am not sure what the declare the variable as. I went ahead and tried ...

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)

I received the following error ..

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

You can write an exists like this.

      SELECT *
        FROM Table1 t1
        WHERE EXISTS (  
        SELECT 1  FROM Exam e 
        WHERE e.Id in ( t1.Exam1_ID , t1.Exam2_ID , t1.Exam3_ID, t1.Exam4_ID ) 
        )