How to avoid same subquery in multiple select in oracle?

oracle, sql

Solution

Use the with clause. I didn't re-create your exact example issue, but any number of repeated sub-queries can be put in the WITH clause and then referenced in the query.

WITH  address_keys as (
        SELECT  address_key
        FROM    person_address
        WHERE   peson_key IN (person_list)
        )
Select * from table1, table2, address_keys
where table1.address_key = address_keys.address_key
and table2.address_key = address_keys.address_key

Problem

This is three different selects using same subquery. How can I use subquery result instead of doing sub query again. ``` SELECT * FROM Address WHERE address_key IN ( SELECT address_key FROM person_address WHERE peson_key IN (person_list) ); -- person_list := '1,2,3,4' SELECT * FROM Phone WHERE phone_key IN ( SELECT address_key FROM person_address WHERE peson_key IN (person_list) ); SELECT * FROM Email WHERE address_key IN ( SELECT address_key FROM person_address WHERE peson_key IN (person_list) ); ```

Original source