/    /  DBMS – Subqueries in ‘where’ clause

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

 

Reference

Subqueries in ‘where’ clause