Subqueries & Nested Queries

What is a Subquery?

A subquery (also called a nested query) is a query written inside another query. It is used to return data that will be used by the main (outer) query.

Subqueries can be used in:

• SELECT
• WHERE
• FROM
• HAVING clauses

They help break down complex problems into simpler queries.

Types of Subqueries

1. Single-row Subquery

Returns only one row from the inner query.

Example:

This query returns the name of the student who is the oldest.

2. Multiple-row Subquery

Returns multiple rows, often used with operators like IN, ANY, or ALL.

Example:

This returns names of students whose department is located in Mumbai.

3. Correlated Subquery

A correlated subquery is executed once for each row of the outer query. It uses values from the outer query.

Example:

This finds students older than the average age in their department.

4. Nested Subquery

A subquery within another subquery.

This query finds students who belong to a department located in Pune.

Subqueries vs Joins

Subqueries: Better for modular and readable queries, especially when filtering.

Joins: Better for retrieving data from multiple related tables in a single flat result set.

Use Cases of Subqueries

• Filtering records using values from another table
• Calculating values (like MAX, MIN, AVG) to use in a condition
• Creating dynamic condition sets
• Replacing complex joins when only filtering is required