Tables and nested subqueries may be aliased within a SQL query, helping with readibility, and allowing a table to be referenced more than once with differnt 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.