mysql: what does 'r' mean?

Question

I've been going through some code and repeatedly see the letter 'r' in queries, and I've never seen it before. I'm no mysql guru and cannot find references on the web that really make sense in this case.

Sample query:

SELECT * FROM database.table r
WHERE column = 'whatever'
AND otherColumn = 'whenever'
ORDER BY id, name

Answer 1

That actually means that the table is being aliased from its long form to the letter/symbol 'r'.

It's a red herring in your case because 'r' is not used anywhere in the query and it doesn't need to be. Your query is not a good example of using aliases because there is only one table involved. If you join multiple tables then aliasing becomes handy (although not required) to specify which table's column you're referencing in your various query's clauses.

You can simply remove the 'r' and run your query.

SELECT * FROM database.table r
WHERE column = 'whatever'
AND otherColumn = 'whenever'
ORDER BY id, name

Or use it outright like: (though it's redundant here)

SELECT * FROM database.table r
WHERE r.column = 'whatever'
AND r.otherColumn = 'whenever'
ORDER BY r.id, r.name

BTW, SQL code like this is the reason I tend to use the keyword AS to highlight the fact that I am aliasing. So the FROM clause would look like this: FROM database.table AS r

As far as what that unused alias is doing there is a good question. My guess it that it's been getting cut, copied and pasted from some old query which used the alias but no one ever bother to remove it when it became unnecessary.

Answer 2

It's a table alias. If you're joining two tables with duplicated column names, you would disambiguate the query by saying some_table.id, the table alias allows you to just type r.id.