DBMS | Nested Queries in SQL

2.8

Prerequisites : Basics of SQL

 

In nested queries, a query is written inside a query. The result of inner query is used in execution of outer query. We will use STUDENT, COURSE, STUDENT_COURSE tables for understanding nested queries.

 

STUDENT

S_ID S_NAME S_ADDRESS S_PHONE S_AGE
S1 RAM DELHI 9455123451 18
S2 RAMESH GURGAON 9652431543 18
S3 SUJIT ROHTAK 9156253131 20
S4 SURESH DELHI 9156768971 18

 

COURSE

C_ID C_NAME
C1 DSA
C2 Programming
C3 DBMS

 

STUDENT_COURSE

S_ID C_ID
S1 C1
S1 C3
S2 C1
S3 C2
S4 C2
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.

     

    IN: If we want to find out S_ID who are enrolled in C_NAME ‘DSA’ or ‘DBMS’, we can write it with the help of independent nested query and IN operator. From COURSE table, we can find out C_ID for C_NAME ‘DSA’ or DBMS’ and we can use these C_IDs for finding S_IDs from STUDENT_COURSE TABLE.

     

    STEP 1: Finding C_ID for C_NAME =’DSA’ or ‘DBMS’

    Select C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME = ‘DBMS’

     

    STEP 2: Using C_ID of step 1 for finding S_ID

    Select S_ID from STUDENT_COURSE where C_ID IN

    (SELECT C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME=’DBMS’);

     

    The inner query will return a set with members C1 and C3 and outer query will return those S_IDs for which C_ID is equal to any member of set (C1 and C3 in this case). So, it will return S1, S2 and S4.

     

    Note: If we want to find out names of STUDENTs who have either enrolled in ‘DSA’ or ‘DBMS’, it can be done as:

    Select S_NAME from STUDENT where S_ID IN

    (Select S_ID from STUDENT_COURSE where C_ID IN

    (SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));

     

    NOT IN: If we want to find out S_IDs of STUDENTs who have neither enrolled in ‘DSA’ nor in ‘DBMS’, it can be done as:

    Select S_ID from STUDENT where S_ID NOT IN

    (Select S_ID from STUDENT_COURSE where C_ID IN

    (SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));

     

    The innermost query will return a set with members C1 and C3. Second inner query will return those S_IDs for which C_ID is equal to any member of set (C1 and C3 in this case) which are S1, S2 and S4. The outermost query will return those S_IDs where S_ID is not a member of set (S1, S2 and S4). So it will return S3.

 

  • 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. e.g.; If we want to find out S_NAME of STUDENTs who are enrolled in C_ID ‘C1’, it can be done with the help of co-related nested query as:

    Select S_NAME from STUDENT S where EXISTS

    ( select * from STUDENT_COURSE SC where S.S_ID=SC.S_ID and SC.C_ID=’C1’);

     

    For each row of STUDENT S, it will find the rows from STUDENT_COURSE where S.S_ID = SC.S_ID and SC.C_ID=’C1’. If for a S_ID from STUDENT S, atleast a row exists in STUDENT_COURSE SC with C_ID=’C1’, then inner query will return true and corresponding S_ID will be returned as output.

 

This article has been contributed by Sonal Tuteja.

 

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above

GATE CS Corner    Company Wise Coding Practice

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.

Recommended Posts:



2.8 Average Difficulty : 2.8/5.0
Based on 15 vote(s)










Writing code in comment? Please use ide.geeksforgeeks.org, generate link and share the link here.