Do table aliases make complex queries harder or easier to read?

Question

Possible Duplicate:
SQL Table Aliases - Good or Bad?

I've been maintaining and extending a somewhat convoluted multi-website application in PHP for nearly two years now. Whilst some major pieces have been upgraded or re-written, there are still huge swathes that haven't been touched since before I started. Many of the previous developers liked creating very domain-specific queries, often with lots of JOINs. And always with table aliases that are often not consistent across multiple queries.

In my experience, using table aliases is a false saving. When typing an ad-hoc query, it might save some keystrokes. But in an application with scads of semi-obscure queries and no coherent system for aliasing tables, I personally find them a hindrance to understanding the query. So I don't use them (except when they're necessary, such as self-joins).

What are other people's experiences?

Answer 1

I always use table aliases.

They're necessary for self-joins as you mentioned:

SELECT ... FROM foo t1 JOIN foo t2 ON t1.foo_id = t2.parent_id;

They're useful when you have two columns of the same name in two tables, and you want to specify which one:

SELECT t1.name FROM foo t1 JOIN bar t2 ON ...

Or if you want both and need to give a column alias for one or both:

SELECT t1.name AS foo_name, t2.name AS bar_name FROM foo t1 JOIN bar t2 ON ...

Saving keystrokes is not why I use table aliases, but that can be a minor benefit too.

Regarding a naming convention for table aliases, this is similar to any other naming conventions in a given programming language (class names, function names, variable names, etc.). You can come up with a naming convention as a guideline, and you'll find this works in 95% of cases. For other cases, you'll have to "bend" the convention a bit.

But I don't bother with a naming convention for table aliases. It's like having rules for naming temporary variables in a for-loop. The scope of table aliases is limited to a single query, so there's only so much confusion you can cause.

Answer 2

I aggree with you. The main reason I use them is to remove prefixes from the table names that have very little meaning in the context of the query such as "Tbl_" (such prefixes are mandated by the DBA's where I work). Prefixes like this can hurt readability in larger queries such as ones with lots of joins or where you are selecting specific columns. However it is more of an issue for queries that are going to hang around, like ones that build views, then ad hoc ones while you are developing.

I haven't been in a team that has specified standards for aliases as they tend to be a fairly close representation of the table that is being aliased. I can however see that it might be useful if aliases are just being used to shorten table names and everyone is doing completely different things.