Q
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


This was last published in September 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close