Tag: Good practice / Bad practice

When writing queries, try making a habit out of using short table aliases, no matter how small the query is. Try using an alias that is as short as possible (one letter if possible, two or three when necessary) to avoid clutter in your queries.
Use these aliases in all places where you refer to a field, no matter if it would technically not be necessary. It will make your queries readable, and if you later need to join in other tables, you will not run the risk of ambiguous field names.

Let’s look at an example using the World database. The following two queries both select the 10 biggest cities by population in the continent of Africa, with a population between 1 and 1,5 million.

Good practice:

SELECT
  ci.name AS city_name,
  ci.district,
  co.name AS country_name,
  ci.population
FROM
  Country AS co
  INNER JOIN City AS ci ON (co.code = ci.countrycode)
WHERE
  (co.continent = 'africa') AND
  (ci.population BETWEEN 1000000 AND 1500000)
ORDER BY
  ci.population DESC
LIMIT 10;

bad practice:

select
  City.name,
  District,
  Country.name,
  City.population
from
  Country
  inner join City on City.countrycode = Country.code
where
  continent = 'africa' and
  City.population between 1000000 and 1500000
order by
  City.population desc
limit 10;

Both queries will execute and return the same results, but the first one is much more readable. Issues with respect to the table aliasing in the second query:
1) It is unclear without looking at the table structure whether District and continent are fields of the city or the country table.
2) The full table name needs to be specified for every field that would otherwise be ambiguous. This clutters the query and thus makes it less readable.
3) if I ever need to change the name of the table I am querying, I have to change it in all places instead of just in the from clause. A good example is when I was writing this article: I used lower case c’s for the country and city table names. So after writing the whole statement I had to go back in and change that lowercase c to an uppercase c in close to a dozen places, while in the first query I just needed to change it in one place.

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!