SQL and MySQL course Β· Module 7: Joining tables (JOIN)
RIGHT JOIN - the mirror image
In this lesson3
The bridge can also be crossed the other way. RIGHT JOIN is the mirror image of LEFT JOIN.
What RIGHT JOIN does
A RIGHT JOIN B keeps all rows from the table on the right side (B), and from the left (A) it adds only matched rows. Where there is no pair - NULL on the left.
SELECT a.nazwisko, k.tytul
FROM ksiazki k
RIGHT JOIN autorzy a ON k.autor_id = a.id;This will show all authors, even those who do not yet have a single book on the shelf. The table on the right (autorzy) is "protected" - none of its rows will disappear.
RIGHT JOIN is LEFT JOIN reversed
In practice LEFT JOIN is used more often. Every RIGHT JOIN can be turned into a LEFT JOIN by swapping the tables:
-- these two queries give the same result
SELECT a.nazwisko, k.tytul
FROM ksiazki k RIGHT JOIN autorzy a ON k.autor_id = a.id;
SELECT a.nazwisko, k.tytul
FROM autorzy a LEFT JOIN ksiazki k ON k.autor_id = a.id;What was on the right we put on the left - and it stays "protected" just the same. That is why many scribes stick to LEFT JOIN only, always "protecting" the table written first.
Remember
- LEFT JOIN - protects the table on the left (
FROM). - RIGHT JOIN - protects the table on the right (
JOIN).
Both add NULL where there is no pair. The choice depends only on which side we want to keep in full.
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. How does RIGHT JOIN differ from LEFT JOIN?
2. How do we get the same result as RIGHT JOIN using only LEFT JOIN?
Hands-on tasks in the game
- Code editor
Write a query that shows the author's nazwisko and the tytul of their book. Use RIGHT JOIN so that authors with no book are shown too. Aliases: ksiazki k, autorzy a, condition k.autor_id = a.id.
- Horizontal ordering
Arrange the correct RIGHT JOIN clause joining the autorzy table on one line (left to right):
- Click in order
Click the elements in the order in which we build a query with LEFT JOIN joining czytelnicy with wypozyczenia:
- Code editor
Write a query that shows the category name (nazwa) and the book's tytul. Use RIGHT JOIN so that all categories are shown. Aliases: ksiazki k, kategorie kat, condition k.kategoria_id = kat.id.