Blog

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.

↑ Back to top
  1. This is one of my biggest pet peeves! Someone hands over a view or query to tune and/or debug that isn’t aliased or has fully qualified database and table name references throughout the entire query. Trouble is several of the BI tools that build queries either do not allow for you to alias tables when you include them in the schema or the schema developer fails to define them and the BI tool is forced to fully qualify the entire SQL statement to avoid the query failing because two tables share a column name.

  2. We actually have a commonly accepted dictionary of abbreviations that we use. I needs documented, however every query uses the exact same aliases for each table. It makes it really easy to look back at queries I wrote 6 months ago – si is always the Sites table.

  3. I just laugh when my mistakes lead to enlightening blog posts for you. :)

    Keep up the good work.

  4. Heh – well, I get tired of writing about my own mistakes, hahaha. It’s sustained me for years, but every now and then I have to break from that!

  5. @peschkaj: I like the idea of a standard dictionary for table abbreviations!

  6. I also prefer to quote all identifiers as well. Somewhat of a nervous tick I have (among others :)

  7. Pingback: Weekly Link Post 89 « Rhonda Tipton’s WebLog

  8. Brent, is the dbo. really necessary? Is there some reason why that should be included if every table in the database is dbo?

  9. David,

    See: http://msdn.microsoft.com/en-us/library/ms189915.aspx

    Microsoft recommends you at least owner-qualifying your object names.

    Executing User-defined Stored Procedures

    When executing a user-defined stored procedure (either in a batch or inside a module such as a user-defined stored procedure or function), we strongly recommend qualifying the stored procedure name with at least the schema name.

    The following example demonstrates the recommended method for executing a user-defined stored procedure.

    USE AdventureWorks;
    GO
    EXEC dbo.uspGetEmployeeManagers 50;

    -Or-

    EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;
    GO

    If a nonqualified user-defined stored procedure is specified, the Database Engine searches for the procedure in the following order:

    * The sys schema of the current database.
    * The caller’s default schema if executed in a batch or in dynamic SQL. Or, if the nonqualified procedure name appears inside the body of another procedure definition, the schema containing this other procedure is searched next. For more information about default schemas, see User-Schema Separation.
    * The dbo schema in the current database.

    Important:
    If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed if you use a nonschema-qualified name reference. For more information, see Creating Stored Procedures (Database Engine).

  10. Absolutely, great question. Every table in the database NOW is dbo, but that’s not necessarily true for the future. I’ve seen a lot of projects where a new round of developers or a new project will need to add an additional schema for security needs, and then it’s an absolute mess to go back and fix the code everywhere.

  11. Funny, I hate multiple letter alias or full table name alias. That is painful for me. Keeping it to a single letter is my preference. The important thing is to use an alias; the rest is personal preference. The ‘dbo’ prefix may be recommended but is pretty stupid on the face of it, bloats code and obscures meaning. Having an Oracle background I can’t bring myself to use it in this shop.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php