Use Aliases in Joins

David Clement

September 2004

Oracle versions 7-8

Table aliases should be used in joins, because they provide a performance improvement.

A join coded without aliases looks as follows.

          select last_name || ', ' || first_name, trw_rpt_seq, crd_rqt_dt
          from crd_rqt_accts, members
          where cra_member_id = member_id
          order by 3, 1, 2;

For this SQL statement, the semantics check of the parse step has to issue numerous recursive SQL statements to ascertain which table each column belongs to. A human being, looking at the statement for the first time, does not know what table trw_rpt_seq belongs to, and the SQL parsing engine has exactly the same problem.

With aliases, the code would look as follows.

          select m.last_name || ', ' || m.first_name, 
            c.trw_rpt_seq, 
            c.crd_rqt_dt
          from crd_rqt_accts c, 
            members m
          where c.cra_member_id = m.member_id
          order by 3, 1, 2;

The performance advantage of coding the statement this way is that just by reading the code the parsing engine knows table each column is supposed to belong to, and can perform the semantics check much more rapidly. The shorter the alias, according to some Oracle performance engineers, the faster the look-up.

An even more important reason to use aliases in joins is that they make code much easier to read, understand, and optimize.