SQL and MySQL course · Module 2: Querying data (SELECT)

Column aliases - give it a pretty name

4 min read ·
In this lesson5

Field names on a shelf can be technical: rok_wydania, autor_id. Scribes wrote them for scribes - with no spaces and no Polish letters. A reader who gets a price list would rather see something friendlier in the header. In the result we can rename the fields - such a new name is an alias.

The word AS

We create aliases with the word AS. It goes right after the field name, and after it we write the label that should appear in the header:

SELECT tytul AS Tytul, cena AS Cena FROM ksiazki;

In the answer the column headers will read Tytul and Cena:

TytulCena
Iliada39.90
Odyseja42.00
Państwo55.50
……

On the shelf the fields are still called tytul and cena. An alias changes only the label in the result - it never touches the data. The next query without an alias will show the headers tytul and cena again.

An alias looks exactly the way you type it. If you write AS autor, the header will read "autor", not "Autor". The Library does not fix the letter case for you, so type the alias the way it should look in the report.

Aliases with a space

What if the header should read "Rok wydania", with a space? Without quotes MySQL reads Rok as the alias and stops at the word wydania, because at that point it expects a comma or FROM. The result is a syntax error. That is why you wrap an alias with a space in quotes:

SELECT rok_wydania AS "Rok wydania" FROM ksiazki;

Now the header reads "Rok wydania", and under it stand the same years as in the rok_wydania field:

Rok wydania
1990
1990
2001
…

MySQL accepts such an alias in double quotes, in single quotes, or in backticks, which also surround field names. Pick one style and keep to it throughout the report.

AS is optional

MySQL lets you drop the word AS. If a second word follows the field name and there is no comma between them, MySQL takes that second word as an alias:

SELECT nazwisko Autor FROM autorzy;

The result has the header "Autor", just as if AS stood there:

Autor
Homer
Platon
Sofokles
…

It works the same, but with AS the code is clearer - and that is what I recommend. You can see at once where the field ends and the label begins.

A trap: the forgotten comma

The same rule can play a trick on you. You wanted two fields, but you lost the comma:

SELECT tytul cena FROM ksiazki;

MySQL raises no error. It takes cena as an alias of the tytul field and returns one column:

cena
Iliada
Odyseja
Państwo
…

The header says "cena", but inside stand the titles. There are no prices in the result at all. When the answer has fewer columns than you expected, count the commas first.

Why use aliases?

  • Headers in reports become understandable to people.
  • You can sort by an alias. Once you learn ORDER BY, you will see that ORDER BY Autor, for example, works.
  • In the WHERE clause, which you will learn in the next location, you cannot use an alias - there you give the real field name.
  • In later locations you will see that aliases are priceless with joins (JOIN) and functions.

Remember: an alias is a nickname given to a field for the span of a single query.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What does an alias created with the word AS do, e.g. "SELECT tytul AS Tytul FROM ksiazki;"?

  2. 2. How do you correctly give a column an alias that contains a space, e.g. Rok wydania?

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves the nazwisko column from the autorzy table and gives it the alias Autor (use the word AS).

  • Click in order

    Click the elements in the order in which you write an alias for the cena column named Cena:

  • Code editor

    Write a query that retrieves the cena column from the ksiazki table and gives it the alias Cena (use the word AS).

  1. QuestionWhat the word AS does
  2. QuestionAlias with a space
  3. Coding taskAlias for the author's surname
  4. Click in orderAn alias for the cena column
  5. Coding taskPrice with a pretty header

Useful articles