Is there a drawback when not using aliases in SQL queries?

Question

Except when it's necessary (self reference, etc.), is there a drawback NOT to use SQL aliases?

I often see queries like :

SELECT c.name, p.phone, a.number, ct.fbAddress
FROM customer c 
INNER JOIN Person p ON p.idCustomer = c.idCuster
INNER JOIN Address a ON .... etc

It annoys me. It's not easily maintainable. Why not use the full names? Is there a problem with it?

And that's just an example. I've seen queries with 10-15 tables in them with this kind of shortcuts. It's not readable.

Edit:

I should have written "It's not easily readable" instead of "maintainable". It's true that it's easier to change the name of a table if you only have to change it in the "FROM" clause.

Oh and by the ways, I've seen these "one letter aliases" mostly from DBAs, so I though maybe there was some performance issue or something.

"It annoys me. It's not easily maintainable" - it is actually more maintainable. — Mitch Wheat, Jan 17 '12 at 01:11
See also: http://stackoverflow.com/questions/8363108/how-does-table-alias-names-affect-performance/8363129 — Glenn, Jan 17 '12 at 01:28

score 6 · Accepted Answer · answered Jan 17 '12 at 01:13

6

There are no (performance) downsides to not using aliases. But I think many would agree to the opposite of your preferences. Aliases make SQL more readable. What is more enjoyable to read to you?

SELECT c.name, p.phone, a.number, ct.fbAddress
FROM customer c 
INNER JOIN Person p 
ON p.idCustomer = c.idCuster
INNER JOIN Address a 
ON .... etc

or

SELECT customer.name, Person.phone, Address.number, SomeOtherTable.fbAddress
FROM customer
INNER JOIN Person
ON Person.idCustomer = customer.idCuster
INNER JOIN Address
ON .... etc

I would choose the former 11 times out of 10. Not to mention, the time saved, and the less error prone you are without having to type many times the characters with no aliases makes it quite worth it as well.

I will hand it to you, the one letter aliases (i.e. a, c, p) can be rough. But when you have a table called CompanyBillExtensions, it is easier and just as intuitive to write/read cbe or BillExt.

answered Jan 17 '12 at 01:13

1

I'm not sure what you intended to convey, but IMO your example reinforces the OP's point. When you see `a.number` you immediately ask, "What is `a`?" and have to hunt for its declaration. In contrast, `Address.number` is completely unambiguous. – Kirk Woll Jan 17 '12 at 01:16
3

@KirkWoll For programmers, database developers, and DBAs that read SQL every day all day, we know exactly where an alias is declared. You don't have to "hunt" for anything. Likewise, it's annoying to see `ALongTableName.SomeColumn`. If you need to scour a query for an alias declaration, then maybe proficiency and experience should be on the forefront of the mind, instead of being unambiguous. If only aliases were the only ambiguous pieces of code... – Jan 17 '12 at 01:18
I disagree -- most programmers (at least in Java and C#) completely *abhor* single character variable names (except when completely obvious like in iterator blocks). And it's not a matter of "where to look" that's hard -- we all know to look at the FROM clause. It's that you have to look *at all*. – Kirk Woll Jan 17 '12 at 01:20
2

@KirkWoll Variable names and table/view/subquery aliases are two completely different things, and are perceived completely different as well. An alias (in SQL) has a definition very close to its use. A variable, if it has a definition, doesn't always have explicit annotation. We're talking about apples and oranges with variables and aliases, IMO. Also, please read my answer. I agree, single letter anythings aren't always prudent. – Jan 17 '12 at 01:22
no, we are talking about apples and pears. They are *very* analogous. – Kirk Woll Jan 17 '12 at 01:23
@Shark That's probably a personal choice and maybe I'm 1 person on 100, but I prefer the second query. If I don't know a system and look at your first query, I don't understand your select and where clauses right away. Anyway, thanks for the input! – mrmuggles Jan 17 '12 at 01:24
@Kirk Woll: the point you make comparing to languages like C, java etc is very different: in a programming language variables names can mean anything (and I abhor badly named objects); in a database the abbreviation can only be one of a small number (hopefully less than 300 or so!) tables/views; tables/views that are constantly used and familiar. – Mitch Wheat Jan 17 '12 at 01:26
@mrmuggles It absolutely is a personal choice. Do what works for *you* and what *you* find easier to code and maintain! – Jan 17 '12 at 01:31
@Kirk, `Address` is no more informative than `a`. Neither tells you anything about the table other than an identifier, one the identifer used in the database, the other the identifier used in the query. If you don't understand what the tables referred to in the query are, than you need to find out what the tables are first, BEFORE ypu start worrying about what identifier to refer to them with. – Charles Bretana Jan 17 '12 at 02:30
Also, If you read a query with two or three tables, and read the From clause first (as you should) and see `From Person p Join Invoice i ... Join LineItem li ...`, and then you have any readability issues with the rest of your experience with that query, you need to have your short term memory checked... Aliases should be well chosen, of course, but stating that they destroy readability or increase ambiguity is foolish. – Charles Bretana Jan 17 '12 at 02:34
@Charles, in what universe can you conceivably argue that the lexical token "address" is not more informative than "a"? – Kirk Woll Jan 17 '12 at 06:03

score 3 · Answer 2 · answered Jan 17 '12 at 01:12

Is there a problem with it?

Yes and no. In your example, there is no problem. But if you try doing self-joins, or join to the same table through multiple paths, you need to alias your tables in order to make it work.

Here is an example when the same table (Address) is used more than once:

SELECT customer.name, home.fbAddress, business.fbAddress
FROM customer 
LEFT OUTER JOIN Address home ON home.AddressId = customer.homeAddressId
LEFT OUTER JOIN Address business ON business.AddressId = customer.businessId

score 0 · Answer 3 · answered Jan 17 '12 at 01:11

0

There aren't really any drawbacks. I would argue that using aliases increases readability and is more maintanable if you properly use them. If you're constantly using aliases that are one character when you're accessing 10-15 tables, then that's a naming problem and not one with using aliases. The maintainability lies with the fact that if a table is renamed or moved, only the place where it is linked to the alias actually needs to be changed instead of every reference to it in the query.

answered Jan 17 '12 at 01:11

RC.

27,409
9
73
93

How can you increase readability by changing the name of something? For example, when I see the table "Person", I know what it is. If I see "P", "Pers" or something like that, I have to know the model to understand it (person is quite easy but sometimes it's other less known concept). For example, if I see "PersonProfile", I know what it is. If I "PP", "PersProf" or whatever, I don't see how it can increase readability (maybe I'm doing it wrong) – mrmuggles Jan 17 '12 at 01:13
1

With a table name such as Person, it probably doesn't affect readablity, but what if the name isn't something as short and concise as Person? What if it is a view or materialized view that is aggregating data and has some longer more specific name? These are the scenarios where aliases can be very beneficial. In the end, you have decide what is more readable. In general though, I find that aliases enhance both readability and maintainability more times than not as long as you use sensible aliases. To the database though, it doesn't matter. (i.e. no downsides) – RC. Jan 17 '12 at 01:21

score 0 · Answer 4 · answered Jan 17 '12 at 01:12

Whether they are "better" or not is a contentious and subjective issue. Some people prefer short aliases as you show, some people don't. Some people feel very strongly about it.

In practice, I doubt it makes any difference to the execution of the query, any more than having a long or short variable name makes any difference in a compiled language (but might be very important to the coder).

score 0 · Answer 5 · answered Jan 17 '12 at 01:19

Another to all the other great comments above... using aliases and building dynamic queries is as simple as just changing a FROM table source, then you don't have to worry about the column names or rest of the joins elsewhere... Ex:

select o.*, od.*
   from CurrentOrders o
           join CurrentOrdersDetails od
              on o.orderid = od.orderid

now, going to some archives on some other server???

select o.*, od.*
   from BackupServer.ArchiveOrders o
           join BackupServer.ArchiveOrdersDetails od
              on o.orderid = od.orderid

score 0 · Answer 6 · answered Jan 17 '12 at 09:28

It's not easily maintainable. Why not use the full names?

Ease of maintenance is subjective. Say that customer and person are base tables and for some reason in the future the same query is required to target views based on customer and person respectively rather than the base tables themselved. If your house style is to use a correlation name that is the same as the table name (as far as is possible) then you will potentially have to change many names in the same query. However, if the preferred style is to use a correlation name that is not the same as the table name then there could be only one name that needs to be changed.

Of course, if the house style is to target only views and never base table then it could be that the aforementioned future change could involve a change to the view definitions only, leaving the queries that consume those view unchanged, which is arguably is hugely easier to maintain, but that's another consideration entirely ;)

Is there a drawback when not using aliases in SQL queries?

6 Answers6