Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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. 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;
like image 1000
Matt Pascoe Avatar asked Jun 16 '10 23:06

Matt Pascoe


People also ask

Can we give alias to subquery in SQL?

An SQL alias is useful for simplifying your queries and making the query and its result more readable. This article explains why and how you can use aliases in your SQL queries. You can temporarily rename a table or a column by giving it another name. This is known as an SQL alias.

Can we use alias name in WHERE clause in Oracle?

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

How do you enclose a sub query?

Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.


2 Answers

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;
like image 188
jmoreno Avatar answered Oct 02 '22 00:10

jmoreno


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
like image 20
OMG Ponies Avatar answered Oct 02 '22 00:10

OMG Ponies