SQL and MySQL course · Module 7: Joining tables (JOIN)
The bridge is built - summary
In this lesson3
Well done! You have crossed the Bridge of Knowledge and can already link the Library's shelves with cross-references. Here is what you have gained:
What you have learned
- Data is split across tables linked by a foreign key (
ksiazki.autor_id→autorzy.id). - INNER JOIN ... ON ... returns only matching pairs of rows from both tables.
- The ON clause defines the join condition - on which column the tables find each other.
- Table aliases (
k,a,c,w,kat) shorten column names and make them unambiguous. - We can join many shelves in one query by adding further
JOINclauses. - LEFT JOIN keeps all rows from the left table; a missing match gives NULL.
- Combining
LEFT JOINwithWHERE ... IS NULLfinds rows without a pair (e.g. readers with no loans). - RIGHT JOIN is the mirror image of
LEFT JOIN- it keeps rows from the right table. - A self-join joins a table with itself, giving it two different aliases.
INNER versus LEFT - remember
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| Rows without a pair | disappear | stay (with NULL) |
| When to use | we want only complete pairs | we want to keep all rows from the left |
What's next?
In the next location we will rise above individual scrolls and learn to count and group them - you will meet the aggregate functions (COUNT, SUM, AVG) and GROUP BY. The bridge is behind us, onward, deeper into the Library!
Spotted a mistake in this lesson?