Use result of select query into another multiple times

Question

I have one complex select query(Query-1) which is executed using INTERSECT and it returns IDs of a particular column.

Query #1:

SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table;

Now there is another more complex query which requires the result from query #1 multiple times.

Query #2:

SELECT * 
FROM
    (SELECT my_id, col_1, my_value
     FROM my_table
     WHERE my_id IN (result from query-1) 
       AND col_3 IN (SELECT col_3 FROM another_table1 
                     WHERE my_id IN (result from query-1) 
                       AND another_col IN (SELECT another_col 
                                           FROM another_table2 
                                           WHERE my_id IN (result from query-1))))
    PIVOT 
        (MIN(my_value)
            FOR(col_1) IN(1 AS name, 2 AS lastname, 3 AS address)
        )

As you can see results from query-1 is required multiple times in query-2, what I tried is to substitute entire query-1 in query-2 wherever needed which increases complexity and readability of the query.

Is there a way to do this in simple manner?

There is already an answer that tells you the proper way to do this - use the `with` clause. This has another benefit: in your attempt, where you inline the same (sub)query in several places, Oracle is not smart enough to check to see that it's the same subquery; it will compute your intersection several times. With the `with` clause, if it's used more than once, Oracle may decide - without your intervention - that is is more efficient to **materialize** the result of the subquery, so that it is computed just once and the output is used repeatedly. — , Oct 14 '21 at 18:16

score 1 · Accepted Answer · edited Oct 14 '21 at 18:12

how about using the with clause (subquery factoring clause):

with query-1 as (SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table)

SELECT * FROM
(
   SELECT my_id, col_1, my_value
   FROM my_table
   WHERE my_id IN (select id from query-1) AND col_3 IN
      (SELECT col_3 FROM another_table1 WHERE my_id IN (select id from query-1) AND another_col IN
         (SELECT another_col FROM another_table2 WHERE my_id IN (select id from query-1))
)
)
PIVOT (
   MIN(my_value)
   FOR(col_1)
   IN(1 AS name, 2 AS lastname, 3 AS address)
)

`query-1` is not a valid identifier (left for you to think about why). — , Oct 14 '21 at 18:13

score 0 · Answer 2 · answered Oct 14 '21 at 18:30

I would definitely use View for any query you will use multiple times.

I will write the below code first:

Create View Query1
as
SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table;

Once I select the above code, I will execute (F5) it. This will store the view. Then I will write below code and execute whenever you want to call it:

Select * From Query1

Or you can simply use Query1 inside your Query2.

Use result of select query into another multiple times

2 Answers2