Typing out table names vs. table aliases in SQL

[DNAunion2000]

SQL allows the user to either fully type out all table names or to use abbreviated table aliases. Here’s an example of the first, more verbose method.


SELECT publishers.name, publishers.id, books.title
FROM publishers, books
WHERE publishers.id = books.publisher_id
ORDER BY publishers.name, books.title ;


The command can be made more compact and easier to comprehend by using table aliases:


SELECT p.name, p.id, b.title
FROM publishers p, books b
WHERE p.id = b.publisher_id
ORDER BY p.name, b.title ;


************************************************
NOTE: Even the above improved command can be improved, by using the new-style join syntax instead of the old-style:


SELECT p.name, p.id, b.title
FROM publishers p INNER JOIN books b ON p.id = b.publishers_id
ORDER BY p.name, b.title ;

The benefits of using the new-style join will be discussed in a separate thread.
************************************************

Most sources recommend using table aliases, for several reasons.

”Notice the use of table aliases in the preceding query. EMPLOYEE_TBL in line 2 has been assigned the alias E, and EMPLOYEE_PAY_TBL in line 3 has been assigned the alias P. You can see that in lines 4, 5, 6, and 7, the E and P stand for the full table names. Aliases require much less typing than spelling out the full table name, and even more important, queries that use aliases are more organized and easier to read than queries that are cluttered with unnecessarily long table names.” (Teach Yourself SQL in 21 Days: Second Edition, Ryan K. Stephens, Ronald R. Plew, Bryan Morgan, & Jeff Perkins, SAMS, 1997, p315)

”Notice the consistent use of table and column aliases in the SQL statement examples. You will save many, many keystrokes by using aliases. They also help make your statement more readable.” (Teach Yourself SQL in 21 Days: Second Edition, Ryan K. Stephens, Ronald R. Plew, Bryan Morgan, & Jeff Perkins, SAMS, 1997, p121)

”You can do this by specifying the columns in the form table.column, such as authors.au_id = title_author.au_id. The more compact and common way to do this, however, is by specifying a table alias in the FROM clause, as was done in this figure. By following the titles table with the word AS and the letter T, the titles table will be referred to as T from that point on. Typically, such an alias consists of one or two letters …

The FROM clause in Figure 7-3 shows an alternative was to specify a table alias, by omitting the word “AS”. The use of the word “AS” preceding the table alias, as used in Figure 7-2, conforms to ANSI SQL-92. ” (Inside Microsoft SQL Server 7.0, Ron Soukup and Kalen Delaney, Microsoft Press, 1999, p304, 307)

”Giving tables aliases is often used to save keystrokes, which results in the SQL statement being shorter and easier to read. In addition, fewer keystrokes means fewer keystroke errors.” (Sams’ Teach Yourself SQL in 24 Hours, Ryan K Stephens & Ronald R. Plew, SAMS, 1998, p173)

”In this case, we are allowed to declare alternative relation [i.e., table] names E and S, called aliases or tuple variables, for the EMPLOYEE relation. An alias can follow the keyword AS, as shown in Q8, or it can directly follow the relation name – for example, by writing EMPLOYEE E, EMPLOYEE S in the FROM clause of Q8. …

Notice that, if we want to, we can use this alias-naming mechanism in any SQL query to specify tuple variables [i.e., aliases] for every table in the WHERE clause, whether or not the same relation [i.e., table] needs to be referenced more than once. In fact, this practice is recommended since it results in queries that are easier to comprehend.” (Fundamentals of Database Systems: Fourth Edition, Ramez Elmasri & Shamkant B. Navathe, Addison Wesley, 2004, p222-223)

Although it is surely not an error to fully type out all table names, using aliases (1) reduces the number of keystrokes required, (2) reduces the likelihood of keystroke errors, (3) leads to more organized SQL commands, (4) improves clarity, and (5) is recommended by those who work with SQL.

[stuka]

My opinion:
1) C'mon, how many keystrokes do you save, and is it that important to be lazy?
2) Keystroke errors don't ruin your day that badly - we've all made millions, EVEN ON short identifiers.
3) How is the command/statement more organized?
4) How is clarity improved?
5) Actually, of those quotes you listed, only one appears (to me) to be from a source I would characterize as high-quality, and I STILL disagree with the statement that using aliases "results in queries that are easier to understand".

[DNAunion2000]

Even "Teach Yourself ..." books are usually written by professionals in the field (SAMS isn't going to publish books like this written by just average high-school students). I will try to look at the books tomorrow and post the authors' information here. In the meantime...


”Table Alias
I always use table aliases and try to keep them as short and descript as possible. Try not to specify the full table name on joins, expressions and filtering as you loose clarity in what the statement is doing, this is especially true for complex statements. There is no performance difference in using a table alias as opposed to not using one.” (http://www.developersdex.com/gurus/articles/271.asp?Page=3)


”A temporary name can be given to tables and complex table references to be used for references to the derived table in further processing. This is called a table alias.

To create a table alias, write

FROM table_reference AS alias

or

FROM table_reference alias

The AS key word is noise. alias can be any identifier.

A typical application of table aliases is to assign short identifiers to long table names to keep the join clauses readable. For example:

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

…

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.,

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...

Additionally, an alias is required if the table reference is a subquery (see Section 4.2.1.3). “ (http://www.sql.org/sql-database/postgresql/manual/queries-table-expressions.html)


”Common SQL Coding Standards

…


Writing SQL Statements


General

SQL-001: Each clause starts on its own line.

SQL-002: Always use spaces after keywords, commas, and other punctuation.

SQL-003: Use aliases for tables and columns. The table alias should be the initials from the table name. Similar names will be suffixed with numbers. Prefix each column with the table alias followed by a period and then the column name. Column names should denote any conversions of data and source table.


select t1.column1, t1.column2, t2.column3,
mt.column4, mt.column5, mt.column6,
to_char(mt.column1) mt_column1_char
from table1 t1,
table2 t2,
my_table mt

(http://www.perptech.com/resources/technotes/sql_std.html)


”If columns from multiple tables are included in the SELECT clause, it is a good idea to precede each column name with a table alias for reading clarity. For instance, “t.tissue_name”, where “t” is the alias for tissue defined in the FROM clause above. In the case where a column name is used in more than one of the tables in the FROM clause, it is mandatory to precede the column with either the table name or with an alias otherwise the database management system won’t know which table to return data from.

…

If more than one table is listed in the FROM clause, conditions can combine columns from the different tables. For reading clarity, it is a good idea to use a table alias as a prefix to each column name. If a column name is used in more than one table in the list, it is mandatory that either the tablename or the alias be used as a prefix.” (http://216.239.37.104/search?q=cache:C2d_6HZPGWQJ:gorgonzola.cshl.org/viewcvs/genome_informatics/lecture_notes/peitzch/lecture_notes.doc%3Frev%3DHEAD+SQL+%22table+alias%22+clarity&hl=en&ie=UTF-8)

[stuka]

select t1.column1, t1.column2, t2.column3,
mt.column4, mt.column5, mt.column6,
to_char(mt.column1) mt_column1_char
from table1 t1,
table2 t2,
my_table mt And THAT is supposed to be clear? I'd have to argue that in this case, aliases make the query HARDER to understand (though having seen real-life table names, with crazy prefixes, and VERY long names, I can see where THOUGHTFUL use of aliases would be good).

[DNAunion2000]

5) Actually, of those quotes you listed, only one appears (to me) to be from a source I would characterize as high-quality, …

Actually, two of the sources are very high quality: the college text Fundamentals of Database Systems: Fourth Edition, and Inside Microsoft SQL Server 7.0. While the other books are not targeted to the same caliber of audience, they are written by professionals in the field (as I alluded to earlier).

”Ryan K. Stephens and Ronald R. Plew are Database Administrators for Unisys Federal Systems. They are also instructors at Indiana University-Purdue University where they teach SQL and various database classes.

Bryan Morgan is a Software Develop with TASC, Inc. in Fort Walton Beach, FL. Bryan is the author of several books for Sams Publishing Including Visual J++ Unleashed, Java Developer’s Reference, and Teach Yourself ODBC Programming in 21 Days.

Jeff Perkins is a Senior Software Engineer with TYBRIN Corporation. He has co-authored three other Sams Publishing book, Teach Yourself NT Workstation in 24 Hours, Teach Yourself ODBC in 21 Days, and Teach Yourself ActiveX in 21 Days.” (from the back cover of Teach Yourself SQL In 21 Days: Second Edition)

”Currently a Microsoft product unit manager, Ron Soukup is one of the original members of the SQL Server team at Microsoft. He is a 17-year veteran of computer and database systems design, including 10+ years leading Microsoft’s SQL Server development group.

Kalen Delaney has worked extensively with SQL Server since 1987 and brings more than 20 years experience in technical support and education to this book. As a consultant to Microsoft, Kalen has developed advanced training materials on SQL Server, including curriculum supporting version 7.0. She is a columnist for SQL Server Magazine.” (from the back cover of Inside Microsoft SQL Server 7.0)

”Ryan Stephens and Ronald R. Plew are currently Oracle Database Administrators managing numerous development and production databases. They are also adjunct professors at Indiana University-Purdue University in Indianapolis, where they teach SQL, PL/SQL, and Oracle database administration. They have specialized in Oracle databases and SQL for more than seven years.” (from the back cover of Sams’ Teach Yourself SQL in 24 Hours)

”Ramez Elmasri
Department of Computer Science Engineering
University of Texas at Arlington

Shamkant B. Navathe
College of Computing
Georgia Institute of Technology” (from the first page of Fundamentals of Database Systems: Fourth Edition)

[stuka]

I still don't see how ALWAYS using table aliases is a Good Thing(TM). And yeah, I expected the textbook authors to be experts. However, just because someone works in a field, doesn't mean they really are good at it.

[DNAunion2000]

Originally posted by Stuka
I still don't see how ALWAYS using table aliases is a Good Thing(TM). And yeah, I expected the textbook authors to be experts. However, just because someone works in a field, doesn't mean they really are good at it.

But the overall picture, based on multiple competent professionals who work with SQL, is that there is a CONSENSUS that using table aliases is a recommended practice.

I have yet to come across any professional in the field stating in his/her book/article that one should NOT use table aliases.

[sicarius]

I have yet to come across any professional in the field stating in his/her book/article that one should NOT use table aliases.


Because that would be a silly suggestion :)

I think what Stuka is saying is that even though the use of aliases is generally recommended, there are times when not using them is appropriate.

[DNAunion2000]

Since this covers both topics together, I'll post it in this thread too.

Picked up a tutee in the computer science course Database Processing. Got the book and checked it out. Here's what it says about joins and table aliases.

"SQL-92 introduced an alternative join syntax that has become very popular because it is easier to interpret. This syntax substitutes the words JOIN and ON for WHERE, as follows:


SELECT Name, HoursWorked
FROM EMPLOYEE JOIN ASSIGNMENT
ON EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNum;


The results of this are: [table omitted]

In addition, it is possible to improve the readability of a join by using aliases for table names. The expression FROM EMPLOYEE E will assign the alias E to the EMPLOYEE table. Using this syntax the join above is the following:


SELECT Name, HoursWorked
FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A
ON E.EmployeeNumber = A.EmployeeNum;


... note that the keyword AS can be omitted, if desired.

As queries become more complicated, this format is easier to interpret and it is the format we will use for all of the joins in Chapters 7 and 8. ..." (David M Kroenke, Database Processing: Fundamentals, Design, and Implementation, Prentice Hall, 2004, p212-213)

[stuka]

And yet again, you still fail to show how alias use is ALWAYS good. I will not argue that aliases are bad - I can think of potential situations where they'd be downright invaluable. However, just using them to save typing a few letters is ridiculous, and IMHO leads to the same sort of obfuscation that one- and two-letter variable names can cause.

[Strike]

To use the ridiculous case (i.e., reductio ad absurdum), what DNAUnion2000 is saying is tantamount to saying that this:

SELECT a.foo, b.bar FROM x a, y b;

is preferable to this:

SELECT x.foo, y.bar FROM x, y;

[DNAunion2000]

That's not what I said.

But I guess what you are saying is that:


SELECT products.prod_id, products.prod_name, products.prod_cost, stores.store_id, stores.store_city,
stores.store_state, inventory.inv_units, inventory.inv_revenue
FROM products, stores, inventory
WHERE products.prod_id = inventory.prod_id
AND stores.store_id = inventory.store_id
AND products.prod_type = “Appliance”
AND stores.store_region = “NORTHEAST”
AND inventory.inv_units > 5
ORDER BY stores.store_id, inventory.inv_revenue, products.prod_name


414 characters

is preferable to:


SELECT p.prod_id, p.prod_name, p.prod_cost, s.store_id, s.store_city, s.store_state,
i.inv_units, i.inv_revenue
FROM products p JOIN inventory i ON p.prod_id = i.prod_id
JOIN stores s ON s.store_id = i.store_id
WHERE p.prod_type = “Appliance”
AND s.store_region = “NORTHEAST”
AND i.inv_units > 5
ORDER BY s.store_id, i.inv_revenue, p.prod_name

301 characters

PS: that comes to ~37.54% more typing - and a heck of a lot of redundancy (as in repeating the full PRODUCTS each time).

[stuka]

No. As I said, there are times when aliases are good, and times when they contribute NOTHING. Frankly, with all the typing-reduction shortcuts provided by modern text/code editors, I don't think the pure character count is relevant, PARTICULARLY when writing code that will be maintained by others. Clarity is KEY. If aliases promote clarity, I'm all for 'em. If not, toss 'em.

[Strike]

DNAUnion2000: admit that using table aliases isn't ALWAYS a good idea and Stuka and I will both shut up.

Your process of deductive logic (if such a process exists) is weak if you think that my saying that aliases aren't always good implies that they are always bad. Assuming that my statement implies that the contrapositive (using aliases isn't always good -> never using aliases is always good) is true is a logical fallacy.

[DNAunion2000]

Ran across another at work today.

"You can use an alias for two basic reasons. The first reason is for simplicity. In Example 8, you will assign the SALES_REP table the alias S and the CUSTOMER table the alias C. By doing this, you can type S instead of SALES_REP and C instead of CUSTOMER in the remainder of the query. The query in this example is relatively simple, so you might not see the full benefit of this feature. If the query is complex and requires you to qualify the names, using aliases can simplify the process greatly.

[example 8 omitted]

In addition to their role in simplifying queries, aliases are essential in certain situations, as illustrated in [the self-join in] Example 9." (Philip J. Pratt, A Guide to SQL: Fifth Edition, Course Technology, 2001, p83-84)

[DNAunion2000]

Your process of deductive logic (if such a process exists) is weak if you think that my saying that aliases aren't always good implies that they are always bad.

I’m sorry, I must have missed the part where I said what you are trying to stuff into my mouth. Could you please point it out to me, the person who allegedly asserted such?

Assuming that my statement [that aliases aren't always good] implies that the contrapositive (using aliases isn't always good -> never using aliases is always good) is true is a logical fallacy.

1) The “contrapositive” you offered isn’t correct: you’ve retained the same order of “parts” while negating the two: that’s the logical inverse, not the contrapositive (i.e., ~p->~q is the inverse of p->q).

2) At least in symbolic logic, the contrapositive of a conditional (~q->~p) is always equivalent to the conditional (p->q): one does imply the other: they are tautological. You can verify this using a truth table.

[Strike]

Originally posted by DNAunion2000
But I guess what you are saying is that:
<snip retarded example that implies that you seem to think that I think that you should NEVER use aliases>

That's where you said "what I am trying to stuff into your mouth" (funny how it came right out of your mouth already!)

[Strike]

If I mixed up logical inverse and contrapositive, big deal. Let's avoid the semantics and realize the fact that your drawn conclusion from my actual statements (using aliases IS NOT always good (mine) -> NOT using aliases IS always good (yours, as evidenced by your example which, by the way, was "putting words into my mouth")), is flat out wrong.

If I say "If it's raining, the ground is wet," that doesn't mean that I also think that "If it's not raining, the ground isn't wet," because that's not true (e.g., sprinkler could be on). You were saying I thought the latter (the logical inverse) to be true, given the former (my initial assertion). That's stupid.

[DNAunion2000]

----------------------------------------
DNAunion2000:
But I guess what you are saying is that:
<STRIKE: snip retarded example that implies that you seem to think that I think that you should NEVER use aliases>
----------------------------------------


That's where you said "what I am trying to stuff into your mouth" (funny how it came right out of your mouth already!)

Gee, how odd. Just the part that supposedly supports your false assertion is snipped out! And replaced by YOU stuffing MORE words into my mouth!

So Strike, I'll ask you again. Please SUPPORT your assertion - and this time, don't try stuffing more words into my mouth to try to show that the other words you were stuffing into my mouth were, allegedly ( but not actually), mine.

[DNAunion2000]

Strike: If I mixed up logical inverse and contrapositive, big deal.

Let’s see….if you ACTUALLY mess up, it’s no big deal. But if you just THINK I’ve messed up I am stupid.

Let's avoid the semantics and realize the fact that your drawn conclusion from my actual statements (using aliases IS NOT always good (mine) -> NOT using aliases IS always good (yours, as evidenced by your example which, by the way, was "putting words into my mouth")), is flat out wrong.

What IS wrong about that is that I never said what you claimed, and your “evidenced by” is busted.

You gave an absurdly simple example to try to show that aliases don’t always have to be used: AND STUFFED WORDS INTO MY MOUTH. Note how I immediately stated so, before any of your additional word-stuffing attempts.

In response, I gave a somewhat complex example to show how aliases can be useful, just as my supporting quotes indicated, and did so in a manner that imitated your original phrasing and "logic".

If I say "If it's raining, the ground is wet," that doesn't mean that I also think that "If it's not raining, the ground isn't wet," because that's not true (e.g., sprinkler could be on). You were saying I thought the latter (the logical inverse) to be true, given the former (my initial assertion).

No, I was not. Show me where I said that (and don’t try again to do it by snipping out sections of my statements and replacing them with your own, stuffing words into my mouth in a compound manner).

[Strike]

I provided the post. You said "I guess what you are saying is that...". What follows in the post is the logical inverse of what I actually said. It's right here in the thread for anyone to see, I didn't see the need to reprint the stupidity. My case is fine. Your move.

[DNAunion2000]

Strike: I provided the post. You said "I guess what you are saying is that...". What follows in the post is the logical inverse of what I actually said. It's right here in the thread for anyone to see, I didn't see the need to reprint the stupidity. My case is fine. Your move.

No, your case is quite bankrupt.

1) You've failed to show me stating that you believe it's always bad to use aliases. And you can't, because I didn't. You are INTERPRETTING my statements to say that, but they don't actually say that. YOUR inference is wrong.

2) You've failed to show me stating that one must always use local aliases. And you can't, because I didn't. In fact, my originally pointing out that you stuffed words into my mouth (which I then mirrored back at you by imitating your "argument") indicates that I don't believe that...on top of my never having said that.

These are the two premises that your "contrapositive" argument is based upon, and neither is correct.

[DNAunion2000]

Let me give you a piece of advice, Strike.

Here's the right way to go about challenging someone's position you are unsure about. ASK FOR CLARIFICATION. Then, IF AND ONLY IF he/she is actually saying what you think, go on with the "clever counter" you've worked out.

[Strike]

It's clear enough to anyone with any grain of sense that you shoveled the "he obviously thinks aliases are always bad" attitude into my mouth by your example that I've been focusing on for the past few posts. Perhaps you should focus on it as well.

Secondly, I never said that you said aliases are always good. Show me where I said that. And I've not based any single argument on it at all. Perhaps you should re-read. I've asked you to concede that aliases aren't always preferable because you seem to dance around that idea so much that one might reasonably presume you could very well be of that opinion. I know the tricks, man, it's not hard. You've avoided key points in just about any post that conflicts with something you say. I don't think anyone else has let this go unnoticed either.

[DNAunion2000]

Strike: Secondly, I never said that you said aliases are always good. Show me where I said that.

By your own logic, yes, you have. And there’s even more to it than just your logic. Let’s start with this.

Strike: DNAUnion2000: admit that using table aliases isn't ALWAYS a good idea and Stuka and I will both shut up.

One meaning of “admit” is to concede as begin true or valid (that this particular meaning probably applies is made stronger by your earlier post where you stuffed words into my mouth using your “absurd” example: which, ironically, you believe the mirror image of demonstrates what you reject here!; as well as that interpretation being consistent with a later statement you made). Asking me to concede that it is true that using table aliases isn’t ALWAYS a good idea strongly suggests that my position is, supposedly, that it is false that using tables aliases isn’t ALWAYS a good idea: hence, basically, that using table aliases is ALWAYS a good idea.

By itself, the above is not an airtight case. But there’s more…

Strike: To use the ridiculous case (i.e., reductio ad absurdum), what DNAUnion2000 is saying is tantamount to saying that this:


SELECT a.foo, b.bar FROM x a, y b;


is preferable to this:


SELECT x.foo, y.bar FROM x, y;



DNAunion: That’s not what I said.

Here, you stuff words into my mouth. And, using the most absurd example you could think of, you simply assert – for me - that I am saying that the first is preferable to the second.

That also strongly suggests that you are claiming my position is that using table aliases is ALWAYS a good idea. Otherwise, what makes you think I’d say the first is preferable to the second?

That’s two strong points, but there’s yet another.

You are trying to argue, despite your missing the overall picture (that my counter to you was an imitation of your absurd example – turning your own logic and tactics back on you) – shows that I am supposedly saying that you think it is always BAD to use table aliases. The fact that you accept that general logic as proof against me indicates we should accept that same general logic as proof against you. This is all the more so since, unlike in my case, you formulated your statement based on your own thoughts (my similar statement was an intentional mirroring of your absurd logic: it was based on what you said).

Hence, three strong indications that you have asserted that my position is that using table aliases is ALWAYS a good idea.

Now, can you show me where I said that? No. And you apparently realize this now as you are trying to squirm away from that unsupportable position.

[Strike]

Concede that using table aliases isn't ALWAYS a good idea.

[DNAunion2000]

Strike: Concede that using table aliases isn't ALWAYS a good idea.

No. I will acknowledge it, but I will not concede it because that statement does not go against anything I have stated or implied here.