Subqueries

A subquery is a SELECT nested inside another query. SQLite lets you place one almost anywhere a value, a table, or a list is expected: inside WHERE, inside FROM as a "derived table," or right inside the SELECT list as a single computed value. This lesson works through all three, using RecipeBox's rating data throughout.

Subqueries in WHERE: IN (SELECT ...)

The subquery runs first, producing a list of values, and the outer query's IN checks each row against that list — this is the same IN from the operators lesson, just fed by a query instead of a literal list.

-- Recipes belonging to users who have left at least one review themselves
SELECT title
FROM recipes
WHERE user_id IN (
  SELECT DISTINCT user_id FROM reviews
);

Scalar Subqueries in SELECT

A scalar subquery returns exactly one column and one row (or NULL), so it can be dropped directly into a SELECT list like any other expression.

-- Each recipe's title alongside the site-wide average rating, for comparison
SELECT
  title,
  rating,
  (SELECT AVG(rating) FROM reviews) AS site_avg_rating
FROM recipes;

Recipes With an Above-Average Rating

Putting a scalar subquery on the right side of a comparison in WHERE is one of the most common subquery patterns — here it filters recipes down to ones rated above the site-wide average.

-- Recipes rated higher than the average rating across ALL recipes
SELECT title, rating
FROM recipes
WHERE rating > (SELECT AVG(rating) FROM recipes)
ORDER BY rating DESC;

This is a non-correlated subquery — the inner SELECT AVG(rating) FROM recipes doesn't reference anything from the outer query, so SQLite can compute it once and reuse the result for every outer row.

Correlated Subqueries

A correlated subquery references a column from the outer query, so it must be re-evaluated separately for every outer row — it can't be computed just once.

-- Each recipe's title alongside its OWN average rating (recomputed per recipe)
SELECT
  r.title,
  (SELECT AVG(rv.rating) FROM reviews rv WHERE rv.recipe_id = r.id) AS this_recipes_avg
FROM recipes r;

Notice rv.recipe_id = r.id reaches out to r, the outer query's table — that outer reference is what makes it correlated. Correlated subqueries are flexible, but because they run once per outer row, an equivalent JOIN + GROUP BY is often faster for large tables; later lessons on query optimization cover that trade-off.

Subqueries in FROM (Derived Tables)

A subquery can also stand in for a table in the FROM clause — SQLite treats its result as a temporary, unnamed-until-you-alias-it table for the rest of the query.

-- Build a per-recipe rating summary first, then filter the SUMMARY, not the raw reviews
SELECT summary.recipe_id, summary.avg_rating
FROM (
  SELECT recipe_id, AVG(rating) AS avg_rating
  FROM reviews
  GROUP BY recipe_id
) AS summary
WHERE summary.avg_rating >= 4.5;