Subqueries

A subquery is a SELECT statement nested inside another query - MySQL runs the inner query first (or, in one important case we'll get to, repeatedly), and the outer query uses whatever it produces. Subqueries show up in several distinct positions in a query, each with its own shape and its own rules, and this lesson works through every one of them against StreamHub's tables.

Scalar Subqueries: Returning a Single Value

A scalar subquery returns exactly one value - one row, one column - which lets it be used almost anywhere a plain value could go, including right inside a WHERE comparison. Movies that run longer than the catalog's average runtime:

SELECT title, duration_minutes
FROM movies
WHERE duration_minutes > (SELECT AVG(duration_minutes) FROM movies);

The inner query runs once, computes one number - the average of every movie's duration_minutes - and the outer query compares each movie's own runtime against that single value. This subquery isn't tied to any particular outer row; it runs exactly once, no matter how many movies the outer query ends up checking.

Subqueries in WHERE ... IN (...)

A subquery can also return a whole list of values for IN to check against. Users who have added at least one movie to their watchlist:

SELECT username
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM watchlist);

The inner query runs once and hands back a plain list of distinct user_id values; the outer query then just checks each user's id against that list, exactly like the IN (4, 5) literal list from the Operators & Conditions lesson - the only difference is the list comes from a query instead of being typed out by hand.

EXISTS and NOT EXISTS: Checking Presence Without Fetching Rows

EXISTS checks whether a subquery would return any row at all, without actually caring what those rows contain - it stops as soon as it finds one match, which can be more efficient than pulling back full data just to check for its presence. Movies that have never been reviewed:

SELECT m.title
FROM movies m
WHERE NOT EXISTS (
  SELECT 1 FROM reviews r WHERE r.movie_id = m.id
);

SELECT 1 inside the subquery is a common convention - since EXISTS only cares whether a row shows up, not what's in it, the selected value itself is irrelevant.

Correlated Subqueries: Re-running Once Per Outer Row

The NOT EXISTS query above is a correlated subquery - notice the inner query references m.id, a column from the outer query. That reference means the inner query can't just run once up front the way the scalar and IN examples did; MySQL has to re-run it once for every single movie, plugging in that movie's own id each time, to answer "does at least one review exist for this specific movie?" That's genuinely different from the earlier two examples: the average-runtime subquery and the watchlist IN subquery are both fully self-contained and don't mention anything from their outer query, so each one only ever runs once no matter how many outer rows are being checked.

Subqueries in FROM: Derived Tables

A subquery can also sit in the FROM clause, acting as a temporary, on-the-fly table called a derived table - the rest of the query then joins or filters against it just like a real table. Here, we first compute every movie's average rating in the subquery, then join that result against movies to show only the ones rated above 4:

SELECT m.title, movie_ratings.avg_rating
FROM movies m
JOIN (
  SELECT movie_id, AVG(rating) AS avg_rating
  FROM reviews
  GROUP BY movie_id
) AS movie_ratings ON movie_ratings.movie_id = m.id
WHERE movie_ratings.avg_rating > 4;

The (SELECT ... GROUP BY movie_id) block runs first and produces what's effectively a small, temporary table named movie_ratings, with one row per movie. The outer query then treats it exactly like any other table it can join against - it just happens to be built on the fly instead of living permanently in the database.

Subqueries vs. Joins: A Genuine Tradeoff

The derived-table example above and the last lesson's join-plus-GROUP BY query answer a nearly identical question using two different techniques, and it's worth being honest about when to reach for each. Subqueries - especially EXISTS/NOT EXISTS - tend to read more naturally for "does a matching row exist" style questions, since the query's structure mirrors the English sentence almost directly. Joins are usually the better - and often measurably faster - choice when the final result needs actual columns from both tables side by side, since a join produces those combined columns directly instead of just a yes/no signal. Neither approach is universally "better"; picking between them is a real, ongoing engineering judgment call you'll keep making as queries get more complex.