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;

You haven't figured out it's the self-join. Make an identical copy of the table, indexes and statistics and see if you get the same timing. The better question would have been, "Help me tune this query" and posted the FULL query, and the explain plan. — Stephanie Page, Jun 17 '10 at 22:22

jmoreno · Accepted Answer · 2020-03-24T22:09:51.773

30

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;

edited Mar 24 '20 at 22:09

answered Jun 17 '10 at 04:49

jmoreno

12,752
4
60
91

1

What is the technical term for the "WITH abc as (select...)" part? If one wanted to research on its capabilities. – SeaBass May 29 '15 at 15:23
2

@SeaBass: Common Table Expressions, frequently abbreviated to CTE (also frequently used as the name/alias, although I dodn't do so this time). Note that both "tables" could have been done as part of the CTE, I just wanted to show both options, so did one one way and the other, the other. – jmoreno May 29 '15 at 15:25
@SeaBass Oracle has another term for CTEs: you can find specific Oracle resources for this with “Subquery Factoring” (or Recursive Subquery Factoring) – bugybunny May 06 '19 at 12:32

score 8 · Answer 2 · answered Jun 16 '10 at 23:40

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

score 3 · Answer 3 · answered Jun 17 '10 at 01:21

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.

answered Jun 17 '10 at 01:21

Jeffrey Kemp

59,135
14
106
158

This solution is what is taking so long to complete on the database I am using. The statement 'abc.client_ref_id = def.client_ref_id' causes the query to go from a few seconds to take over an hour to complete. – Matt Pascoe Jun 17 '10 at 15:33
You have to be very careful about timing. Are you talking about last row timing? or just first rows? Certain query plans can produce 1 row very fast (a second) but would take a year to produce the last row. Whereas others would get the last row in a minute. – Stephanie Page Jun 17 '10 at 21:57
@Matt, post the query plan - it's no good mucking around with the SQL when we don't know what's causing the performance issue. – Jeffrey Kemp Jun 18 '10 at 00:53

score 0 · Answer 4 · answered Nov 01 '21 at 13:00

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

Please format code as code, as the other answers are. You can use the [editing help pages](https://stackoverflow.com/editing-help) to assist you. — David Buck, Nov 01 '21 at 13:15
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Nov 01 '21 at 13:45

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

4 Answers4

Linked