Is table aliasing a bad practice?

Question

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:

SELECT t1.id, t2.stuff 
FROM 
              someTable    t1 
   INNER JOIN otherTable   t2 
      ON t1.id=t2.id
;

But... Why is this acceptable in stored procedures and such? It seems like all it does is harm the readability of the statement while saving an extremely minor amount of time. Is there any functional or logical reason to do this? It seems to add ambiguity rather than remove it; the only acceptable reason I can see for using this format is if you were adding a semantically meaningful alias -- for example, FROM someTable idsTable -- when the table name isn't descriptive enough.

Is table aliasing a bad practice or is this just a misuse of a helpful system?

Answer 1

Table aliasing is a common and helpful practice.

• It saves you keystrokes when referencing columns anywhere in your query.
• It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
• It is even required when you join a table to itself or when you join to the same table multiple times. This is so the query optimizer knows which table you are referencing when you mention a column.

The following reporting extract illustrates all of the above points nicely:

INSERT INTO reporting.txns_extract
SELECT 
    -- 30+ columns snipped
    -- 
    -- Would you want to type out full table names for each 
    -- column here?
FROM 
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
    INNER JOIN  
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
    INNER JOIN  
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
    LEFT OUTER JOIN
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
    LEFT OUTER JOIN
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
    LEFT OUTER JOIN
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
    INNER JOIN
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
    INNER JOIN
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
WHERE
    -- ... and in the WHERE conditions here?
        ft_cdi.transaction_type <> 'Payment'   -- alias tells me this table is not for payments
    AND ft_cdi.status = 'Approved'
    AND ft_pmt.transaction_type =  'Payment'   -- alias tells me this table is for payments
    AND ft_pmt.status = 'Approved'
    AND ft_cdi.last_user_date >   ft_last_user_date_begin
    AND ft_cdi.last_user_date <=  ft_last_user_date_end
;

Answer 2

I think using aliases helps the readability of a query if the table names are long or so similar to each other that someone reading it quickly might mistake them. Do you think that this...

SELECT Really_long_table_name.ID,
       Even_longer_table_name_than_before.Name,
       Even_longer_table_name_than_before.Description,
       Even_longer_table_name_than_before.State
FROM   Really_long_table_name
       INNER JOIN Even_longer_table_name_than_before
               ON Really_long_table_name.ID = Even_longer_table_name_than_before.ID
WHERE  Really_long_table_name.Department = 'Whatever' 

is more readable than this?

SELECT a.ID,
       b.Name,
       b.Description,
       b.State
FROM   Really_long_table_name a
       INNER JOIN Even_longer_table_name_than_before b
               ON a.ID = b.ID
WHERE  a.Department = 'Whatever' 

Depending on what you use as table aliases, it can make the query much simpler for a person to read and understand.

Answer 3

Table aliasing (for the sake of shorter table names) isn't bad practice.

I normally use it when the tablenames are long and then only use the alias that makes sense:

SELECT tTable.stuff FROM track_table tTable;

If you want to improve readability, you can use the AS keyword:

SELECT tTable.stuff FROM track_table AS tTable;

But, as you get used to the syntax, it's not needed.

Answer 4

This is a question about "bad practice". The answers seem to be about "Saving keystrokes".

Personally, my coding speed is limited by my thought speed, not by my typing speed. I find code with lots of table aliases much harder to read than code that uses the table names themselves. Table aliases add another level of indirection.

However, most programmers do use table aliases (although I don't). Some "SQL development environments" make this very easy to do, and some teachers teach table aliases automatically, especially as part of learning the "Join" syntax.

Using table aliases is not bad practice, but sometimes I have to go through code and replace the aliases with the original table names in order to understand what's going on.

Answer 5

You can use this to significantly increase the readability of your queries. Rather than using a short alias, use your alias to describe the data that you are joining to, for example

SELECT
    transaction.unique_id,
    authorisingUser.name AS authorising_user_name,
    requestingUser.name AS requesting_user_name
FROM transactions AS transaction
JOIN users AS authorisingUser
    ON authorisingUser.user_id = txn.authorising_user_id
JOIN users AS requestingUser
    ON requestingUser.user_id = txn.request_user_id

While using extremely short aliases (like a or t1) can make a query difficult to read as you need to go and find the alias to look up what the alias means, a well-named alias can often make a query more readable than just using the table names.

Answer 6

My first job that required interacting with SQL regularly involved migrating tens of thousands of lines of legacy queries after dozens of columns and tables had been moved and renamed. This process took years. This process could have taken at most a couple of days had no one used table aliases (or at least avoided them when they were unnecessary).

How?

Consider the following query.

select 
employeeid,
a.cust,
b.date,
c.first_name,
c.last_name
on employee as a
inner join customers as b
using(transactionid)
left join transactions c
on c.transactionid = b.transactionid

Don't think too hard about it. I made it up and what it does isn't important. Just imagine you were told transactions.first_name and transactions.last_name have moved to transactions_table.firstname and transactions_table.lastname respectively. Meanwhile, transactions_table is indexed on transaction_datetime_id instead of transactionid. Sure, you could spend a couple of minutes backtracking this new query and swapping out transactions for transactions_table, the join involving c.transactionid for c.transaction_datetime_id, and finally firstname and lastname for first_name and last_name respectively.

Now consider the following query.

select 
employee.employeeid,
employee.cust,
customers.date,
transactions.first_name,
transactions.last_name
on employee
inner join customers
on employee.transactionid = customers.transactionid
left join transactions
on transactions.transactionid = customers.transactionid

Now imagine making the same changes. It should be immediately clear you can just run a few blind regular expression based substitutions on the query.

• transactions.transactionsid => transactions_table.transaction_datetime_id
• transactions.first_name => transactions_table.firstname
• transactions.last_name => transactions_table.lastname
• (?<![A-Za-z0-9.])transactions(?![A-Za-z0-9]) => transactions_table

I'm not the first person to encounter this problem and I won't be the last (the group I met at a company I joined later went through the same painstaking process).

If you want to blame me for being new to heavy SQL at the time, then please note that my predecessor was a 4 year full-time SQL veteran and was the same person that wrote these queries that needed updating. He was already a year into the process of migrating them when he left and I took over. Tremendous resources are wasted because of unnecessary table aliases. Seriously.