I've spent a lot of time searching for, researching and implementing various complex MySQL queries over the last several years. Many of them use lots of aliases for tables, even when none are necessary.

Why is this? I've seen a little discussion about using aliases to improve readability, but other than for aggregate/calculated columns, I find that it's majorly detrimental to readability. Your brain has to translate between aliases and actual tables.

For example:

SELECT orders.id, COUNT(*)
FROM orders, order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING COUNT(*) > 1

vs

SELECT o.id, COUNT(*)
FROM orders o, order_items i
WHERE i.order_id = o.id
GROUP BY o.id
HAVING COUNT(*) > 1

To me, the first version is so much easier to read, and this is just a simple example. But I see it used in simple examples a lot. Is it just to type less? Because that's a horrible reason.

I understand it makes sense if you're joining the same table one or more times, but totally not the case much of the time.

Or is there just something I'm missing?

closed as primarily opinion-based by Colin 't Hart, Paul White, dezso, RolandoMySQLDBA, Kin May 18 '15 at 21:59

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I think that I can answer this without falling in the "opinion" category. – jynus May 17 '15 at 10:56
  • Maybe as you say for shorthand or, more likely, obfuscation... – Greenonline May 17 '15 at 11:39
up vote 8 down vote accepted

In some cases, MySQL requires an alias, for example, a self join:

mysql> CREATE TABLE test (id SERIAL, name text);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT name FROM test JOIN test on test.id=test.id;
ERROR 1066 (42000): Not unique table/alias: 'test'

mysql> SELECT T1.name FROM test T1 JOIN test T2 on T1.id=T2.id;
Empty set (0.00 sec)

Also when using certain subqueries:

mysql> SELECT name FROM (SELECT * FROM test) JOIN test;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> SELECT T1.name FROM (SELECT * FROM test) T1 JOIN test T2;
Empty set (0.00 sec)

I think this could be the origin of "Always using an alias".

I would agree that the second example is more legible, but that is orthogonal to the use of aliases - nobody is forcing you to use 1-letter aliases, you can name the alias with the same name as the table itself:

SELECT orders.id, COUNT(*)
FROM orders orders, order_items order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING COUNT(*) > 1

If that makes sense, but it is out of the scope of the original question. However, many people do not do that, as the other main reason to use aliases is actually to type less, which it is true that in most contexts decreases legibility - although as it is very extended in the SQL community. However, please take into account that in some cases, conventions makes things more legible (for reference, the i, j, k widely-accepted standard for counters in programming languages).

However, here is my preferred formatting for your query, in which the slight different syntax makes it better to read for me:

SELECT   O.id, count(*) AS `times`
FROM     orders O
JOIN     order_items OI
ON       OI.order_id = O.id
GROUP BY O.id
HAVING   `times` > 1

For larger queries you may want to go for something like this (exaggerated, in my opinion, for such a small query):

SELECT 
    O.id, 
    count(*) as `times`
FROM 
    orders O
JOIN 
    order_items OI
ON 
    OI.order_id = O.id
GROUP BY 
    O.id
HAVING
    `times` > 1

The JOIN syntax is preferred. I also, personally, prefer uppercase for aliases, either with or without a _ sign. The alias of the column is because count(*) as an identifier is ugly (in a practical way, as it has non-ascii characters).

The important part is sticking to a style guide or convention, that could be different for each project you are in.

  • Excellent explanation. Thank you for taking the time. I had no idea that aliases could be the same as the real table name, though that makes sense. I also prefer the JOIN syntax, I just copied that example nearly straight from another source. Good point about i, j, k as counters. – Josh Hudnall May 17 '15 at 16:48
  • 2
    I like short aliases because they is less clutter. I usually use the first letter or initials of the tables. – Rick James May 17 '15 at 17:07
  • 1
    When there is a JOIN and I don't know which column is in which table, a tablename/alias is very important. – Rick James May 17 '15 at 17:08

Jynus's answer is very good, but i would also add that in some cases more than one table in a query can have the same field name which is ambiguous and must be resolved. Although I guess you could use a full table name as a prefix.

In my experience when dealing with extremely long table names that can have very similar spelling, using aliases makes the code much more compact, tidier and more readable.

Not the answer you're looking for? Browse other questions tagged or ask your own question.