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