SQL Table / Sub-Query Alias Conventions

Question

I've been writing SQL for a number of years now on various DBMS (Oracle, SQL Server, MySQL, Access etc.) and one thing that has always struck me is the seemingly lack of naming convention when it comes to table & sub-query aliases.

I've always read that table alises are the way to go and although I haven't always used them, when I do I'm always stuck between what names to use. I've gone from using descriptive names to single characters such as 't', 's' or 'q' and back again. Take for example this MS Access query I've just written, I'm still not entirely happy with the aliases I'm using even with a relatively simple query as this, I still don't think it's all that easy to read:

SELECT stkTrans.StockName
    , stkTrans.Sedol
    , stkTrans.BookCode
    , SUM(IIF(stkTrans.TransactionType="S", -1 * stkTrans.Units, 0)) AS [Sell Shares]
    , SUM(IIF(stkTrans.TransactionType="B", stkTrans.Units, 0)) AS [Buy Shares]
    , SUM(IIF(stkTrans.TransactionType="B", -1 * stkTrans.Price, 0) * stkTrans1.Min_Units) + SUM(IIF(stkTrans.TransactionType="S", stkTrans.Price, 0) * stkTrans1.Min_Units) AS [PnL]
    , "" AS [Comment]
FROM tblStockTransactions AS stkTrans 
INNER JOIN (SELECT sT1.BookCode
                    , sT1.Sedol
                    , MIN(sT1.Units) AS [Min_Units]
            FROM tblStockTransactions sT1
            GROUP BY sT1.BookCode, sT1.Sedol
            HAVING (SUM(IIF(sT1.TransactionType="S", 1, 0)) > 0
            AND SUM(IIF(sT1.TransactionType="B", 1, 0)) > 0)) AS stkTrans1 ON (stkTrans.BookCode = stkTrans1.BookCode) AND (stkTrans.Sedol = stkTrans1.Sedol)
GROUP BY stkTrans.BookCode, stkTrans.StockName, stkTrans.Sedol;

What do you think? Thought I would throw it out there to see what everyone else's feelings are about this.

Answer 1

I don't know of any canonical style rules for naming table/query aliases across databases, although I understand that Oracle recommends abbreviations of three to four characters.

I would generally steer clear of single letter abbreviations, except where the query is sufficiently simple that these should be completely unambiguous to anyone having to maintain the code - typically no more than two or three tables per query.

I would also generally avoid long alias names that conform to the general style of your database table-naming conventions, since it can become unclear what is a database table name and what is an alias.

In the example provided, the alias sT1 inside the inline view is utterly unnecessary, as there is only one table being accessed within that inline view. That leaves one table being joined to one inline view (based on the same table) in the query - in these circumstances, I would use s as the alias for the table, and s1 as the alias for the inline view (to indicate that it was querying the same underlying database table).