Oracle: Subqueries

WHAT ARE SUBQUERIES IN ORACLE?

Subqueries in Oracle mean nested queries. In an easy term, the subquery can be identified as a query inside the main query. This article will be covering the following topics on Subqueries:

Syntax

SELECT Column_name FROM Table_Name
where column_Name Operator(subquery)...;

Example

we need to find maximum Roll_No in the Stu table:

Stu Table

One way to do that is by writing a simple query:

Select Max(Roll_No) from Stu ;

Either way to do it is by using the subquery method. For instance, now I need to find details of the student who have the greatest Roll_No:

Select * from Stu
where Roll_No =(Select Max(Roll_No) from Stu )

OUTPUT:
OUTPUT

The above query shows a clear example of the subquery. 

Now the question arises, How the above query works?
Well, firstly the inner query or the nested query:

Select Max(Roll_No) from Stu;

executes and results as:
subquery

Moving ahead, the main query will now follow the logic as:

select * from Stu
where Roll_No=180

and will display results as:

subquery1