Subqueries: a query inside a query

A subquery (nested query) is a SELECT written inside another query. It lets you solve problems in steps: answer a smaller question first, then use that answer in the bigger one. It's a little tricky at first, but it unlocks complex problems.

Two kinds

Independent, single value

Which movie has the biggest budget? MAX(budget) gives the number but not the name (Chapter 8). A subquery fixes that:

SELECT *
FROM movies
WHERE budget = (SELECT MAX(budget) FROM movies);

1. The inner query runs first and becomes one number: the maximum budget.
2. The outer query becomes "WHERE budget = that number".

In the class's full dataset the answer was Padmaavat (₹215 crore); in this sample it's Baahubali 2. Same pattern: see also Practice 1, Q4.

Because it returns a single value, you compare with =, >, <… For example, movies that cost more than the average:

SELECT title, budget
FROM movies
WHERE budget > (SELECT AVG(budget) FROM movies)
ORDER BY budget DESC;

Independent, many values: use IN

A subquery can also return a list. Then = no longer makes sense; use IN. Task: all movies whose lead actor's name starts with "A", done in two steps.

-- step 1: the actors (DISTINCT, so each name appears once)
SELECT DISTINCT actor FROM movies WHERE actor LIKE 'A%';

-- step 2: plug that list into the outer query
SELECT title, actor
FROM movies
WHERE actor IN (SELECT DISTINCT actor FROM movies WHERE actor LIKE 'A%');

(Yes, here WHERE actor LIKE 'A%' alone would do. The point is the pattern: build a list in the inner query, filter with it in the outer.)

A subquery in FROM (derived table)

Nitish's harder example: show every movie of the top 5 actors by total profit.

-- step 1: top 5 actors by total profit (GROUP BY from Chapter 9)
SELECT actor, SUM(worldwide - budget) AS profit
FROM movies
GROUP BY actor
ORDER BY profit DESC
LIMIT 5;

We only want the actor column of that result. A query's result is itself a table, so we can select from it. A subquery in FROM must be given an alias:

-- step 2: just the names
SELECT actor
FROM (SELECT actor, SUM(worldwide - budget) AS profit
      FROM movies
      GROUP BY actor
      ORDER BY profit DESC
      LIMIT 5) AS top_actors;

-- step 3: all their movies
SELECT title, actor, worldwide - budget AS profit
FROM movies
WHERE actor IN (SELECT actor
                FROM (SELECT actor, SUM(worldwide - budget) AS profit
                      FROM movies
                      GROUP BY actor
                      ORDER BY profit DESC
                      LIMIT 5) AS top_actors);

Correlated subquery: best movie per genre

For each genre, the most profitable movie, showing title, genre and profit:

SELECT title, genre, worldwide - budget AS profit
FROM movies m1
WHERE (worldwide - budget) = (SELECT MAX(worldwide - budget)
                              FROM movies m2
                              WHERE m2.genre = m1.genre);

The inner query mentions m1.genre, a column of the outer row. For every movie the outer query looks at, the inner query finds the maximum profit within that movie's genre; the movie is kept only if its own profit equals that maximum. In class: Dangal for Drama, Baahubali for Masala/Action, Golmaal Again for Comedy, Sachin for Documentary…

The same pattern appeared as "top value per category" in Practice 1, Q8.

Where subqueries can go (summary)

-- bonus: subquery in SELECT
SELECT title, budget,
       (SELECT ROUND(AVG(budget), 1) FROM movies) AS avg_budget
FROM movies;

SQL lab

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.