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

Question

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. The 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;

Answer 1

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;

Answer 2

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

Answer 3

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.

Answer 4

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