SQL Table Aliases - Good or Bad? [closed]

Question

What are the pros and cons of using table aliases in SQL? I personally try to avoid them, as I think they make the code less readable (especially when reading through large where/and statements), but I'd be interested in hearing any counter-points to this. When is it generally a good idea to use table aliases, and do you have any preferred formats?

Answer 1

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

Rather than the somewhat standard single character aliases, I tend to favor short word aliases so the 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

Answer 2

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

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

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