CODEWITHSUNDEEP
oracle-database
kasperhj
kasperhj

Reputation: 10482

Using the result of a query in another query without subquerying

I have a query that creates a result with two columns of ints.

from_id | to_id
---------------
  51    |  100
  3     |  21
  ...   |  ...

Let's call this result map_ids

Then, can I do something like

SELECT * FROM some_table st WHERE st.id IN map_ids.to_id

to only select stuff from some_table that has an ID that matches the result of the to_id in the first query?

I want to avoid using a subquery because the query that generate map_ids is quite long and is actually used twice in the real select from some_table.

Upvotes: 1

Views: 53

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

It sounds like you're looking for subquery factoring. You can put your first query into a with clause and then join to that in the main body:

WITH map_ids (from_id, to_id) as (
   ... you first query ...
)
SELECT columns
FROM map_ids mi
JOIN some_table st ON st.id = mi.to_id

You can refer to map_ids again later, e.g. in another branch of a union.

You can do something like where st.id in (select to_id from map_ids) but joining is probably clearer (and maybe more efficient, depending on what the optimiser does with the real query).

Upvotes: 2

Related Questions