SQL and MySQL course Β· Module 7: Joining tables (JOIN)

Table aliases - short catalog numbers

2 min read Β·
In this lesson3

Have you already noticed the shortcuts k and a in the queries? These are table aliases - temporary, short catalog numbers that we give the shelves for the duration of a single query.

Why aliases?

When we join tables, the id column exists in both. How is the Library to know whether we mean ksiazki.id or autorzy.id? We must prefix the column name with the table name. Full names, however, are long:

SELECT ksiazki.tytul, autorzy.nazwisko
FROM ksiazki
INNER JOIN autorzy ON ksiazki.autor_id = autorzy.id;

Shorter with aliases

We give an alias right after the table name, writing a letter (or short shortcut) after a space:

SELECT k.tytul, a.nazwisko
FROM ksiazki k
INNER JOIN autorzy a ON k.autor_id = a.id;
  • ksiazki k - from now on, in this query, k means ksiazki.
  • autorzy a - a means autorzy.

Thanks to this, k.tytul and a.nazwisko are short and unambiguous. An alias works only within one query - it does not change the table's name in the database.

Good practice

By convention we choose aliases so that they resemble the table name: k for ksiazki, a for autorzy, c for czytelnicy, w for wypozyczenia, kat for kategorie. When joining three shelves, aliases save your eyes!

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. Why do we use the aliases k and a in FROM ksiazki k INNER JOIN autorzy a?

  2. 2. Where in the query do we give a table its alias?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Vertical ordering

    Arrange the parts of a table-joining query in the correct order (top to bottom, as we write them in the editor):

  • Click in order

    Click the elements in the order in which we build a query joining the ksiazki table with autorzy:

  • Code editor

    Write a query that shows the tytul of each book and the name of its category (the nazwa column). Join ksiazki (alias k) with kategorie (alias kat) using an INNER JOIN on k.kategoria_id = kat.id.

  • Code editor

    Write a query that shows the book's tytul, the category name (nazwa) and the book's cena. Join ksiazki (alias k) with kategorie (alias kat) using INNER JOIN on k.kategoria_id = kat.id.

  1. QuestionWhy table aliases
  2. QuestionWhere we give an alias
  3. Put in orderA query joining books with authors
  4. Click in orderJoining ksiazki with autorzy step by step
  5. Coding taskTitle and category name
  6. QuestionWhy qualify the id column
  7. Coding taskTitle, category and price

Useful articles