When to use SQL Table Alias

Solution 1:

There are two reasons for using table aliases.

The first is cosmetic. The statements are easier to write, and perhaps also easier to read when table aliases are used.

The second is more substantive. If a table appears more than once in the FROM clause, you need table aliases in order to keep them distinct. Self joins are common in cases where a table contains a foreign key that references the primary key of the same table.

Two examples: an employees table that contains a supervisorID column that references the employeeID of the supervisor.

The second is a parts explosion. Often, this is implemented in a separate table with three columns: ComponentPartID, AssemblyPartID, and Quantity. In this case, there won't be any self joins, but there will often be a three way join between this table and two different references to the table of Parts.

It's a good habit to get into.

Solution 2:

I use them to save typing. However, I always use letters similar to the function. So, in your example, I would type:

SELECT t.TripNum, s.SegmentNum, s.StopNum, s.ArrivalTime 
FROM Trip t, Segment s 
WHERE t.TripNum = s.TripNum

That just makes it easier to read, for me.

Solution 3:

As a general rule I always use them, as there are usually multiple joins going on in my stored procedures. It also makes it easier when using code generation tools like CodeSmith to have it generate the alias name automatically for you.

I try to stay away from single letters like a & b, as I may have multiple tables that start with the letter a or b. I go with a longer approach, the concatenation of the referenced foreign key with the alias table, for example CustomerContact ... this would be the alias for the Customer table when joining to a Contact table.

The other reason I don't mind longer name, is due to most of my stored procedures are being generated via code CodeSmith. I don't mind hand typing the few that I may have to build myself.

Using the current example, I would do something like:

SELECT TripNum, TripSegment.SegmentNum, TripSegment.StopNum, TripSegment.ArrivalTime 
FROM Trip, Segment TripSegment 
WHERE TripNum = TripSegment.TripNum

Solution 4:

Can I add to a debate that is already several years old?

There is another reason that no one has mentioned. The SQL parser in certain databases works better with an alias. I cannot recall if Oracle changed this in later versions, but when it came to an alias, it looked up the columns in the database and remembered them. When it came to a table name, even if it was already encountered in the statement, it re-checked the database for the columns. So using an alias allowed for faster parsing, especially of long SQL statements. I am sure someone knows if this is still the case, if other databases do this at parse time, and if it changed, when it changed.