Questions tagged [alias]

Tables and nested subqueries may be aliased within a SQL query, helping with readability, and allowing a table to be referenced more than once with different join conditions. An example of a query with sub-query and table aliases below.

select a.foo
      ,b.bar
      ,b.BarCount
  from (select b.bar
              ,count (*) as BarCount
          from BarTable b
          join OtherTable o
            on b.OtherTableID = o.OtherTableID
         group by b.bar) b
  join Foobar a
    on a.bar = b.bar

Table Foobar is aliased as a, and the nested subquery has an alias b. A nested subquery has a separate name-space for aliases as can be seen in the use of the alias b in both the parent and child queries to note different things. Note that this is possible, but not necessarily recommended as it can be confusing to people reading the code. It may also be desirable to use more meaningful aliases.

SQL statements may have an optional AS statement for aliasing, e.g.

select [...]
  from Foo       as a
  join Bar       as b
    on a.BarID = b.BarID

Aliases are necessary if a table is to be included more than once with different join conditions in the same query. A common example of this is a self-join such as the query below.

select par.BusinessUnitName    as ParentBusinessUnit
      ,chl.BusinessUnitName    as ChileBusinessUnit
  from BusinessUnit par
  join BusinessUnit chl
    on chl.ParentBusinessUnitID = par.BusinessUnitID

In this case the table BusinessUnit cannot be specified for both sides of the join without disambiguating which side a given expression is referring to. Aliases allow this to be specified.