Bad habits to kick : inconsistent table aliasing

This is part of more than a decade of posts involving Bad Habits and Best Practices.
See the full index.

Today, I wanted to touch on a topic I've touched on before: aliasing.

Over the weekend, there was a twitter conversation between @unclebiguns and @jaybonk (it started with this post), and it reminded me of another bad habit I see quite a bit: inconsistent aliasing.

Now, in this very series, I talked about aliasing back in November. But, in that case, I was talking about choosing poor and meaningless aliases like a, b, and c – and the problems that can cause when someone needs to make sense of the query later.

In this case, I am talking about bad practices involving being inconsistent with the use of table aliases. I'll illustrate with two examples:

Only aliasing some of the tables in the query

This is one form I see a lot, where an alias is used for some tables, but not all. You end up having short forms for table references and then in other spots you have to spell out the entire name of the table every time.

SELECT
    o.OrderID,
    OrderDetails.ProductID
FROM
    dbo.Orders AS o
INNER JOIN
    OrderDetails
    ON o.OrderID = OrderDetails.OrderID
WHERE
    o.OrderID = 1;

If you have to maintain this query and it gets more complex later, you will be cursing yourself because you will have to write "OrderDetails" umpteen more times. Or you will be lazy, and also be guilty of:

Aliasing all tables, but not aliasing some of the columns

Another form of this pet peeve that I see is when the developer leads a wild goose chase, where we get to guess which table a column comes from. Of course the original coder knows what they were thinking, and they can get away with it because that column happens to only exist in one table mentioned in the query (at least for now).

 SELECT
    o.OrderID,
    ProductID,
    Quantity
FROM
    dbo.Orders AS o
INNER JOIN
    dbo.OrderDetails AS od
    ON o.OrderID = od.OrderID
WHERE
    o.OrderID = 1
    AND ProductID IN (4,5,6,7,8);

The problem is that if another table is introduced to the query (say, the Products table, to get the name of the products ordered), now you will have to go through all the column references to avoid ambiguous column name errors.

Summary

Basically I am against any type of shortcut where changing the query later will be much more cumbersome than it should be. For the sake of future maintenance, write your queries clearly, concisely, and consistently. As I've said before, I am not suggesting that you have to alias like I do, or that you have to alias at all… but if you're going to use table aliases, use them the same way every time.

This is part of more than a decade of posts involving Bad Habits and Best Practices.
See the full index.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am an architect at Wayfair.

20 Responses

  1. Stephanie Sullivan says:

    Well that didn't show up very well once it was published! All AS statements, table names, table aliases, ON statements etc should be all sit one under the other.

  2. Stephanie Sullivan says:

    mjswart- I completely agree
    Coming from a maths background and kind of falling into SQL, I try to keep all my production code consistently formatted and well commented.  I've found that most developers don't put as much effort into their code.  This makes it really difficult to read and understand the code they produce when it comes round to amending it.  
    I think that tables should always be aliased and joins stated explicitly.  Also, because I write a lot reports, I try to put user friendly column aliases whenever possible as it saves time down the line.  Since I will often have to pull off many different existing and calculated columns, I've tried to develop a consistent format that makes it easy to see my progress as I'm developing the code.  Aliasing columns is pivotal for this.  I've found that explicitly stating AS 'Column' in the same location for each column is the most readable (for me anyway).
    My work usually ends up looking like this:
      /*
      Example code
      I don't have SSMS on my PC
      at home so this han't been validated
      */
    SELECT
    T1.Name                          AS   'Customer Name',
    T1.Address1 + T1.PostCode        AS   'Customer Address',
    T2.NOK_Name                      AS   'Next of Kin Name',
    T2.Add + T2.PostCode             AS   'Next of Kin Address',
    COUNT(*)                         AS   'Calls re: Payment,
    SUM( CASE WHEN HOUR(TD.Date)>18
             THEN 1
        END)                        AS   'Out of hrs calls re: Payment'
    FROM        Table1      T1  
    INNER JOIN  Table2      T2       ON   T1.TableID  =  T2.Table1_ID
    LEFT JOIN   Table3Daily TD       ON   T1.TableID  =  TD.Table1_ID
    WHERE       TD.EntryTypeID       =    18 –Call re:Payment code
    GROUP BY
    T1.Name                          ,
    T1.Address1 + T1.PostCode        ,
    T2.NOK_Name                      ,
    T2.Add + T2.PostCode

  3. Piotr Rodak says:

    Thanks Aaron,
    It just happened that I was also collecting some bad coding standards recently for my blog, and bad aliasing is amongst them. I find this extremely annoying and waste of money/time to have to dig through all views and tables to find the one a column belongs to. There are no good tools yet that would work as well as intelisense in Visual Studio.

  4. Madhivanan says:

    Thanks Aaron
    I realised that I was using old version of BOL
    Sorry for the inconvenience caused

  5. AaronBertrand says:

    Ok, so on 1., we're agreed.  
    I still can't agree with you on 2.  Are you really coding SELECT statements with so many contiguous <alias>=<expression> lines that they fill the screen, and do you really assume it's an UPDATE without scrolling up to check?  I guess we must code very differently but my SELECTs are not composed completely of expressions… and UPDATEs that use other columns rather than variables are pretty rare, as I'm not a big believer in maintaining multiple aspects of information that could otherwise be derived at query time (which such expressions would).  
    And on 3., I looked at this URL:
    http://msdn.microsoft.com/en-us/library/ms143729.aspx
    Replacement certainly does include:
     alias = expression
    So I'm not sure which version of BOL you are looking at, but I can assure you that SQL Server will continue to support this syntax, at least for three or so more versions.

  6. Madhivanan says:

    Aaron,
    1 Agreed
    2 Assume you have SELECT statement with lot of expressions and you are using alias names
    SELECT
    <alias>=<expression>,
    <alias>=<expression>,
    <alias>=<expression>,
    .
    .
    .
    .<alias>=<expression>,
    <alias>=<expression>,
    <alias>=<expression>
    FROM
    your_table
    If it is in a procedure and part of a big code, when you parse thru eyes, they are more like UPDATE statemnet than SELECT
    3
    as per BOL
    Deprecated feature : 'string_alias' = expression
    Replacement : expression AS alias
                 expression AS [alias]
    But I guss it may support
    string_alias = expression
    and deprecate (usage of single quote)
    'string_alias' = expression
    But not sure why string_alias = expression is not specified as one of the replacements

  7. AaronBertrand says:

    Sorry Madhivian, I don't have a 2005 version of Books Online installed.  Can you post a real URL please?
    Anyway, as for your reasons why I "need" to change it to AS:
    1. I have a lot of code that uses various other things that aren't ANSI standard.  If I removed all of the code that wasn't ANSI standard I'd have a lot of modules that no longer work, and it would be a wasted exercise anyway.  I am not of the Celko mindset, where I'm worried that we will switch to Oracle tomorrow and DB2 next Wednesday.
    2. I have never confused a SELECT statement with an UPDATE statement.  Can you elaborate how this is a problem?
    3. I would be absolutely shocked if this suddenly stopped working.  That will break a LOT of code and Microsoft would need to have a very, very, very good reason to implement this kind of restriction that breaks backward compatibility.
    I still think it is much more useful to have the column alias at the beginning of the line than the end.  But you are more than welcome to have to look for the column aliases at the end of the line.  As I said earlier in these comments, this really is quite subjective.

  8. Brian Tkatch says:

    @Madhivanan
    Because comma is such an important character in SQL, i don't think it is missed. Also, because i nearly always put each COLUMN name on it's own line, i don't have that issue. Aliases go on the same line.
    I believe format with tabs and spaces to be far superior than the AS.

  9. Madhivanan says:

    Aaron,
    If you use
    alias=(complex expression)
    You need to change it to
    (complex expression) as alias
    Reasons
    1 Your preference is not ANSI standard
    2 It looks like a update statement where expression is copied to a column
    3 Most importantly, SQL Server will not support it in future release
    See the point specified at BOL at the link ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/c10eeaa5-3d3c-49b4-a4bd-5dc4fb190142.htm

  10. Madhivanan says:

    Brian,
    Did you read part 1 of my article?
    There is a change you may get different result if comma is omitted between the columns
    Compare the resultsets
    use northwind
    select employeeid,orderdate,shippeddate,shipcity from orders
    select employeeid,orderdate,shippeddate shipcity from orders

  11. Brian Tkatch says:

    Oh, i forgot about =. I switch between Oracle and SQL Server, so i do not use that.
    Nothing major on the format, just wrap and put the alias after the end:
    SELECT
    Moo,
    Cow,
    CASE Hoof
    WHEN 4 THEN 42
    WHEN 2 THEN 17
    END Hoof_Code,
    FROM
    SomeTable
    WHERE
    EXISTS
    (
    SELECT
    *
    FROM
    Dual
    );

  12. AaronBertrand says:

    Brian, on your second point, I'm not sure if we agree or not.  I prefer this for column aliasing:
    Alias = (complex expression)
    vs.
    (complex expression) AS alias
    The latter I find tedious, and since you say you hate AS for column aliases, I'm kind of surprised that you have some way to line wrap the latter format using AS so that it is easier to find the alias names.  Can you show an example?

  13. Brian Tkatch says:

    >I am a fan of using AS for table aliases
    I never use it. But on TABLE s it bother me less.
    >this can be tedious when you have complex expressions defining column output.
    Not if the lines are wrapped manually.

  14. AaronBertrand says:

    I am a fan of using AS for table aliases, but certainly not for column aliases.  In that case I'd much rather have the column names align on the left, than have to scan each line to find the alias at the end – this can be tedious when you have complex expressions defining column output.

  15. Brian Tkatch says:

    @Madhivanan
    Nooooooooooooooooooooooooooooooooooooo
    I *hate* AS. It is redundant. It adds clutter. It is reminiscent of some code generator.
    IOW "AS" is the opposite of what SQL stands for.

  16. mjswart says:

    Not too long ago, I saw an alias for a table named GRADE_OBJECT that was just asking for trouble. (And it did indeed create trouble before too long).

  17. Armando Prato says:

    I call it "lazy coding" and it drives me crazy too

  18. Noel says:

    Thanks Aaron, couldn't agree more. It's too bad you often see queries like your examples in books, including SQL Server exam prep books. Of course, there seems to be a social aspect to this as well, a belief that elite developers write sloppy code. I've encountered a fair amount eye rolling and such for trying to keep code consistent and readable… I can't help it, I started out in accounting!

  19. Brian Tkatch says:

    >Only aliasing some of the tables in the query
    I don;t see an issue with that. Perhaps convenience, but its really a case by case judgment. If the second coder does not want to type it out, he can alias it himself!
    >Aliasing all tables, but not aliasing some of the columns
    This indeed is a big problem. Being explicit about scope is always a good thing. My rule is, if there is more than one line in the FROM clause, every COLUMN must be referenced with its TABLE.