CODEWITHSUNDEEP
sqlsql-server
Deathstalker
Deathstalker

Reputation: 844

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

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

I updated the select to use OR instead of AND as this clarified my question a little better.

Upvotes: 0

Views: 470

Answers (2)

Peter B
Peter B

Reputation: 24187

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.

Upvotes: 1

Stu
Stu

Reputation: 32609

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

Upvotes: 1

Related Questions