UNION in a subquery

I have a problem with UNION as subquery. I want to do something like: Select x from Table where x IN (a Union query...

which fetch x from multiple tables). It fails saying that union can not be a subquery. Here is my code:

Select PolicyID from Policies 
where ServerID= 1 AND PolicyID in 
( SELECT PolicyID From AlertPolicy 
WHERE VolumeName="g" UNION 
SELECT PolicyID From BackupPolicy )

Any suggestions?

There are a number of ways to rewrite this query, and here's one of them:

select PolicyID 
  from Policies 
 where ServerID= 1 
   and ( 
       PolicyID in 
          ( select PolicyID 
              from AlertPolicy 
             where VolumeName="g" )
    or PolicyID in 
          ( select PolicyID 
              from BackupPolicy )
       )

The nice part about this solution is that by splitting up the UNION, if the value is found in the first subquery, the second will not even be executed, since it follows an OR connector. The optimizer is smart enough to stop after a TRUE condition is discovered in the first subquery.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.