SQLServerPedia

When joining multiple tables together in a query, use aliases every time on every field.

Say we’ve got two tables, Customers and Salespersons.  The Customers table has a PreferredSalespersonID field that identifies who their normal sales rep is, and that lets us quickly grab the right salesperson when a customer calls in.  We want to display basic information about the customer and their salesperson:

SELECT CompanyName, Address, City, StateID, ZipCode, SalespersonName
FROM dbo.Customers
INNER JOIN dbo.Salespersons ON Customers.PreferredSalespersonID = Salespersons.ID
WHERE Customers.ID = 12345

That works great at first, so we put it in production and off we go.  Months later, someone decides to add an Address field to the Salespersons table.  Boom goes the dynamite - suddenly this query starts failing because SQL Server isn’t sure which City field we mean. We get errors like this:

Msg 209, LEVEL 16, STATE 1, Line 3
Ambiguous COLUMN name 'Address'.

To prevent this problem, always alias every single field.  My personal preference is to use an alias as short as possible, but long enough to explain what the alias points to.  Never alias tables with single letters starting with A, B, C, etc - it’s painful to read those queries.  Instead, consider aliasing the query like this:

SELECT cust.CompanyName, cust.Address, cust.City, cust.StateID, cust.ZipCode, sls.SalespersonName
FROM dbo.Customers cust
INNER JOIN dbo.Salespersons sls ON cust.PreferredSalespersonID = sls.ID
WHERE cust.ID = 12345

If I used one-letter aliases like “c” and “s”, it would still work, but I try to design every query as if I’m going to have to come back to it tomorrow and add three more tables.  If I come back and join more tables in, then the “c” and “s” might be confusing if those new tables also start with C or S.

Finally, when picking aliases, check other stored procedures and views to see if there’s already an aliasing standard in the database.  If the Customers table is already being aliased with “cmr” in other T-SQL code, then reuse that same alias even if it doesn’t make perfect sense.  The more consistent the code, the easier it is to jump from one T-SQL script to another with ease.