11.5 Subqueries (WHERE, FROM, SELECT)

Level: 11 | Version: v1.1 | Author: Meptrasoft

Overview

A subquery is a query written inside another SQL query.

It is useful when one query needs the result of another query.

INNER QUERY
    ↓
RESULT
    ↓
OUTER QUERY

Subqueries can commonly be used in:

WHERE
FROM
SELECT

Subquery → A query inside another query.

SQL Subquery — Query Inside a Query — Figure 1: A subquery is a query inside another query, where the inner query provides a value or result used by the outer query.

Subquery in WHERE

A subquery can provide a value used to filter rows.

Find students who scored above the average:

SELECT *
FROM students
WHERE score > (
    SELECT AVG(score)
    FROM students
);

The inner query calculates the average score. The outer query then finds students whose score is greater than that value.

WHERE subquery → Use another query's result for filtering.

Subquery with IN

A subquery can also return multiple values.

Find employees working in departments located in New York:

SELECT *
FROM employees_sq
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);

The inner query returns department IDs, and the outer query finds employees belonging to those departments.

Three Common Places

Location	Purpose
`WHERE`	Filter using another query's result
`FROM`	Use a query result as a temporary result set
`SELECT`	Return a calculated value

Simple Memory Trick

WHERE  → FILTER
FROM   → SOURCE
SELECT → VALUE

Subquery in FROM

A subquery in FROM can act as a temporary result set.

Example:

SELECT *
FROM (
    SELECT name, score
    FROM students
    WHERE score > 70
) AS high_scorers;

The inner query first creates the high_scorers result, which the outer query uses as its source.

FROM subquery → Use a query result like a temporary table.

Subquery in SELECT

A subquery can also return a calculated value in the SELECT list.

Example:

SELECT
    name,
    score,
    (SELECT AVG(score) FROM students) AS average_score
FROM students;

This shows each student's score along with the overall average.

SELECT subquery → Return an additional calculated value.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
A subquery is a separate database	It is a query nested inside another query
Every subquery returns multiple rows	A subquery can return one value, one column, or multiple rows depending on the query
A subquery can only be used in WHERE	It can also be used in places such as FROM and SELECT
The inner query always runs completely before the outer query	The optimizer may choose a different physical execution strategy
A subquery always performs worse than a JOIN	Performance depends on the query, database, data, and execution plan

Placement Quick Points

SUBQUERY
→ QUERY INSIDE QUERY

WHERE
→ FILTER

FROM
→ SOURCE

SELECT
→ CALCULATED VALUE

A subquery is a query nested inside another query.
Subqueries can return a single value or multiple rows depending on their use.
WHERE subqueries are commonly used for filtering.
FROM subqueries provide a derived result set.
SELECT subqueries can provide calculated values.
Subqueries and joins can sometimes solve similar problems.

Interview Questions

What is a subquery?

A subquery is a SQL query nested inside another query.

Where can subqueries be used?

Common locations include:

WHERE
FROM
SELECT

What is a WHERE subquery?

A subquery whose result is used to filter rows in the outer query.

What is a FROM subquery?

A subquery used as a derived result set in the FROM clause.

What is a SELECT subquery?

A subquery used to produce a value or calculated result in the SELECT list.

Can a subquery return multiple rows?

Yes. The allowed result depends on how the subquery is used. For example, IN can work with multiple returned values.

Practice & Hands-On Exercises

Using the practice tables:

Find students who scored above the average:

SELECT * FROM students
WHERE score > (SELECT AVG(score) FROM students);

Find employees working in New York departments using IN:

SELECT * FROM employees_sq
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location = 'New York'
);

Create a FROM subquery that returns students scoring above 70:

SELECT * FROM (
    SELECT name, score FROM students WHERE score > 70
) AS high_scorers;

Use a SELECT subquery to display each student's score and the overall average:

SELECT name, score, (SELECT AVG(score) FROM students) AS average_score
FROM students;

Explain the difference between subqueries in WHERE, FROM, and SELECT.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

SUBQUERY
    ↓
PROVIDES A RESULT
    ↓
OUTER QUERY USES IT

A subquery is a query inside another query, commonly used in WHERE for filtering, FROM for derived results, and SELECT for calculated values.

End of Subqueries (WHERE, FROM, SELECT)