Subqueries: Basics & Result Types

Question: "Which players scored more than the average?" You cannot write WHERE runs > AVG(runs) — aggregates are not allowed in WHERE. You first need the average, then the filter. A subquery lets you do both in one statement.

Definition

A subquery (also called an inner query or nested query) is a complete SELECT statement written inside brackets within another SQL statement (the outer query or main query). The inner query produces a result that the outer query then uses as a value, a list, or a table.

• Complete SELECT — it has its own SELECT … FROM … and could often run alone.
• Inside brackets — the brackets ( ) are mandatory.
• Outer query uses its result — the outer query treats it like a number, a list or a temporary table, depending on what the inner query returns.

How a subquery works — inside out

SELECT name, runs
FROM players
WHERE runs > (SELECT AVG(runs) FROM players);

The inner query is finished before the outer query uses it (for a normal, non-correlated subquery).

Note: In SQL Server, AVG of an INT column returns an integer (528). The playground (SQLite) shows 528.33. The filtered rows are the same either way. Use AVG(runs * 1.0) in SQL Server if you need decimals.

Subquery result types

What the inner query returns decides where it is allowed to go.

Syntax — the three places a subquery can live

-- 1. In SELECT   (must be scalar)
SELECT column_name, (SELECT aggregate FROM table_b) AS alias
FROM table_a;

-- 2. In FROM     (a table result — alias required)
SELECT *
FROM (SELECT ... FROM table_a) AS derived_alias;

-- 3. In WHERE    (scalar with a comparison, or a list with IN / ANY / ALL / EXISTS)
SELECT *
FROM table_a
WHERE column_name > (SELECT ... FROM table_b);

Example — subquery in the FROM clause (derived table)

Find teams whose total runs are 700 or more.

SELECT *
FROM (
    SELECT team, SUM(runs) AS total
    FROM players
    GROUP BY team
) AS t
WHERE total >= 700;

How it works: the inner query builds a temporary result (one row per team with its total). The outer query treats that result as a table named t and filters it. A subquery in FROM is called a derived table, and in SQL Server it must have an alias (AS t).

Tip: Here you could also write HAVING SUM(runs) >= 700. Derived tables become essential when you need to filter or join on a result that already has aggregates or window functions in it.

Example — subquery in the SELECT clause

Show every player next to the overall average.

SELECT
    id,
    name,
    runs,
    (SELECT ROUND(AVG(runs), 0) FROM players) AS avg_runs
FROM players;

How it works: the inner query returns one value (a scalar), and that same value is repeated on every row. A subquery in SELECT must return a single value — otherwise SQL Server raises an error.

Common mistakes

• ❌ WHERE runs > AVG(runs) → aggregates are not allowed in WHERE. ✅ WHERE runs > (SELECT AVG(runs) FROM players).
• ❌ SELECT * FROM (SELECT team, SUM(runs) AS total FROM players GROUP BY team) → no alias. ✅ add AS t after the closing bracket.
• ❌ A derived-table column with no name, e.g. SUM(runs) without AS total → SQL Server error "No column name was specified". ✅ alias every calculated column.
• ❌ A scalar subquery that returns several rows, e.g. WHERE runs = (SELECT runs FROM players WHERE team = 'MI') → SQL Server error "Subquery returned more than 1 value". ✅ use IN, or make the inner query return one value (MAX, MIN, TOP 1).
• ❌ ORDER BY inside a subquery → not allowed in SQL Server unless TOP is also used. ✅ sort in the outer query.

Note: SQLite (the playground) silently uses the first row when a scalar subquery returns many rows. SQL Server is stricter and raises an error — always write it the strict way.

Interview tip

Q: What is the difference between a subquery in FROM and a subquery in WHERE? A subquery in FROM returns a table (a derived table) that the outer query reads rows from, and it needs an alias. A subquery in WHERE returns a value or a list that the outer query compares against to decide which rows to keep.