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.
