With joins you don't temporarily rename. You alias. So "u" is an alias of table reviews2_users and "r" is an alias of "reviews2_reviews". Basically, it makes things much easier to work with. Without the aliasing, the query would go something like:
SELECT reviews2_users.id AS IDUSER,
signature,
user,
email,
address,
genres_yes,
genres_no
COUNT(reviews2_users.id) AS COUNTER
FROM reviews2_users INNER JOIN reviews2_reviews ON (reviews2_users.id=reviews2_reviews.User_id)
GROUP BY reviews2_users.id
ORDER BY COUNTER DESC
Notice how r and u can make the query more readable and easier to understand? It's nothing more than that in some cases.
In other cases, you can rename the same table and alias them using 2 different names:
SELECT * FROM table1 a, table1 b WHERE a.id=b.cid
Same table, different names. It's a self join. But that's something you don't need to worry about.
Inner joining brings two tables information together so that it's all accessible almost at once. The "ON" clause defines what the tables will be joined on. This is usually and ID field of some sort.
Want to know more about it, read a book on SQL (or mySQL if you're not going to branch off into postgre, MSSQL or other SQL databases). Or, just visit the all-knowing place of knowledge about mySQL. The manual
