3 Replies Latest reply on Jan 6, 2014 8:53 PM by Frank Kulash

    Using the same alias for two different tables in a select

    ScotM

      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.

        • 1. Re: Using the same alias for two different tables in a select
          Brian Bontrager

          There is no join, so Oracle will form a cartesian product of rows in the 2 tables.  These 2 tables have no columns named the same, so there is no need to use a table name (or alias) to distinguish which table the column comes from, so the alias is likely never used, avoiding any "ORA-00918: column ambiguously defined".

           

          It executes, but is the result meaningful?

           

          I don't think it is useful... I think you "got lucky".

          • 2. Re: Using the same alias for two different tables in a select
            ScotM

            Ok, possibly relevant, so I went back and made the example non-Cartesian as such:

             

            SELECT owner, Table_Name, COLUMN_name

             

              FROM All_Tab_Cols a,

             

                   All_Users     a

             

            WHERE owner = username

              AND Column_Name LIKE 'R%';

             

            and still Oracle doesn't squawk about using the table alias 'a' for two different tables.

            • 3. Re: Using the same alias for two different tables in a select
              Frank Kulash

              Hi,

               

              It looks like Oracle allows duplicate table alaises as long as it doesn't really need any alias at all.  For example, this works

              SELECT  a.dname

              ,       a.ename

              FROM    scott.emp   a

              JOIN    scott.dept  a   ON  a.ename > a.loc

              ;

              and it would work the same if you removed all the aliases.  Oracle can figure out that dname and loc must belong to the dept table, and that ename must belong to emp, without the aliases.

               

              Like Brian, I don't think this a useful feature.  I think it's confusing to use the same table alias twice in the same statement (e.g., if separate sub-queries); using the same alais twice in the same query is even worse.