views:

2952

answers:

15
A: 

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.

Adam Caviness
+12  A: 

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.

BoltBait
The only reason I don't like this is what if I have more than on table that starts with the same letter? Now I switch to 2, 3, 4 characters for the alias... where does it end? JMHO.
mattruma
In that case, (and it happens alot) I use a letter to represent the relationship that I'm after. For example, I often join the users table of an application many times for finding a user (u), supervisor (s), or coworker (c).
BoltBait
Unless Trip and Segment both have fields name TripNum, SegmentNum, StopNum, and ArrivalTime, you don't need the aliases everywhere
Steven A. Lowe
@steven the state of the database today, TODAY, is all that you're concerned about? What about tomorrow when I add arrivalTime to the Trip table? Your code breaks. That's what.
A: 

Always. Make it a habit.

Justice
Not a helpful answer without an explanation why they should "always" be used, even when there is no technical reason to do so.
Dave Sherohman
You probably need to give a reason why you should 'always' do it.
Thorpe Obazee
A: 

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.

Steven A. Lowe
+4  A: 

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
mattruma
why alias only one table? Seems pretty half-ashed to do only one.
+9  A: 

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.

Walter Mitty
He's asking about using bullshit alias like t1, t2, t3 or a, b, c... not the use in general.
A: 

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

Jeff Schumacher
A: 

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.

+2  A: 

See also another thread on SO for this:

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

ScottCher
A: 

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

Milan Babuškov
A: 

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.

HLGEM
A: 

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 :)

onedaywhen
A: 

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.

Noah Yetter
A: 

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 ...
Erv
A: 

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.

MJB