Let's go to the mailbag!

In response to this post, Chris writes:

Jeff, I just got done reading your VB posts. I love those sarcastic posts you do.

Anyway, my coworker and I disagree on something, and I'd like to consult you to "settle an argument". At work, I'm told to not use semantic table aliases. Just like you said, I'd normally use ct.OrderID for CustomerTransactions.OrderID, but instead I'm told to use a.OrderID.

I have to use a, b, c, etc at work because that's just what they do here and I'm not going to argue about it with anybody. But as a personal disagreement with my coworker, he thinks that naming the tables a, b, c in order that they were joined is more clear for debugging than actually naming them something based off of the table name, in other words, semantic.

To clarify, he'd do:

SELECT b.OrderID, a.CustomerID, c.OrderDetailID
FROM
Customer a
JOIN Order b on a.CustomerID = b.CustomerID
JOIN OrderDetail c on b.OrderID = c.OrderID
WHERE
{ some conditionals here }

That's a contrived example to demonstrate our use of table aliases, not a real production example.

Will you provide some guidance on this?

Chris, I personally take your side on this. Naming anything based on the "order in which things are done" is a bad idea, in my opinion, because often we need to change the order in which things are done and/or remove or add some steps as we revise and edit our code. If the join no longer needs table B, do we edit the entire thing to "cascade down" table alias C to B, D to C, and so on?

I think it's pretty clear that this:

SELECT CustTotals.CustomerID, CustTotals.Amount, ....

is eaiser to read and understand in a large SQL statement rather than this:

SELECT D.CustomerID, D.Amount, ....

It's all subjective, of course, but that's my take. Granted, the alias "D" is shorter than "CustTotals" and easier to type, so sometimes I might even just use "C" or "CT" for "customer totals". At least there is some meaning behind it and at a glance it is easy to interpret in my opinion. It might be quite confusing to alias "Customers" as "B" and "Budgets" as "C" just because they happened to be joined that way!

Of course, I do use meaningless aliases as well (often, just "A" or "tmp" or even "X") but usually only when there is a single derived table that I am "wrapping". i.e., something like this:

select a.*
from
  ( select customerID, transactionID, Rate, Units, Rate*Units as Amount 
    from Transactions ) a
where
  a.Amount > 0 

In that case, putting a meaningful alias on the inner SELECT would be redudant, since it would be the same as the outer SELECT; we are just using the derived table to make it clearer or shorter or because we need to reference an inner calculation more than once. So, in those cases, I tend to use just a quick one-letter alias. If I were to join that inner derived table to another table, though, I would give them both meaningful aliases to keep things clear:

select  
  Cust.CustomerID, Cust.Name,  Trans.TransactionID, 
  Trans.Rate, Trans.Units, Trans.Amount
from
  Customers Cust
left outer join
  ( select transactionID, Rate, Units, Rate*Units as Amount from Transactions ) Trans
  on Cust.CustomerID = Trans.CustomerID
where
  Trans.Amount > 0 

Does anyone else have any other feedback on this issue? Any other conventions that work well for you? Let us know!

see also:

• Thinking Set-Based .... or not?
• What I learned about SQL from using Access
• How to be an Effective Report Writer
• Sometimes the problem isn't the code. It's the specs.
• Alias Naming Conventions
• Always Use Parameters. Even if you don't use Stored Procedures.
• Programming is a funny thing ....
• SQL Problem Solving: Identify ... and then Simplify!