/    /  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