Inner & Outer Joins

StreamHub's data is deliberately spread across several separate tables instead of being crammed into one giant sheet - users, movies, and reviews each hold one kind of thing, and a foreign key links them together. That design, called normalization, avoids repeating a movie's full title on every single review row (we cover the reasoning behind it properly in Database Normalization). The tradeoff is that a genuinely simple question - "which movies has this user reviewed, and what did they say about them" - now needs data from three different tables stitched back together. That stitching is exactly what a JOIN does.

INNER JOIN: Matching Rows on Both Sides

An INNER JOIN combines rows from two tables based on a matching condition, and keeps only the rows where a match exists on both sides. Here's the three-table join that answers our opening question:

SELECT u.username, m.title, r.rating
FROM reviews r
JOIN users u ON r.user_id = u.id
JOIN movies m ON r.movie_id = m.id;

(JOIN on its own means INNER JOIN - MySQL treats the keyword INNER as optional.) Every row here required a real match in all three tables: a real review, linked to a real user via user_id, linked to a real movie via movie_id. A review pointing at a deleted movie, or a movie nobody has reviewed at all, simply wouldn't show up here.

Table Aliases: Why r, u, and m

Notice reviews r, users u, and movies m - short aliases assigned right after each table name. Once three tables are involved, writing reviews.user_id and users.id in full, over and over, makes a query hard to scan. Aliases shrink that to r.user_id and u.id, and they become essential (not just convenient) the moment a query needs to reference the same table twice - exactly the situation the next lesson's self joins run into.

LEFT JOIN: Keeping Rows With No Match

An INNER JOIN quietly drops anything without a match on both sides - which is a problem if the question is "show me every movie, including ones nobody has reviewed yet." LEFT JOIN keeps every row from the left-hand table no matter what, and fills in NULL for the right-hand columns when no match exists:

SELECT m.title, r.rating, r.review_date
FROM movies m
LEFT JOIN reviews r ON r.movie_id = m.id;

The Long Orbit has never been reviewed, but it still shows up - with NULL standing in for the review columns it doesn't have. This is genuinely different behavior from INNER JOIN, which would have silently removed that row entirely. Whenever a report needs to include "everything, even things with zero matches" - every movie, every user, every genre - LEFT JOIN is the tool, and checking r.id IS NULL afterward is exactly how you isolate just the unmatched ones, as we did in the combined example back in Operators & Conditions.

RIGHT JOIN: The Mirror Image

RIGHT JOIN does the same thing as LEFT JOIN, just flipped - it keeps every row from the right-hand table, filling in NULL for the left side when nothing matches:

SELECT m.title, r.rating
FROM reviews r
RIGHT JOIN movies m ON r.movie_id = m.id;

This produces the exact same result as the LEFT JOIN example above, just written with the tables in the opposite order. In practice, most people rarely reach for RIGHT JOIN at all - it's simpler to always use LEFT JOIN and just put the table you want "everything from" on the left, reordering the FROM/JOIN lines instead of switching keywords. It's a genuinely common convention, and following it means every join in your codebase reads the same way at a glance.

Combining a Join With WHERE, GROUP BY, and ORDER BY

Joins become most useful combined with everything from earlier lessons. Here's a realistic StreamHub query: the average rating per movie, showing the actual title instead of a bare movie_id, limited to movies that have at least one review, ordered from best-rated to worst:

SELECT m.title, AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM movies m
JOIN reviews r ON r.movie_id = m.id
GROUP BY m.id
ORDER BY avg_rating DESC;

The plain JOIN (not LEFT JOIN) here is deliberate - it naturally excludes The Long Orbit, since a movie with zero reviews has nothing to average in the first place. Notice too that GROUP BY m.id is enough even though we're displaying m.title - because id is the primary key, every group already corresponds to exactly one title, so MySQL allows it under the grouping rule from the previous lesson.