Nested query and Sub query example in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

hi All

tell me the Scenario when we need to use nested query 

when we need to use nested query or sub query in sql with real time examples

 

Posted one year ago

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')