Hi sidu,
In nested queries, a query is written inside a query. The result of inner query is used in execution of outer query.
Refer below example for understanding nested queries.
SQL
DECLARE @Course AS TABLE(CourseId VARCHAR(10),CourseName VARCHAR(20))
INSERT INTO @Course VALUES('C1','DSA')
INSERT INTO @Course VALUES('C2','Programming')
INSERT INTO @Course VALUES('C3','DBMS')
DECLARE @StudentCourse AS TABLE(StudentId VARCHAR(10),CourseId VARCHAR(10))
INSERT INTO @StudentCourse VALUES('S1','C1')
INSERT INTO @StudentCourse VALUES('S1','C3')
INSERT INTO @StudentCourse VALUES('S2','C1')
INSERT INTO @StudentCourse VALUES('S3','C2')
INSERT INTO @StudentCourse VALUES('S4','C2')
INSERT INTO @StudentCourse VALUES('S4','C3')
There are mainly two types of nested queries:
Independent Nested Queries
In independent nested queries, query execution starts from innermost query to outermost queries. The execution of inner query is independent of outer query, but the result of inner query is used in execution of outer query. Various operators like IN, NOT IN, ANY, ALL etc are used in writing independent nested queries.
SELECT DISTINCT StudentId
FROM @StudentCourse
WHERE CourseId IN(SELECT CourseId FROM @Course WHERE CourseName = 'DSA' OR CourseName = 'DBMS')
SELECT DISTINCT StudentId
FROM @Student
WHERE StudentId NOT IN
(SELECT StudentId
FROM @StudentCourse
WHERE CourseId IN
(SELECT StudentId
FROM @Course
WHERE CourseName='DSA' OR CourseName='DBMS'));
Co-related Nested Queries
In co-related nested queries, the output of inner query depends on the row which is being currently executed in outer query.
SELECT StudentName
FROM @Student S
WHERE EXISTS(SELECT * FROM @StudentCourse SC WHERE S.StudentId=SC.StudentId and SC.CourseId='C1')