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