SQL and MySQL course Β· Module 7: Joining tables (JOIN)
Self-join and INNER versus LEFT
In this lesson2
Before we leave the bridge, you will learn two subtleties of joining shelves: the difference between INNER and LEFT in practice, and a trick called the self-join.
INNER versus LEFT - on one example
Imagine a reader who has just registered and borrowed nothing.
- INNER JOIN
czytelnicywithwypozyczenia- such a reader disappears, because there is no pair. - LEFT JOIN
czytelnicywithwypozyczenia- such a reader stays, and the loan date is NULL.
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| Rows without a pair | disappear | stay (with NULL) |
| When to use | only complete pairs | all rows from the left |
This is the only - but truly fundamental - difference between these two bridges.
Self-join - a shelf linked to itself
Sometimes we join a table with itself (self-join). We then give it two different aliases, as if they were two separate shelves:
SELECT a1.nazwisko, a2.nazwisko
FROM autorzy a1
INNER JOIN autorzy a2 ON a1.kraj = a2.kraj;This query pairs up authors from the same country. The aliases a1 and a2 are two "copies" of the same autorzy table - thanks to them MySQL knows which row is meant in the ON condition and in SELECT.
The self-join is useful wherever we compare rows within a single table - for example authors from the same country or books published in the same year. It is an advanced but powerful bridge trick.
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. What is the key difference between INNER JOIN and LEFT JOIN?
2. What is a self-join?
These are 2 of 4 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Horizontal ordering
Arrange the correct self-join clause for the autorzy table on one line (left to right):
- Code editor
Write a query that shows the surnames of pairs of authors who come from the same country. Join autorzy a1 with autorzy a2 using INNER JOIN on a1.kraj = a2.kraj. Select a1.nazwisko and a2.nazwisko.
- Vertical ordering
Arrange the parts of a self-join of the autorzy table (top to bottom):
- Click in order
Click the elements in the order in which we build a query joining the three loan tables:
- Code editor
Write a query that shows data_wypozyczenia, the reader's nazwisko, the book's tytul and the category name (nazwa). Join wypozyczenia w with czytelnicy c (on w.czytelnik_id = c.id), with ksiazki k (on w.ksiazka_id = k.id) and with kategorie kat (on k.kategoria_id = kat.id), using INNER JOIN.
- Horizontal ordering
Arrange the correct join condition for the third table ksiazki in the loan report on one line (left to right):
- Code editor
Write a query that shows the book's tytul, the author's nazwisko and their kraj. Join ksiazki (alias k) with autorzy (alias a) using INNER JOIN on k.autor_id = a.id.
- Click in order
Click the elements in the order in which we build a query with RIGHT JOIN joining ksiazki with autorzy:
- Vertical ordering
Arrange the parts of a query joining three tables: the book, the author and the category (top to bottom):
Tasks after this lesson
- QuestionINNER versus LEFT JOIN
- QuestionWhat is a self-join
- QuestionWhy two aliases in a self-join
- Put in orderThe self-join clause of the autorzy table
- Coding taskAuthors from the same country
- Put in orderA full self-join of the autorzy table
- Click in orderJoin order in the loan report
- Coding taskLoan report with category
- QuestionWhich JOIN keeps all rows
- Put in orderAttaching the ksiazki table to loans
- Coding taskTitle, author and author's country
- Click in orderAuthors and titles with RIGHT JOIN
- Put in orderJoining books, authors and categories