When to use SQL Table Alias

Answer 1

I feel that you should use them as often as possible but I do agree that t & s represent the entities better than a & b.

This boils down to, like everything else, preferences. I like that you can depend on your stored procedures following the same conventions when each developer uses the alias in the same manner.

Go convince your coworkers to get on the same page as you or this is all worthless. The alternative is you could have a table Zebra as first table and alias it as a. That would just be cute.

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.

Answer 3

Always. Make it a habit.

Answer 4

i only use them when they are necessary to distinguish which table a field is coming from

select PartNumber, I.InventoryTypeId, InventoryTypeDescription
from dbo.Inventory I
    inner join dbo.InventoryType IT on IT.InventoryTypeId = I.InventoryTypeId

In the example above both tables have an InventoryTypeId field, but the other field names are unique.

Always use an abbreviation for the table as the name so that the code makes more sense - ask your other developers if they name their local variables A, B, C, etc!

The only exception is in the rare cases where the SQL syntax requires a table alias but it isn't referenced, e.g.

select *
from (
    select field1, field2, sum(field3) as total
    from someothertable
) X

In the above, SQL syntax requires the table alias for the subselect, but it isn't referenced anywhere so I get lazy and use X or something like that.

Answer 5

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

Answer 6

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 subervisorID column that references the empkloyeeID 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 7

I find it nothing more than a preference. As mentioned above, aliases save typing, especially with long table/view names.

Answer 8

Using the full name makes it harder to read, especially for larger queries or the Order/Product/OrderProduct scenari0

I'd use t and s. Or o/p/op

If you use SCHEMABINDING then columns must be qualified anyway

If you add a column to a base table, then the qualification reduces the chance of a duplicate in the query (for example a "Comment" column)

Because of this qualification, it makes sense to always use aliases.

Using a and b is blind obedience to a bizarre standard.

Answer 9

See also another thread on SO for this:

http://stackoverflow.com/questions/11043/sql-table-aliases-good-or-bad

Answer 10

I use it always, reasons:

• leaving full tables names in statements makes them hard to read, plus you cannot have a same table twice
• not using anything is a very bad idea, because later you could add some field to one of the tables that is already present in some other table

Consider this example:

select col1, col2
from tab1
join tab2 on tab1.col3 = tab2.col3

Now, imagine a few months later, you decide to add column named 'col1' to tab2. Database will silently allow you to do that, but applications would break when executing the above query because of ambiguity between tab1.col1 and tab2.col1.

But, I agree with you on the naming: a, b, c is fine, but t and s would be much better in your example. And when I have the same table more than once, I would use t1, t2, ... or s1, s2, s3...

Answer 11

One thing I've learned is that especially with complex queries; it is far simpler to troubleshoot six months later if you use the alias as a qualifier for every field reference. Then you aren't trying to remember which table that field came from.

We tend to have some ridiculously long table names, so I find it easier to read if the tables are aliased. And of course you must do it if you are using a derived table or a self join, so being in the habit is a good idea. I find most of our developers end up using the same alias for each table in all their sps,so most of the time anyone reading it will immediately know what pug is the alias for or mmh.

Answer 12

I always use them. I formerly only used them in queries involving just one table but then I realized a) queries involving just one table are rare, and b) queries involving just one table rarely stay that way for long. So I always put them in from the start so that I (or someone else) won't have to retro fit them later. Oh and BTW: I call them "correlation names", as per the SQL-92 Standard :)

Answer 13

Tables aliases should be four things:

1. Short
2. Meaningful
3. Always used
4. Used consistently

For example if you had tables named service_request, service_provider, user, and affiliate (among many others) a good practice would be to alias those tables as "sr", "sp", "u", and "a", and do so in every query possible. This is especially convenient if, as is often the case, these aliases coincide with acronyms used by your organization. So if "SR" and "SP" are the accepted terms for Service Request and Service Provider respectively, the aliases above carry a double payload of intuitively standing in for both the table and the business object it represents.

The obvious flaws with this system are first that it can be awkward for table names with lots of "words" e.g. a_long_multi_word_table_name which would alias to almwtn or something, and that it's likely you'll end up with tables named such that they abbreviate the same. The first flaw can be dealt with however you like, such as by taking the last 3 or 4 letters, or whichever subset you feel is most representative, most unique, or easiest to type. The second I've found in practice isn't as troublesome as it might seem, perhaps just by luck. You can also do things like take the second letter of a "word" in the table as well, such as aliasing account_transaction to "atr" instead of "at" to avoid conflicting with account_type.

Of course whether you use the above approach or not, aliases should be short because you'll be typing them very very frequently, and they should always be used because once you've written a query against a single table and omitted the alias, it's inevitable that you'll later need to edit in a second table with duplicate column names.

Answer 14

In simple queries I do not use aliases. In queries whit multiple tables I always use them because:

• they make queries more readable (my aliases are 2 or more capital letters that is a shortcut for the table name and if possible a relationship to other tables)
• they allow faster developing and rewriting (my table names are long and have prefixes depending on role they pose)

so instead of for example:

SELECT SUM(a.VALUE) 
       FROM Domesticvalues a, Foreignvalues b 
       WHERE a.Value>b.Value
       AND a.Something ...

I write:

select SUM(DVAL.Value) 
       from DomesticValues DVAL, ForeignValues FVAL 
       where DVAL.Value > FVAL.Value
       and   DVAL.Something ...

Answer 15

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.