Is there a way to give a subquery an alias in Oracle 11g SQL?

Is there a way to give a subquery in Oracle 11g an alias like:

select * 
from
    (select client_ref_id, request from some_table where message_type = 1) abc,
    (select client_ref_id, response  from some_table where message_type = 2) defg
where
    abc.client_ref_id = def.client_ref_id;

Otherwise is there a way to join the two subqueries based on the client_ref_id. I realize there is a self join, but on the database I am running on a self join can take up to 5 min to complete (there is some extra logic in the actual query I am running but I have determined the self join is what is causing the issue). The individual subqueries only take a few seconds to complete by them selves. T开发者_Go百科he self join query looks something like:

select st.request, st1.request
from
    some_table st, some_table st1
where 
    st.client_ref_id = st1.client_ref_id;

You can give a query a name or alias with CTE’s (Common Table Expressions) aka WITH clause aka by Oracle as Subquery Factoring:

WITH abc as (select client_ref_id, request from some_table where message_type = 1)
select * 
from abc
    inner join 
    (select client_ref_id, response  from some_table where message_type = 2) defg
       on abc.client_ref_id = def.client_ref_id;

I don't have an Oracle instance to test with, but what you posted should be valid ANSI-89 JOIN syntax. Here it is in ANSI-92:

SELECT *
  FROM (SELECT client_ref_id, request 
          FROM SOME_TABLE 
         WHERE message_type = 1) abc
  JOIN (SELECT client_ref_id, request 
          FROM SOME_TABLE 
         WHERE message_type = 1) defg ON defg.client_ref_id = abc.client_ref_id

Your query should be fine.

An alternative would be:

select abc.client_ref_id, abc.request, def.response
from   some_table abc,
       some_table def
where  abc.client_ref_id = def.client_ref_id
and    abc.message_type = 1
and    def.message_type = 2;

I wouldn't be surprised if Oracle rewrote the queries so that the plan would be the same anyway.

In SQL standard you can define alias for subquery in this way

SELECT Temp.patente, Temp.SalarioProm
FROM ( SELECT A.aid aid, A.patente AS patente,
AVG (E.esalario) AS SalarioProm
FROM Aircraft A, Certified C, Employees E
WHERE A.aid = C.aid
AND C.eid = E.eid
AND A.rangocrucero > 1000
GROUP BY A.aid, A.patente ) AS Temp

Where Temp is the alias for the subquery

In Oracle, you can use the clause WITH, especially when the subquery is complex and use a lot of temporary space.

In Oracle, the following syntax works fine

SELECT Temp.patente, Temp.SalarioProm
FROM ( SELECT A.aid aid, A.patente AS patente,
AVG (E.esalario) AS SalarioProm
FROM Aircraft A, Certified C, Employees E
WHERE A.aid = C.aid
AND C.eid = E.eid
AND A.rangocrucero > 1000
GROUP BY A.aid, A.patente ) Temp