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

Bridge of Knowledge - why join shelves

2 min read Β·
In this lesson2

Welcome to the Bridge of Knowledge - a stone walkway that connects two wings of the Great Library. On one side stands the shelf of books, on the other the shelf of author descriptions. A book scroll alone will not tell you who its creator was - you must cross the bridge of cross-references.

Data split across shelves

In our Library we do not keep everything on one scroll. An author's name is not repeated next to each of their books - that would be chaos and a waste of papyrus. Instead we have a separate autorzy shelf, and each book carries only a cross-reference to it.

ksiazki.idtytulautor_id
1Iliada3
2Odyseja3
autorzy.idimienazwisko
3Homerz Jonii

Foreign key - a cross-reference between shelves

The ksiazki.autor_id column is a foreign key. It points to a row in another shelf through its primary key: autor_id in the ksiazki table points to id in the autorzy table.

This is exactly how a whole relational database works - shelves linked by cross-references:

  • ksiazki.autor_id β†’ autorzy.id
  • ksiazki.kategoria_id β†’ kategorie.id
  • wypozyczenia.ksiazka_id β†’ ksiazki.id
  • wypozyczenia.czytelnik_id β†’ czytelnicy.id

To see a book's title and the author's surname in one result, we must learn to walk across these cross-references. We call this art JOIN - joining tables. Let us step onto the bridge!

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 split data in a database into separate tables (e.g. ksiazki and autorzy) instead of keeping everything in one?

  2. 2. What is the ksiazki.autor_id column?

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 steps of following foreign keys to get from a loan to the surname of the borrowed book's author:

  1. QuestionWhy we split data into tables
  2. QuestionWhat is a foreign key
  3. QuestionWhat kategoria_id points to
  4. Put in orderFollowing the foreign keys

Useful articles