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 
              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?

  • 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. – Jon of All Trades 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? – Colin 't Hart Aug 2 '18 at 22:33

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
    -- 30+ columns snipped
    -- Would you want to type out full table names for each 
    -- column here?
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
    -- ... 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
|improve this answer|||||
  • 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. – Mike Sherrill 'Cat Recall' 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
  • @HLGEM - Both excellent points. – Nick Chammas Apr 26 '12 at 21:04

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,
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?

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.

|improve this answer|||||
  • 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

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.

|improve this answer|||||

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

    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.

|improve this answer|||||

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.

|improve this answer|||||

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.