SQL and MySQL course Β· Module 7: Joining tables (JOIN)
Bridge of Knowledge - why join shelves
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.id | tytul | autor_id |
|---|---|---|
| 1 | Iliada | 3 |
| 2 | Odyseja | 3 |
| autorzy.id | imie | nazwisko |
|---|---|---|
| 3 | Homer | z 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.idksiazki.kategoria_idβkategorie.idwypozyczenia.ksiazka_idβksiazki.idwypozyczenia.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. Why do we split data in a database into separate tables (e.g. ksiazki and autorzy) instead of keeping everything in one?
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: