SQL: Is it possible to join on the same sub-select?

I'd like to know if it possible to join the same sub-select to itself within the same query without actually having to execute the query again? The following query is the actual obfuscated query I want to run

select * from 

       (

        -- Sub query A - same as sub query B
          select bc.service_type,  bc.cid, min(bc.last_modified_date) as last_modified1 from 
              (

                 select * from table_a bc2
                where bc2.state != 7
                AND bc2.cid in 
                ( 

                   select cid from table_a TA, table_b TB
                    where TB.name not like '% IS' and TA.state != 7
                    AND TA.service_type = 1
                    AND TA.username is not null
                    and TA.bctid = TB.bctid
                )
              ) bc
           group by service_type, cid
         ) result1,

         (

         // Sub query B - same as sub query A
          select bc.service_type,  bc.cid, min(bc.last_modified_date) as last_modified2 from 
              (

                 select * from table_a bc2
                where bc2.state != 7
                AND bc2.cid in 
                ( 

                    -- select affected records
                   select cid from table_a TA, table_b TB
                    where TB.name not like '% IS' and TA.state != 7
                    AND TA.service_type = 1
                    AND TA.us开发者_C百科ername is not null
                    and TA.bctid = TB.bctid
                )
              ) bc
           group by service_type, cid
         ) result2

where result1.service_type = 1
and result2.service_type = 2
and result1.cid = result2.cid
and result1.last_modified1 < result2.last_modified2

The explain plan for the duplicate sub-query is expensive given the size of the table, so I ideally don't want to run it twice. What I'm looking for is some way of cloning the result of the first query and joining it to itself!

This is to be run in Oracle in case there is some DB specific extension.

---

Use a with statement:

with bar as (
select * from foo where ...
)
select bar.* from bar join bar barian on ...

---

Denis' solution is the best way to do that in Oracle.

However you might want to create a view if you need to use that sub-select in more than one query.

---

I have a feeling that the original query can be rewritten into (I didn't test this):

    select * from 
    (
        select bc2.cid, min(bc2.last_modified_date) as last_modified1 from table_a bc2
        where bc2.state != 7
        AND bc2.cid in 
        ( 
            select cid from table_a TA, table_b TB
            where TB.name not like '% IS' and TA.state != 7
            AND TA.service_type = 1
            AND TA.username is not null
            and TA.bctid = TB.bctid
        )
        and bc2.service_type = 1
        group by cid
    ) result1,
    (   
        select bc2.cid, min(bc2.last_modified_date) as last_modified1 from table_a bc2
        where bc2.state != 7
        AND bc2.cid in 
        ( 
             select cid from table_a TA, table_b TB
             where TB.name not like '% IS' and TA.state != 7
             AND TA.service_type = 1
             AND TA.username is not null
             and TA.bctid = TB.bctid
         )
         and bc2.service_type = 2
         group by cid
    ) result2
    where result1.cid = result2.cid
    and result1.last_modified1 < result2.last_modified2

I think you can push up the predicates "where result1.service_type = 1 and result2.service_type = 2". After that you can do what Denis advised.