Subqueries & Nested SELECTs

A query inside a query

A subquery is a SELECT written inside another query, wrapped in parentheses. MySQL runs the inner query first, then uses its result in the outer one. This lets you ask questions that need two steps — like “show me orders bigger than the average order”, where you must first compute the average, then compare against it.

We will use this orders table throughout (totals: 500, 250, 900, 300, 150 — the average is 420):

Scalar subquery — compare against one computed value

A scalar subquery returns a single value (one row, one column). You can drop it straight into a WHERE comparison. Here we find orders whose total is above the average total:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

MySQL runs the inner SELECT AVG(total) FROM orders first — it returns the single number 420. The outer query then behaves as if you had typed WHERE total > 420, keeping only the orders above the average. The beauty is you never had to know the average yourself; the subquery computes it.

IN subquery — match against a list the query produces

When the inner query returns many values (a whole column), use IN to keep outer rows whose value appears in that list. Say we want all orders placed by users who live in Mumbai — the inner query produces the list of Mumbai user ids, and the outer query keeps orders matching any of them:

SELECT id, user_id, total
FROM orders
WHERE user_id IN (SELECT id FROM users WHERE city = 'Mumbai');

The inner SELECT id FROM users WHERE city = 'Mumbai' returns a list of ids — say (1, 2). The outer query then keeps every order whose user_id is IN that list. This is often cleaner than a JOIN when you only need to filter, not show columns from the other table.

EXISTS — does any matching row exist?

EXISTS checks whether a subquery returns any rows at all — it answers a yes/no question rather than comparing values. It is the natural way to ask “show users who have placed at least one order”:

SELECT name
FROM users
WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.user_id = users.id
);

For each user, MySQL runs the inner query, which looks for an order belonging to that user (orders.user_id = users.id links the inner query to the current outer row — this is called a correlated subquery). If even one such order exists, EXISTS is true and the user is kept. We select 1 inside because the actual value does not matter — only whether a row comes back.

Derived table — a subquery in FROM

You can also use a subquery as if it were a table, by putting it in the FROM clause. This is called a derived table. Here we first total each user’s spending in an inner query, then filter that summary in the outer query — without storing anything:

SELECT user_id, total_spent
FROM (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) AS user_totals
WHERE total_spent > 600;

The inner query builds a small temporary table of each user and their total spend, which we name user_totals (a derived table must have an alias). The outer query then treats user_totals like any real table and keeps only the high spenders. This is one way to “filter on an aggregate of an aggregate” cleanly.