Table aliases are a necessary evil when 9 dealing with highly normalized schemas. For 8 example, and I'm not the architect on this 7 DB so bear with me, it can take 7 joins 6 in order to get a clean and complete record 5 back which includes a person's name, address, phone 4 number and company affiliation.

Rather than 3 the somewhat standard single character aliases, I 2 tend to favor short word aliases so the 1 above example's SQL ends up looking like:

select person.FirstName
      ,person.LastName
      ,addr.StreetAddress
      ,addr.City
      ,addr.State
      ,addr.Zip
      ,phone.PhoneNumber
      ,company.CompanyName
from tblPeople person
left outer join tblAffiliations affl on affl.personID = person.personID
left outer join tblCompany company on company.companyID = affl.companyID

... etc

Well, there are some cases you must use them, like 13 when you need to join to the same table 12 twice in one query.

It also depends on wether 11 you have unique column names across tables. In 10 our legacy database we have 3-letter prefixes 9 for all columns, stemming from an abbreviated 8 form from the table, simply because one 7 ancient database system we were once compatible 6 with didn't support table aliases all that 5 well.

If you have column names that occur 4 in more than one table, specifying the table 3 name as part of the column reference is 2 a must, and thus a table alias will allow 1 for a shorter syntax.

Am I the only person here who really hates 58 them?

Generally, I don't use them unless 57 I have to. I just really hate having to 56 read something like

select a.id, a.region, a.firstname, a.blah, b.yadda, b.huminahumina, c.crap
from table toys as a
inner join prices as b on a.blah = b.yadda
inner join customers as c on c.crap = something else
etc

When I read SQL, I like 55 to know exactly what I'm selecting when 54 I read it; aliases actually confuse me more 53 because I've got to slog through lines of 52 columns before I actually get to the table 51 name, which generally represents information 50 about the data that the alias doesn't. Perhaps 49 it's okay if you made the aliases, but I 48 commonly read questions on StackOverflow 47 with code that seems to use aliases for 46 no good reason. (Additionally, sometimes, someone 45 will create an alias in a statement and 44 just not use it. Why?)

I think that table 43 aliases are used so much because a lot of 42 people are averse to typing. I don't think 41 that's a good excuse, though. That excuse 40 is the reason we end up with terrible variable 39 naming, terrible function acronyms, bad 38 code...I would take the time to type out 37 the full name. I'm a quick typer, though, so 36 maybe that has something to do with it. (Maybe 35 in the future, when I've got carpal tunnel, I'll 34 reconsider my opinion on aliases. :P ) I 33 especially hate running across table aliases 32 in PHP code, where I believe there's absolutely 31 no reason to have to do that - you've only 30 got to type it once!

I always use column 29 qualifiers in my statements, but I'm not 28 averse to typing a lot, so I will gladly 27 type the full name multiple times. (Granted, I 26 do abuse MySQL's tab completion.) Unless 25 it's a situation where I have to use an 24 alias (like some described in other answers), I 23 find the extra layer of abstraction cumbersome 22 and unnecessary.

Edit: (Over a year later) I'm 21 dealing with some stored procedures that 20 use aliases (I did not write them and I'm 19 new to this project), and they're kind of 18 painful. I realize that the reason I don't 17 like aliases is because of how they're defined. You 16 know how it's generally good practice to 15 declare variables at the top of your scope? (And 14 usually at the beginning of a line?) Aliases 13 in SQL don't follow this convention, which 12 makes me grind my teeth. Thus, I have to 11 search the entire code for a single alias 10 to find out where it is (and what's frustrating 9 is, I have to read through the logic before 8 I find the alias declaration). If it weren't 7 for that, I honestly might like the system 6 better.

If I ever write a stored procedure 5 that someone else will have to deal with, I'm 4 putting my alias definitions in a comment 3 block at the beginning of the file, as a 2 reference. I honestly can't understand how 1 you guys don't go crazy without it.

Good

As it has been mentioned multiple times 14 before, it is a good practice to prefix 13 all column names to easily see which column 12 belongs to which table - and aliases are 11 shorter than full table names so the query 10 is easier to read and thus understand. If 9 you use a good aliasing scheme of course.

And 8 if you create or read the code of an application, which 7 uses externally stored or dynamically generated 6 table names, then without aliases it is 5 really hard to tell at the first glance 4 what all those "%s"es or other placeholders 3 stand for. It is not an extreme case, for 2 example many web apps allow to customize 1 the table name prefix at installation time.

Microsoft SQL's query optimiser benefits 7 from using either fully qualified names 6 or aliases.

Personally I prefer aliases, and 5 unless I have a lot of tables they tend 4 to be single letter ones.

--seems pretty readable to me ;-)
select a.Text
from Question q
    inner join Answer a
        on a.QuestionId = q.QuestionId

There's also a 3 practical limit on how long a Sql string 2 can be executed - aliases make this limit 1 easier to avoid.

If I write a query myself (by typing into 3 the editor and not using a designer) I always 2 use aliases for the table name just so I 1 only have to type the full table name once.

I suppose the only thing that really speaks 13 against them is excessive abstraction. If 12 you will have a good idea what the alias 11 refers to (good naming helps; 'a', 'b', 'c' can 10 be quite problematic especially when you're 9 reading the statement months or years later), I 8 see nothing wrong with aliasing.

As others 7 have said, joins require them if you're using the 6 same table (or view) multiple times, but 5 even outside that situation, an alias can 4 serve to clarify a data source's purpose 3 in a particular context. In the alias's 2 name, try to answer why you are accessing particular 1 data, not what the data is.

I LOVE aliases!!!! I have done some tests 6 using them vs. not and have seen some processing 5 gains. My guess is the processing gains 4 would be higher when you're dealing with 3 larger datasets and complex nested queries 2 than without. If I'm able to test this, I'll 1 let you know.

You need them if you're going to join a 2 table to itself, or if you use the column 1 again in a subquery...

Aliases are great if you consider that my 11 organization has table names like: SchemaName.DataPointName_SubPoint_Sub-SubPoint_Sub-Sub-SubPoint... My 10 team uses a pretty standard set of abbreviations, so 9 the guesswork is minimized. We'll have 8 say ProgramInformationDataPoint shortened 7 to pidp, and submissions to just sub.

The 6 good thing is that once you get going in 5 this manner and people agree with it, it 4 makes those HAYUGE files just a little smaller 3 and easier to manage. At least for me, fewer 2 characters to convey the same info seems 1 to go a little easier on my brain.

IMHO, it doesn't really matter with short 8 table names that make sense, I have on occasion 7 worked on databases where the table name 6 could be something like VWRECOFLY or some 5 other random string (dictated by company 4 policy) that really represents users, so 3 in that case I find aliases really help 2 to make the code FAR more readable. (users.username 1 makes a lot more sence then VWRECOFLY.username)

I like long explicit table names (it's not 7 uncommon to be more than 100 characters) because 6 I use many tables and if the names aren't 5 explicit, I might get confused as to what 4 each table stores.

So when I write a query, I 3 tend to use shorter aliases that make sense 2 within the scope of the query and that makes 1 the code much more readable.

I always use aliases in my queries and it 10 is part of the code guidebook in my company. First 9 of all you need aliases or table names when 8 there are columns with identical names in 7 the joining tables. In my opinion the aliases 6 improve readability in complex queries and 5 allow me to see quickly the location of 4 each columns. We even use aliases with 3 single table queries, because experience 2 has shown that single table queries don´t 1 stay single table for long.

I always use aliases, since to get proper 4 performance on MSSQL you need to prefix 3 with schema at all times. So you'll see 2 a lot of

Select Person.Name From
dbo.Person 1 As Person

I always use aliases when writing queries. Generally 6 I try and abbreviate the table name to 1 5 or 2 representative letters. So Users becomes 4 u and debtor_transactions becomes dt etc...

It 3 saves on typing and still carries some meaning.

The 2 shorter names makes it more readable to 1 me as well.

If you do not use an alias, it's a bug in 6 your code just waiting to happen.

SELECT Description -- actually in a
 FROM
 table_a a,
 table_b b
 WHERE
 a.ID = b.ID

What happens 5 when you do a little thing like add a column 4 called Description to Table_B. That's right, you'll 3 get an error. Adding a column doesn't need 2 to break anything. I never see writing good 1 code, bug free code, as a necessary evil.