SQL Sub Query

A subquery or inner query or nested query is a query within another SQL query. 

We can use subquery with select, update, insert and delete statements.

In the subquery, order by command cant be used, but Group by command can be used to perform the same function as order by command.

A subquery can be placed as follow:

• Where clause
• From clause
• Having clause

Syntax:

Select columns From table_name

Where col_nane operator (subquery).

Examples with Where clause:

Get names of all the students who scored more than class average marks.

Select name, marks from student

Where marks > (select avg(marks) from student);

Find the names of all students with even roll numbers.

Select name, rollno from student

Where rollno in (select rollno from student where rollno % 2 = 0);

Example with From clause:

Find the max numbers from the students of Delhi.

Select max(marks) 

From (select * from student where city = 'Delhi' ) as temp;

Example with select:

Select (select max(marks) from student ), name

From student.