Why do so many queries have aliases?

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?

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.

StackExchange Database Administrators Q#101715, answer score: 8