Inner & Outer Joins

Joins combine rows from two or more tables based on a related column — RecipeBox's recipes, users, and reviews tables are only useful together once you can join them. This lesson covers INNER JOIN and LEFT [OUTER] JOIN, SQLite's two long-standing join types, plus the real story behind RIGHT JOIN and FULL OUTER JOIN support.

INNER JOIN

INNER JOIN (or just JOIN — the INNER keyword is optional and implied) returns only the rows that have a match on both sides. A recipe with zero reviews simply won't appear at all.

-- Every recipe paired with its author (a recipe always has exactly one author)
SELECT r.title, u.username AS author
FROM recipes r
INNER JOIN users u ON u.id = r.user_id;

-- Recipes together with each of their reviews (recipes with 0 reviews are excluded)
SELECT r.title, rv.rating, rv.comment
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id;

LEFT [OUTER] JOIN

LEFT JOIN keeps every row from the left (first-listed) table, matching in rows from the right table where possible — and filling the right side's columns with NULL where there's no match. This is exactly what you need to find recipes with zero reviews.

-- Every recipe, with review columns NULL when a recipe has no reviews yet
SELECT r.title, rv.rating
FROM recipes r
LEFT JOIN reviews rv ON rv.recipe_id = r.id;

-- Isolate recipes that have never been reviewed at all
SELECT r.title
FROM recipes r
LEFT JOIN reviews rv ON rv.recipe_id = r.id
WHERE rv.id IS NULL;

RIGHT JOIN and FULL OUTER JOIN

For a long time, SQLite genuinely did not support RIGHT JOIN or FULL OUTER JOIN — only INNER JOIN and LEFT [OUTER] JOIN existed. That changed with SQLite 3.39.0 (released June 2022), which added both.

-- RIGHT JOIN: works on SQLite 3.39.0+. Keeps every user, even ones with no recipes.
SELECT u.username, r.title
FROM recipes r
RIGHT JOIN users u ON u.id = r.user_id;

-- FULL OUTER JOIN: works on SQLite 3.39.0+. Keeps unmatched rows from both sides.
SELECT u.username, r.title
FROM users u
FULL OUTER JOIN recipes r ON r.user_id = u.id;

You'll still run into older SQLite builds in the wild — bundled with older Android or Python installs, for instance — where these two keywords raise a syntax error. Two workarounds cover both cases without needing them:

-- RIGHT JOIN workaround: just flip the table order and use LEFT JOIN instead
SELECT u.username, r.title
FROM users u
LEFT JOIN recipes r ON r.user_id = u.id;

-- FULL OUTER JOIN workaround: UNION two LEFT JOINs run in opposite directions
SELECT u.username, r.title
FROM users u
LEFT JOIN recipes r ON r.user_id = u.id
UNION
SELECT u.username, r.title
FROM recipes r
LEFT JOIN users u ON u.id = r.user_id;