Enhancing the readability of your code: Table aliasing in sql

Aliasing tables can be a topic that database developers feel very strongly about. I'm actually one of the developers that do use aliases, but there's a right way to do it, and there's a wrong way.
First, the wrong way (using the pubs sample database):

select
   c.Au_id
   ,c.au_lname
   ,c.au_fname
   ,a.title
   ,a.title_id
   ,b.royaltyper
from titles a
   join titleauthor b
      on a.title_id = b.title_id
   join authors c
      on c.au_id = b.au_id

Don't laugh. I've actually seen some sql done exactly like this. Reading and understanding it later on takes far longer than it should (now what did b stand for again?).

Most people would agree that the aliasing in the above example is terrible, and most sql isn't written that way. However, I'd say about 90% of sql is written like the example below:

select
   au.Au_id
   ,au.au_lname 
   ,au.au_fname
   ,ti.title
   ,ti.title_id
   ,ta.royaltyper
from titles ti
   join titleauthor ta
      on ti.title_id = ta.title_id
   join authors au
      on ta.au_id = au.au_id

This is an improvement over using aliases that have no relation whatsoever with the tablename. But in this particular sql statement, there's no reason to alias your tables names - it would just save a few keystrokes, at the cost of making it much less readable. Here's what I would do.

select
   authors.Au_id
   ,au_lname 
   ,au_fname
   ,title
   ,titles.title_id
   ,royaltyper
from titles
   join titleauthor
      on titles.title_id = titleauthor.title_id
   join authors
      on titleauthor.au_id = authors.au_id

In this case - just a simple select statment - it really doesn't make any sense to use aliases.

It's in more complex sql, with cross database joins, subqueries, and derived tables that you really want to use aliases in order to make your code more readable. For instance, the following from clause contains tables from 4 different databases:

from BookingStg.dbo.TSData TSData
join BookingStg.dbo.Booking Booking
  on Booking.BookingID = TSData.BookingID
join Domain.dbo.BookingWin BookingWin
  on TSData.DateOfStayTimeID = BookingWin.DateOfStayTimeID
left join WarehouseFactStore.dbo.Customer_Dim Customer_Dim
  on Customer_Dim.TUID = Booking.TUID
  and Customer_Dim.ProductID = TSData.ProductID
left join TServerImp.dbo.TLRPostalCode TLRPostalCode
  on TLRPostalCode.TLR = TSData.TLR
  and TLRPostalCode.ProductID = TSData.ProductID

When using tables from multiple databases, you alias the fully qualitifed names (i.e. DatabaseName.SchemaName.TableName) with just the tablename. Like this:

join BookingStg.dbo.Booking Booking

This cuts down of the length of table references, without obscuring them.

Also, in a situation where you're creating a derived table, you'll need to pick a name for it. Make sure it reflects what the table is used for. For instance, here I'm creating a derived table in order to group transactions, so the alias name reflects that:

from #TransactionAdFact
left join
  (
  Select
     TLR
     ,MinAdID= min(AdID)
  from  RetailOperations.dbo.OmTransaction OmTransaction
  join #BaseData
     on #BaseData.OmniProductMapping  = OmTransaction.OmProductID
  where
     TransDateKey between @StartDateKey and @EndDateKey
     and AdID is not null
  group by
     TLR
     ,OmTransaction.OmProductID
  ) GroupedTransactions
  on GroupedTransactions.TLR = #TransactionAdFact.TLR

Please, don't call the derived table something like Temp1. I see this all the time. It may save you the minute it would cost to think of a name that would be understandable to other people, but for the sake of the people who will be reading this code - take the minute, and make your code more readable.

Chosing the right tables aliases in SQL Server can either make your queries far more readable...or you can save yourself some thought and a couple keystrokes. Your choice.