Oracle sql derived table - optional aliasing

Question

I came across a client's query yesterday, it is something like:

select count(*) as countall,
          person,
          location 
  from (select custid,
               min("Date") as theDate,
               person,
               data.location  
        from data join 
             customer on customer.customerid = custid 
        where status = 1 
          and custid <> -1 
       group by custid,
                person,
                data.location)  --[NO ALIAS]
   group by person,location

I don't have a lot of hours in Oracle, but in MS SQL this would not fly, to my knowledge. Any time I have used a derived table it throws an error, so to encounter scenarios such as this piques my interest. I couldn't find anything to explain it on the interwebs, hopefully somebody can explain the scenarios where aliasing for derived tables is optional and when it is not.

Aliasing is not required. It is needed, however, if you need to refer to it. — , Apr 07 '16 at 19:48
This will work. Aliasing is optional, but recommended. Not really necessary in the code you posted, but recommended if there are tables outside the inline view so that joins can be used and it is mandatory in the case that column names are common or ambiguous. — T Gray, Apr 07 '16 at 19:48
This differs from platform to platform. In Oracle subselects (derived tables) do not require what you call an alias (in fact it's called _correlation name_). I'd say use them always as best practice. — mustaccio, Apr 07 '16 at 19:48
+ for "correlation name", I am trying to understand platform-specific nomenclature — n8., Apr 07 '16 at 20:21

Brino · Accepted Answer · 2016-04-07T20:04:06.127

You only need to alias, when you are referencing a column that is not uniquely defined. This means that the column exists in more than one table/derived table. A reference could be in the select statement, or a join. If all columns are unique, then you do not need an alias.

I prefer to alias all of the time for clarity, and because it helps with Intellisense in PL/SQL.

--ALIAS needed, because the 'a' column referenced is not unique
--this will throw an error
select a, a, b, c
  from (select 'A1' as a, 'B1' as b, 'C1' as c from dual),
       (select 'A2' as a from dual);
--this will not throw an error
select t1.a, t2.a, b,c
  from (select 'A1' as a, 'B1' as b, 'C1' as c from dual) t1,
       (select 'A2' as a from dual) t2;
;

--ALIAS not needed for join, because all referenced columns are unique
select a, b, c, d, e, f
  from (select 'A' as a, 'B' as b, 'C' as c from dual)
  join (select 'D' as d, 'E' as e, 'F' as f from dual)
    on a = d;

--ALIAS needed for join, because the 'x' column referenced is not unique
--this will throw an error
select a
  from (select 'A' as a, 'B' as b, 'C' as c, 'X' as x from dual)
  join (select 'D' as d, 'E' as e, 'F' as f, 'X' as x from dual)
    on x = x;
--this will not throw an error
select a
  from (select 'A' as a, 'B' as b, 'C' as c, 'X' as x from dual) t1
  join (select 'D' as d, 'E' as e, 'F' as f, 'X' as x from dual) t2
    on t1.x = t2.x;

score 1 · Answer 2 · answered Apr 07 '16 at 19:47

In this instance, you are selecting all columns from your subquery and hence the subquery was written with no alias .

If you were to join this subquery with another table or perhaps another subquery, you would want to alias so that you can reference the join columns using the defined alias.

Oracle sql derived table - optional aliasing

2 Answers2

Linked