Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
7  
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
add comment

3 Answers 3

up vote 30 down vote accepted

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
;
share|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
5  
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
3  
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
show 4 more comments

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.

share|improve this answer
1  
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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