Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on Oracle and SQL