When to use SQL Table Alias

Question

I'm curious to know how people are using table aliases. The other developers where I work always use table aliases, and always use the alias of a, b, c, etc.

Here's an example:

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

I disagree with them, and think table aliases should be use more sparingly.

I think they should be used when including the same table twice in a query, or when the table name is very long and using a shorter name in the query will make the query easier to read.

I also think the alias should be a descriptive name rather than just a letter. In the above example, if I felt I needed to use 1 letter table alias I would use t for the Trip table and s for the segment table.

Answer 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.

Answer 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.