Javatpoint Logo
Javatpoint Logo

Nested Query In SQL

In nested query, a query is implemented inside an another query in SQL. The result of inner query is used to implement the query of outer query. In this query we will use three tables to implement and understand nested query in SQL. The tables are Student, Course, S_Course.


The table contains the given information:

S1 Aman Noida 8929403 19
S2 Raghav Delhi 5687030 21
S3 Harsh Gurugram 4020295 18
S4 Harshit Noida 9583920 15

Use the given query to create the given table:


The table contains the given information:

C_ID Cour_Name
C1 English
C2 Maths
C3 Science

Use the given insert query to create the above table:


S1 C3
S1 C2
S2 C3
S3 C3
S3 C1
S4 C2

Use this query to create the S_Course table:

Types of Nested Queries

Two types of Nested Queries can be implemented in SQL. These are as follows:

Independent Nested Queries

In independent nested queries, the query execution begins from the innermost query and moves to the outermost query. The result of the inner query is not affected by the operations on the inner query. The results computed in the inner query are used as a parameter in the outer query. Several operations that are used to implement independent nested queries include IN, NOT IN, ALL, and ANY.

Example: Consider a situation, where you want to get the S_ID of the students who want to enroll in the C_Name English or Maths, we can implement a query to retrieve the above information by using an independent nested query and IN operator. From the Course table, the user can determine the C_ID for C_Name English or Maths and use the respective C_ID to find the S_ID from the S_Course table.

Steps to retrieve the S_ID for the student who has enrolled for the English or Maths courses.

Step 1: First find the C_ID for the respective courses. The courses include Programming and DSA. Implement the given query to retrieve the above result.

STEP 2: Once the first query is implemented it will retrieve the C_ID from the Course table and use the S_Course table to retrieve the S_ID.


Nested Query In SQL

The inner query that is the query enclosed in the brackets will return the respective C_ID in the above case C1 or C2, the outer query will use the retrieved C_ID to retrieve the S_ID. The query will use the S_Course table to retrieve the result. Here S1, S3, and S4 match the criteria.

The user can also retrieve the students names that are enrolled in English and Maths courses. It can be retrieved by using two nested queries. The query is as follows:


Nested Query In SQL

NOT IN: if the user wants to retrieve the S_ID for all the students except the ones enrolled in English or Maths. Then the below query will return the results:


Nested Query In SQL

Here you can see there are two inner queries. The innermost query will return the C_ID for the English and Maths course that is C1 and C2. then the second inner query will retrieve the S_ID for all the students enrolled in the above course then finally the outermost query will retrieve all the S_IDs except the one retrieved in the previous statement. In this case, it is S2.

Co-related Nested Queries

In this type queries the results retrieved from the inner query are dependent on the rows which are being implemented in the outer query.

For example: If the user wants to get the students' names who are enrolled in the C_ID=3 then the user can perform a co-related query to get the required results. The query is as follows;


Nested Query In SQL

The above query will be executed for each record of Student S, it will find every record that meets the condition S.S_ID = SC.S_ID and SC.C_ID=3. If for a S_ID from Student S, at least a record is present S_Course SC with C_ID=3, then the inner query will become true, and the corresponding S_ID will be returned.

Next TopicSQL Join Example

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA