SQL and MySQL course Β· Module 7: Joining tables (JOIN)
LEFT JOIN - every scroll from the left shelf
In this lesson3
INNER JOIN shows only complete pairs. Sometimes, however, we want to see all rows from one shelf, even if they have no pair in the other. That is what LEFT JOIN is for.
What LEFT JOIN does
A LEFT JOIN B returns:
- all rows from the left table (
A), - matched rows from the right table (
B), - and where there is no match, it fills the columns from
Bwith the value NULL (emptiness).
SELECT c.nazwisko, w.data_wypozyczenia
FROM czytelnicy c
LEFT JOIN wypozyczenia w ON w.czytelnik_id = c.id;This means: "Show every reader, and next to them the date of their loan - and if they have never borrowed anything, leave an empty space (NULL)".
| nazwisko | data_wypozyczenia |
|---|---|
| z Aleksandrii | 0250-03-01 |
| Nowicjusz | NULL |
When to use it
LEFT JOIN is ideal when we do not want to lose rows without a pair. The classic example: a list of readers who borrowed nothing. With INNER JOIN such readers would disappear - because they have no row in wypozyczenia.
The absence filter: IS NULL
Since missing matches produce NULL, we can filter them out. To find readers without a single loan, we look for empty cross-references:
SELECT c.nazwisko
FROM czytelnicy c
LEFT JOIN wypozyczenia w ON w.czytelnik_id = c.id
WHERE w.id IS NULL;This is a powerful combination: LEFT JOIN keeps everyone, and WHERE w.id IS NULL leaves only those without a pair. The same way we find empty categories or authors without books.
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 will a LEFT JOIN of tables A and B (A LEFT JOIN B) return?
2. In which case is LEFT JOIN better than INNER JOIN?
These are 2 of 4 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that shows the nazwisko of every reader and the data_wypozyczenia from the wypozyczenia table. Use LEFT JOIN so that readers with no loans are shown too. Aliases: czytelnicy c, wypozyczenia w, condition w.czytelnik_id = c.id.
- Horizontal ordering
Arrange the correct LEFT JOIN clause joining wypozyczenia with readers on one line (left to right):
- Code editor
Write a query that lists the nazwisko of readers who have NEVER borrowed anything. Use czytelnicy c LEFT JOIN wypozyczenia w ON w.czytelnik_id = c.id and keep only the rows where w.id is NULL.
- Code editor
Write a query that shows the name of each category (the nazwa column) and the tytul of a book in that category. Use LEFT JOIN so that categories without books are shown too. Aliases: kategorie kat, ksiazki k, condition k.kategoria_id = kat.id.
- Vertical ordering
Arrange the parts of a query that finds readers with no loans (top to bottom):
Tasks after this lesson
- QuestionWhat LEFT JOIN returns
- QuestionWhen to use LEFT JOIN
- Coding taskAll readers and their loans
- QuestionValue when there is no match
- Put in orderLEFT JOIN of the wypozyczenia table
- Coding taskReaders with no loans
- QuestionThe IS NULL filter after LEFT JOIN
- Coding taskAll categories and their books
- Put in orderFinding readers with no loans