Types of Subqueries

Back to course home

0% completed

Vote For New Content

Types of Subqueries

Table of Contents

Contents are not accessible

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

Nested Scalar Subquery
Single-Row Subquery
Multiple-Row Subquery
Single-Column Subquery
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);

MYSQL

. . . .

Result

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

MYSQL

. . . .

Result

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

MYSQL

. . . .

Result

4. Single-Column Subquery

A Single-Column Subquery is used to extract a single column's value from the database. In this example, we fetch the student_name where the student_id is less than the maximum.

Query

MYSQL

. . . .

Result

5. Correlated Subquery

A Correlated Subquery references columns from the outer query, creating a connection. Here, we fetch student_name for those whose age is greater than the average age in their respective departments.

Query

MYSQL

. . . .

Result

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible