How to reuse a large query without repeating it?

Question

If I have two queries, which I will call horrible_query_1 and ugly_query_2, and I want to perform the following two minus operations on them:

(horrible_query_1) minus (ugly_query_2)
(ugly_query_2) minus (horrible_query_1)

Or maybe I have a terribly_large_and_useful_query, and the result set it produces I want to use as part of several future queries.

How can I avoid copying and pasting the same queries in multiple places? How can I "not repeat myself," and follow DRY principles. Is this possible in SQL?

I'm using Oracle SQL. Portable SQL solutions are preferable, but if I have to use an Oracle specific feature (including PL/SQL) that's OK.

Solution 1

create view horrible_query_1_VIEW as 
 select .. ...
  from .. .. ..

create view ugly_query_2_VIEW as 
 select .. ...
  from .. .. ..

Then

(horrible_query_1_VIEW) minus (ugly_query_2_VIEW)

(ugly_query_2_VIEW) minus (horrible_query_1_VIEW)

Or, maybe, with a with clause:

with horrible_query_1 as (
  select .. .. ..
    from .. .. ..
) ,
ugly_query_2 as (
  select .. .. ..
     .. .. ..
)
(select * from horrible_query_1 minus select * from ugly_query_2    ) union all
(select * from ugly_query_2     minus select * from horrible_query_1)

Solution 2

If you want to reuse the SQL text of the queries, then defining views is the best way, as described earlier.

If you want to reuse the result of the queries, then you should consider global temporary tables. These temporary tables store data for the duration of session or transaction (whichever you choose). These are really useful in case you need to reuse calculated data many times over, especially if your queries are indeed "ugly" and "horrible" (meaning long running). See Temporary tables for more information.

If you need to keep the data longer than a session, you can consider materialized views.

Solution 3

Since you're using Oracle, I'd create Pipelined TABLE functions. The function takes parameters and returns an object (which you have to create) and then you SELECT * or even specific columns from it using the TABLE() function and can use it with a WHERE clause or with JOINs. If you want a unit of reuse (a function) you're not restricted to just returning values (i.e a scalar function) you can write a function that returns rows or recordsets. something like this:

FUNCTION RETURN_MY_ROWS(Param1 IN type...ParamX IN Type)
            RETURN PARENT_OBJECT PIPELINED
            IS
            local_curs cursor_alias; --you need a cursor alias if this function is in a Package
            out_rec ROW_RECORD_OF_CUSTOM_OBJECT:=ROW_RECORD_OF_CUSTOM_OBJECT(NULL, NULL,NULL) --one NULL for each field in the record sub-object
        BEGIN
         OPEN local_curs FOR
          --the SELECT query that you're trying to encapsulate goes here
          -- and it can be very detailed/complex and even have WITH () etc..
        SELECT * FROM baseTable WHERE col1 = x;

   -- now that you have captured the SELECT into a Cursor
   -- here you put a LOOP to take what's in the cursor and put it in the 
   -- child object (that holds the individual records)
          LOOP
         FETCH local_curs --opening the ref-cursor
          INTO out_rec.COL1, 
               out_rec.COL2,
               out_rec.COL3;
        EXIT WHEN local_curs%NOTFOUND;
         PIPE ROW(out_rec); --piping out the Object
        END LOOP;
        CLOSE local_curs;  -- always do this
        RETURN;  -- we're now done
  END RETURN_MY_ROWS;

after you've done that, you can use it like so

SELECT * FROM TABLE(RETURN_MY_ROWS(val1, val2)); 

you can INSERT SELECT or even CREATE TABLE out of it , you can have it in joins.

two more things to mention:

--ROW_RECORD_OF_CUSTOM_OBJECT is something along these lines
CREATE or REPLACE TYPE ROW_RECORD_OF_CUSTOM_OBJECT AS OBJECT
(
     col1 type;
     col2 type;
      ...
     colx type;
 );

and PARENT_OBJECT is a table of the other object (with the field definitions) we just made

create or replace TYPE PARENT_OBJECT IS TABLE OF ROW_RECORD_OF_CUSTOM_OBJECT;

so this function needs two OBJECTs to support it, but one is a record, the other is a table of that record (you have to create the record first).

In a nutshell, the function is easy to write, you need a child object (with fields), and a parent object that will house that child object that is of type TABLE of the child object, and you open the original base-table fetching SQL into a SYS_REFCURSOR (which you may need to alias) if you're in a package and you read from that cursor from a loop into the individual records. The function returns a type of PARENT_OBJECT but inside it packs the records sub-object with values from the cursor.

I hope this works for you (there may be permissioning issues with your DBA if you want to create OBJECTs and Table functions)*/

Comments

[Buttons840]

Good, but I would like to handle items unique to horrible_query_1 differently than I handle the items unique to ugly_query_2. Is it possible to tell which set the items are unique to with this query? EDIT: The views may be the best solution for plain SQL.

[JamieSee]

Sure, wrap your two set operations in outer queries with selects that include fixed columns called Source, i.e. SELECT 'horrible_query_1' AS Source, * and SELECT 'ugly_query_2' AS Source, *. That way your UNION will give you all the columns from your query plus the source identifier.

[Jon Heller]

That may help with performance, but I think this question is more about coding style.

[Buttons840]

The last example, using the "with clauses" have been very helpful over the years since I originally asked this question. These "with clauses" are sometimes called common table expressions (or CTE). These are some useful terms to know when searching for more details. The with-clauses were added to the SQL standard in 1999 so most databases should support them, except for MySQL which still hasn't implemented this part of the twenty year old standard.

[Alicia]

I wanted to create a temp table or a view to perform searches on, but I couldn't do it because I didn't have the proper permissions for the DB. Instead, I used a WITH clause, and thanks to it I could write a single query without repeating my very own "horrible_query".

[UpTide]

link is dead, but this other question has information about temporary tables. stackoverflow.com/questions/3462131/…