Oracle SQL: Multiple Subqueries Unioned Without Running Original Query Multiple Times.

Question

So I've got a very large database, and need to work on a subset ~1% of the data to dump into an excel spreadsheet to make a graph. Ideally, I could select out the subset of data and then run multiple select queries on that, which are then UNION'ed together. Is this even possible? I can't seem to find anyone else trying to do this and would improve the performance of my current query quite a bit. Right now I have something like this:

SELECT (
     SELECT (
          SELECT(
                long list of requirements
          )
          UNION
          SELECT(
                slightly different long list of requirements
          )
     )
)

and it would be nice if i could group the commonalities of the two long requirements and have simple differences between the two select statements being unioned.

Answer 1

If you're using Oracle 9i or later, you can make use of subquery factoring (AKA Common Table Expression (CTE) in SQL Server). It uses the WITH syntax:

WITH inline_view_name AS (
   SELECT *
     FROM YOUR_TABLEs)
SELECT 
  FROM inline_view_name a
 WHERE ...
UNION ALL
SELECT 
  FROM inline_view_name b
 WHERE ...

...and can be reused like you see in the example.