I was transcribing a co-worker's query from one system to another and made a typo , such that I ended up with two tables in a select having the same alias - and the query worked!
I have reproduced it with system tables:
SELECT Table_Name
FROM User_Tab_Cols a,
All_Users a
WHERE Column_Name LIKE 'R%';
So, both User_Tab_Cols and All_Users are aliased with 'a' and the query executes (yes, Cartesian results, not what I am pointing out).
I had always thought that they would have to be unique and I haven't found any reference in the documentation that says that they must be unique.
So, that got me wondering about column alias and I found that
SELECT Table_Name b, COLUMN_name b, user_id b, username b
FROM User_Tab_Cols a,
All_Users a
WHERE Column_Name LIKE 'R%'
executes just fine.
So, my question is WHY? How is this useful? I was going to askTom, but he is always back logged on questions.
Thank you.