Subqueries in ‘where’ clause
A query is said to be a ‘subquery’ if it is a part of the where / from clause of another query. It is used to fit a column against a set of rows.
Let us consider the following table R4
R4 : FACULTY-1
————————————————————
FNo FName DNo Qual Salary
————————————————————
522 Ria 21 Ph.D. 35000
524 Priya 22 MTech 30000
525 Sia 22 MTech 42000
527 Maggie 23 MTech 28000
530 Alex 23 MTech 32000
533 Ben 24 Ph.D. 33000
535 Becca 24 Ph.D. 32000
537 Justin 25 MTech 26000
539 Pat 25 MTech 24000
540 Jack 25 MTech 34000
————————————————————
Example-1 : Find the names of faculty, who are working in the DNo = 23.
General Query : Select FName
From FACULTY-1
Where DNo = 23 ;
Sub Query : Select FName
From FACULTY-1
Where FName in
(Select FName
From FACULTY-1
Where DNo = 23) ;
Output : FName
Maggie
Alex
Example-2 : Find the names of faculty, who are working in the IT dept.
Consider the following relation :
DEPT
DNo DName
21 CSE
22 IT
23 ECE
24 ME
25 EEE
To develop the SQL queries, we need the following relations.
DEPT
FACULTY-1
General Query : Select FName
From FACULTY-1, DEPT
Where FACULTY-1.DNo = DEPT.DNo
andDName = ‘IT’ ;
Sub Query : Select FName
From FACULTY-1
Where DNo =
(Select DNo
From DEPT
Where DName = ‘IT’ ) ;
Output : FName
NN
VV
Subqueries in ‘where’ clause