Oracle: Types of Subqueries

Subqueries in Oracle divided into 3 categories:

1. Single-Row Subquery
2. Multi-Row Subquery
3. Correlated Subquery

In this article we will go through Single and Multi-Row subqueries. To read about Correlated subquery, click here.

Follow the Stu Table as shown below:

1. Single-Row Subquery

Single Row Subqueries are among those nested queries that return a single row as an output. There is some operators list that can be used before subqueries. =,<,>,<=,>= are those operators.

Example

We want to find out details of those Students that Stu_id more than the Student having Roll number=16 :

Select * from Stu
Where Stu_id>(Select Stu_id from Stu where Roll_No=16);

OUTPUT:

The highlighted part in the output above displays a single-row subquery. The execution of this highlighted query displays the output as above. It can be noticed that the query returns a single row.

2. Multi-Row Subquery

Multi-line Subqueries are amongst those subqueries, that return multiple rows as an output. Now, consider a situation wherein we need the list of those Students who have Roll_no equal to those having Stu_id>1. 

Select * from Stu
where Roll_no= (Select Roll_no from Stu where Stu_id>1)

But we can see that the above code will display an error and the error mentions:

OUTPUT:

This is because the multiline subquery returns multiple rows as an output. So we need to put a multiline operator preceding the subquery to perform the same task. Some Operators are:

• ANY Operator
• ALL Operator
• IN Operator
• EXIST Operator,
• NOT EXIST Operator,

Example

We want the list of those Students who have Roll_no equal to those having Stu_id>1.

Select * from Stu
where Roll_no= ANY(Select Roll_no from Stu where Stu_id>1)

OUTPUT:

The output displays a multiline subquery that is marked as highlighted below. You can see executing the subquery has returned 3 rows.