Posted on 14 Comments

Good Practice / Bad Practice: Table Aliases

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!

Posted on 14 Comments

14 thoughts on “Good Practice / Bad Practice: Table Aliases

  1. I would take it one step farther. I despise ambiguous column names like “name”. So, although I do recommend aliasing every column, I would end up with:
    SELECT
    ci.city_name,
    ci.district,
    co.country_name,
    ci.population

    As I rule, I try to avoid giving any two objects in the entire data model the same name unless they are the exact same piece of data (joinable). I realize that “name” is an extreme case, but it’s too tempting for users to join disparate things “because the name was the same, I assumed it was the same thing”.

  2. Gregory Haase: iSerial needs to die in a fire.

  3. Hi!

    yeah, I mostly agree. I find I mostly go by this convention.However, I usually end up with 3 letter table name abbreviations for the aliases. I mean, when you make each alias as short as possible, you would have some alias of 1 letter, some with 2 and so on, which I found confusing. I found that 3 letters is usually enough to get unique aliases for all tables.

    So I usually make 3 letter aliases, which I derive directly from the table name. My aliasses start with the first letter of the table name, and end with the last letter of the table name. The second letter is usually the second letter of the table name, or the first letter of the second syllable of the table name. This usually gives me good, unique aliases that are still easy recognizable and specific for their table.

    Something I kind of missed in your post is reusing the same aliases for all queries in the same schema. I mean, the applicaitions I maintain tend to use the same aliases over and over in different queries. Of course when dealing with a new schema you need some time to know all tables so it may take some time to let a convention sink in. Having a rule like I just described for generating aliases helps a lot here.

    It is always a challenge to deal with self-joins. I find that in these cases, it is best to use an alias that defines in what role the table plays. Because the role names are normally not visiable as database identifiers I tend to not use 3 letter aliases here. For two way self joins I tried to use “sub” and “sup” for the child and the parent, but I found it doesn’t work too well for me – it’s just too similar. Even using “parent” and “child” doesnt work, it’s too abstract. So I end up with full names in this case: “boss” and “employee”.

    kind regards,

    Roland Bouman

  4. @gregory: I don’t have standards carved in stone with names and such. I like for instance and id column in a table to be called id, and not employee_id. I usually try to stick with what feels good and sane when naming columns. That means I could have a column called ‘name’ in a table ‘parts’, but I would have a longer name in case there are two ‘name’ attributes that need to be stored in a table. Sounds confusing, I know, but it makes sense in practice.

    @Ronald: Totally agree with what you say. Back when I was programming fulltime, I usually maintained 1 big application at a time. I found myself often using the same abbreviations for the same tables, making it easy to recognise.
    Now that I see different databases every day and my queries don’t end up in applications but they are just for me checking stuff out, I tend to just go with my approach described above.

  5. Howdy,

    While I agree all fields should be referenced in table.field format, I’m actually not for the idea of always aliasing tables with short aliases.

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

    Is way more readable then your ‘best’ practice. I also am very much a fan of using all caps for sql keywords, it splits up what is data fields from sql keywords a lot better visually IMHO.

  6. Hi,

    I actually try and not use table aliases, unless self-joining. I do make a point of using fully-qualified column names everywhere.
    This leads for much longer queries, granted, but I personally don;t like the use of 2-3 letter aliases, since it’s another thing to remember and associate. Instead of remembering a table’s name, I also need to remember its abbreviation. When you have dozens or more of tables, this becomes difficult.
    I also try and have meaningful table names. They could be long.
    It is later easier for me to grep or otherwise search my application code for any reference to a specific column.

    The exception is when I have a single table query. In those cases I might drop using fully qualified column names.

    Point #3 is good point, but I think rare.

    Regards

  7. As others have pointed out, table aliases are not always the best idea.

    One problem with using the shortest possible alias is when you have a long query with multiple joins, and are trying to decipher the EXPLAIN plan…do you really want something like this?

    mysql> EXPLAIN select count(*) from cpu_stats\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: co
    type: index
    possible_keys: NULL
    key: PRIMARY
    key_len: 7
    ref: NULL
    rows: 1
    Extra: Using index

    for each table, in the EXPLAIN plan?

    As Roland says, be careful not to make the aliases too short. It doesn’t help to have aliases if you can’t make them refer back to what they need to.

    You’re spot on about that “what happens if you change the table name” but that happens way less often in real life than finding a slow query, running the EXPLAIN plan, and getting a 7-way join of tables called “r”, “p”, and “s”.

  8. Nice to see a discussion over something relatively simple!

    @sheeri: I guess that kind of depends on your job. As a consultannt/dba/sysadmin, you are right. As a developerr, you will spend a lot more time making/changing queries.

    Besides that, as illustrated with Roland’s comment before, it is important to pick aliasses that make sense. If you are writing a one time usage query a one letter alias is fine. If you are writing queries that endd up in an application, a wisely chosen alias makes a whole lot of sense.

  9. I suppose choosing short aliases is similar to choosing short variable names in any application code. They’re better when they’re mnemonic.

  10. I find

    SELECT
    City.name,
    City.district,
    Country.name,
    City.population
    FROM
    Country
    INNER JOIN City ON City.countrycode = Country.code
    WHERE
    Country.continent = ‘africa’ AND
    City.population BETWEEN 1000000 AND 1500000
    ORDER BY
    City.population desc
    LIMIT 10;

    the most readable, although I always use all small caps for table and column names. For the “changing the table name” issue, you can always use:

    FROM
    country AS country

    and change the name as you’d like. Have a look at this:
    http://it.toolbox.com/blogs/database-soup/writing-maintainable-queries-part-i-29052

  11. Hi All,

    Ia it possible to create a table with some alias name?

    i.e. while creating a table with name ‘ReportBuildingBlock’ is it possible to give ‘rbb’ as its alias name?

    -Nag

    1. @nag aliases are just during a query. What you describe is more something a db abstraction layer could do.

  12. I was expecting that you will write about using aliasing the table (even though only single table will be used) which is used in two or more place.

    1. Same story applies – as per the first paragraph of the post, it’s useful to almost always alias references in the FROM clause.

Comments are closed.