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
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):
count(*) as `times`
OI.order_id = O.id
`times` > 1
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.