Difference between Subquery, Nested Subquery and Correlated Subquery

Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Suppose we have a two tables Student and courses whose structure is given below:-

create table Student (Studentid int identity(1,1), Firstname nvarchar(200), Lastname nvarchar(200),Email nvarchar(100))

create table Course (Courseid int identity(1,1), Coursename nvarchar(250), CourseAdmin int)

Now suppose we insert the following data into these tables:-

For table Student

insert into Student values ('Atul','Bajaj', 'atul@abc.com' )

insert into Student values ('Vivek','Johari', 'vivek@abc.com' )

insert into Student values ('Ankur','Johari', 'ankur@abc.com' )

insert into Student values ('Tarveen', 'Kaur', 'Tarveen@abc.com')

For table Course

Insert into Course values('Oracle',2)

Insert into Course values('Automation',4)

Insert into Course values('Java',2)

Insert into Course values('QTP',4)

Now the query to see all the data from the table student and course is given below:-

Select * from student

Select * from Course

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

For example, if we want to find the name of the course Admin of the course “Oracle”, then the following subquery will be used:-

select Firstname+' '+Lastname from student where studentid in (select courseadminid from course where coursename ='Oracle')

Result:-

In this example, the sql statement “select courseadminid from course where coursename ='Oracle'” is a subquery.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Let us suppose we have another table called “StudentCourse” which contains the information, which student is connected to which Course. The structure of the table is:-

create table StudentCourse( StudentCourseid int identity(1,1), Studentid int, Courseid int)

The Query to insert data into the table “Studentcourse” is

Insert into StudentCourse values(1,3)

Insert into StudentCourse values(2,1)

Insert into StudentCourse values(3,2)

Insert into StudentCourse values(4,4)

Note: - We don’t need to insert data for the column StudentCourseid since it is an identity column.

Now, if we want to get the list of all the student which belong to the course “Oracle”, then the query will be,

select Firstname, lastname from student where studentid in (select studentid from studentcourse where courseid in (select courseid from course where coursename='Oracle'))

Result:-

In this example we use the nested subquery since the subquery “select courseid from course where coursename='Oracle'” is itself contained in the another subquery(Parent Subquery) “select studentid from studentcourse where courseid in (select courseid from course where coursename='Oracle')”.

Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Suppose we want to get the details of the Courses (including the name of their course admin) from the Course table, we can use the following query:-

select Coursename ,Courseadminid,(select Firstname+' '+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course

Result:-

Here in this example the “select Firstname+' '+Lastname  from student where studentid=Course.courseadminid” is called the correlated subquery since the outcome of this subquery is depends on the column courseadminid of the parent query. This means that the correlated subquery will be executed for each row selected by the parent query.

It is not necessary that the column on which the correlated query is depended is included in the selected columns list of the parent query. For example the   below query will also works even the column courseadminid on which the correlated query is depends , is not included in the selected columns list of the parent query.

select Coursename ,(select Firstname+' '+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course

Results:-