Subqueries can be categorized based on where they appear in an SQL statement and their purpose. They are categorized as,

1. Nested Scalar Subquery
2. Single-Row Subquery
3. Multiple-Row Subquery
4. Single-Column Subquery
5. Correlated Subquery

Example Data

The given table will be used for the demonstration of subtypes of nested queries.

1. Nested Scalar Subquery

A Nested Scalar Subquery returns a single value and is often used in a comparison. In this example, we retrieve the student_name where the student_id is the maximum.

Query

SELECT student_name
FROM students
WHERE student_id = (SELECT MAX(student_id) FROM students);

2. Single-Row Subquery

A Single-Row Subquery is used to fetch a specific row from the database table, in this case, retrieving the student_name and age where the student_id is the maximum in the students table.

Query

3. Multiple-Row Subquery

A Multiple-Row Subquery filters results based on a condition involving multiple rows. Here, we retrieve student_name for those in departments where at least one student is older than 21.

Query