Self Joins, Cross Joins & Unions

The previous lesson joined different tables together. This lesson covers three more join-related ideas that round out the picture: joining a table to itself, joining every row against every other row on purpose, and combining the results of two completely separate queries into one list.

Self Joins: Joining a Table to Itself

A self join joins a table to a copy of itself, using two different aliases so you can tell which "copy" a column is coming from. It's useful whenever a table needs to be compared against its own other rows. A genuinely practical StreamHub example: finding pairs of users who reviewed the same movie - the seed of a "users with similar taste" feature. We join reviews to reviews, matching on a shared movie_id, while requiring the user_ids to be different so a review isn't paired with itself:

SELECT r1.user_id AS user_a, r2.user_id AS user_b, r1.movie_id
FROM reviews r1
JOIN reviews r2 ON r1.movie_id = r2.movie_id AND r1.user_id < r2.user_id;

r1 and r2 are both just reviews, but the aliases let the ON clause treat them as two independent sets of rows to compare against each other. The condition r1.user_id < r2.user_id is doing two jobs at once - it stops a user from being paired with themselves (where both sides would be the same row), and it stops every pair from showing up twice in reverse order (user 1 with user 2, and user 2 with user 1).

Cross Joins: Every Possible Combination

A cross join pairs every row in one table with every row in another, with no matching condition at all - if movies has 8 rows and genres has 5, a cross join between them produces 40 rows. Applied to StreamHub, every possible movie/genre pairing:

SELECT m.title, g.name
FROM movies m
CROSS JOIN genres g;

StreamHub already tracks which genres a movie actually belongs to through the movie_genres junction table, so this particular cross join isn't something you'd run for real - most of those 40 pairings are combinations that don't reflect reality at all. Cross joins are rarely reached for directly in everyday work. Their real value is conceptual: an INNER JOIN is really just a cross join with an ON condition filtering out every pairing that doesn't match. Seeing the unfiltered cross join makes it clear what a regular join is throwing away.

UNION: Combining Two Queries' Result Rows

UNION stacks the result rows of two separate SELECT statements into one combined result set - not side by side like a join, but one query's rows followed by the other's. A search-suggestions feature might want one combined list mixing movie titles and genre names, since a user typing into a search box doesn't care which table a suggestion came from:

SELECT title AS suggestion FROM movies
UNION
SELECT name AS suggestion FROM genres;

UNION vs. UNION ALL

Plain UNION automatically removes duplicate rows from the combined result, which means MySQL has to compare every row against every other row to find them - real work that costs real time. UNION ALL skips that step entirely and keeps every row, duplicates included, which makes it meaningfully faster. If a movie title ever happened to exactly match a genre name, UNION would silently show it once while UNION ALL would show it twice:

SELECT title AS suggestion FROM movies
UNION ALL
SELECT name AS suggestion FROM genres;

Use plain UNION when duplicates would be genuinely misleading in the output - like this search-suggestions list, where seeing "Drama" twice would look like a bug. Reach for UNION ALL whenever you already know the two queries can't overlap, or you simply don't care about duplicates and want the faster query - which, in practice, is most of the time.

The Column-Count Rule

Every SELECT combined with UNION must return the same number of columns, with compatible data types in each position - MySQL lines them up by position, not by name. Trying to union a two-column query with a one-column query fails outright:

SELECT title FROM movies
UNION
SELECT id, name FROM genres;

The fix is simply making both sides select the same shape of data - one column here, or two columns on both sides, whichever the actual question calls for.