Self Joins, Cross Joins & Unions

Not every join is between two different tables. This lesson covers self joins (joining a table to itself), cross joins (every row of one table paired with every row of another), and UNION / UNION ALL for stacking the results of two separate queries into one result set.

Self Joins

A self join treats one table as if it were two, by giving it two different aliases. It's the standard way to compare rows in a table against other rows in that same table — here, finding pairs of users who both reviewed the same recipe.

-- Pairs of different users who reviewed the same recipe
SELECT r1.recipe_id, u1.username AS reviewer_one, u2.username AS reviewer_two
FROM reviews r1
JOIN reviews r2 ON r1.recipe_id = r2.recipe_id AND r1.user_id < r2.user_id
JOIN users u1 ON u1.id = r1.user_id
JOIN users u2 ON u2.id = r2.user_id;

CROSS JOIN

CROSS JOIN pairs every row of the first table with every row of the second — no ON condition, no matching logic. The result has rows(A) × rows(B) rows, so it's easy to accidentally produce a huge result set. It's genuinely useful for generating combinations, such as pairing every recipe with every dietary tag to build a "does this recipe have this tag?" checklist.

-- Every possible (recipe, tag) combination, to check off which actually apply
SELECT r.title, t.name AS tag
FROM recipes r
CROSS JOIN tags t;

UNION

UNION stacks the results of two SELECT statements into one result set and removes duplicate rows. Both queries must return the same number of columns, with compatible types in the same order.

-- One combined list of names: recipe authors and recipe reviewers, no duplicates
SELECT u.username FROM users u JOIN recipes r ON r.user_id = u.id
UNION
SELECT u.username FROM users u JOIN reviews rv ON rv.user_id = u.id;

UNION ALL

UNION ALL does the same stacking but keeps every row, duplicates included. It's also noticeably faster than UNION, since SQLite doesn't have to scan the combined result for duplicates to remove — use it whenever you know the two queries can't overlap, or you actually want the duplicates.

-- A combined activity feed: recipe titles added AND review comments left, duplicates kept
SELECT title AS activity FROM recipes
UNION ALL
SELECT comment AS activity FROM reviews WHERE comment IS NOT NULL;