25

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?

4
  • 8
    When you've written a few thousand lines of SQL, you'll appreciate the saved typing. This is a case where, used with care, you can buy increased productivity for little or no cost in maintainability. Sep 20 '11 at 21:16
  • 6
    Most every query I've written that started with one table eventually grew to encompass more tables ("This is great, but can you add Foo?"). Aliasing every column up front will simplify your life.
    – billinkc
    Sep 21 '11 at 12:02
  • The only good thing I can see about this very common practise of aliasing all tables in all queries is that sometimes a creative developer manages to slip a naughty word into the code!
    – NeedHack
    Dec 29 '14 at 15:16
  • Why not just select id, stuff from someTable natural join otherTable? Aug 2 '18 at 22:33
40

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
;
9
  • 2
    Aliases are more readable for people who have written and read a lot of SQL. They're less readable for new database developers, but I think it's a hurdle they have to clear sooner or later anyway. Sep 20 '11 at 18:15
  • 7
    I heartily recommend meaningful aliases. It is really nice to see an example with meaningful aliases instead of t1, t2... or a, b, b, c, d, e.... It can get really confusing when you get aliases like employees a, addresses b, accounts c, billing d, customers e.
    – BillThor
    Sep 21 '11 at 0:39
  • 4
    I think it is also important to use the alias on every column refernce for easier maintainibilty, too. Sure only one table has the field named xyzjunk, but which one? When you write complex reporting queries, it is helpful to always know where your fields came form.
    – HLGEM
    Apr 26 '12 at 20:13
  • 1
    It is also required when you join to a derived table as well.
    – HLGEM
    Apr 26 '12 at 20:17
  • 1
    Overall i agree, except maybe the short names. Aliases are most usefull when you are joining the same time several times, ie. you have a person table and are joining it twice for a Message table, where one persone is the Sender and another one is the Recipient. On the other hand, using short aliases like cs, bil or fe is in my opinion bad practice. It doesn't help towards a selfdocumenting code and makes it harder for a new developer to approach and mantain the same code, or even if you return to it after longer time. Short(convenient) != good Jul 8 '14 at 13:47
13

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.

1
  • 2
    I always want to hunt down and kill developers that don't alias their tables (well not literally). That first example makes my eyes bleed. And somehow when they do this they don't make their code so that I can view it without scrolling too (just like you did).
    – HLGEM
    Apr 26 '12 at 20:16
5

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.

1

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.

0

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.

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.