Write a where clause that compares two columns to the same subquery?-CodePudding

I want to know if it's possible to make a where clause compare 2 columns to the same subquery. I know I could make a temp table/ variable table or write the same subquery twice. But I want to avoid all that if possible. The Subquery is long and complex and will cause significant overhead if I have to write it twice.

Here is an example of what I am trying to do.

SELECT * FROM Table WHERE Column1 and Column2 IN (Select column from TABLE)

I'm looking for a simple answer and that might just be NO but if it's possible without anything too elaborate please clue me in.

CodePudding user response：

The example you've given would probably perform best using exists, such as:

select *
from t1
where exists (
  select 1 from t2 
  where t2.col = t1.col1 and t2.col = t1.col2
);

CodePudding user response：

To prevent writing the complicated subquery twice, you can use a CTE (Common Table Expression):

;WITH MyFirstCTE (x) AS
(
   SELECT [column] FROM [TABLE1]
   -- add all the very complicated stuff here
)
SELECT *
FROM   Table2
WHERE  Column1 IN (SELECT x FROM MyFirstCTE)
AND    Column2 IN (SELECT x FROM MyFirstCTE)

Or using EXISTS:

;WITH MyFirstCTE (x) AS
(
   SELECT [column] FROM [TABLE1]
   -- add all the very complicated stuff here
)
SELECT *
FROM   Table2
WHERE  EXISTS (SELECT 1 FROM MyFirstCTE WHERE x = Column1)
AND    EXISTS (SELECT 1 FROM MyFirstCTE WHERE x = Column2)

I used deliberately clumsy names, best to pick better ones.
I started it with a ; because if it's not the first command in a larger script then a ; is needed to separate the CTE from the commands before it.