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.

Student

The table contains the given information:

S_ID	S_NAME	S_ADD	S_No	S_AGE
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:

CREATE TABLE Student (
  S_ID INT PRIMARY KEY,
  S_NAME VARCHAR(50),
  S_ADD VARCHAR(50),
  S_No INT,
  S_AGE INT
);

INSERT INTO Student (S_ID, S_NAME, S_ADD, S_No, S_AGE)
VALUES
  (1, 'Aman', 'Noida', 8929403, 19),
  (2, 'Raghav', 'Delhi', 5687030, 21),
  (3, 'Harsh', 'Gurugram', 4020295, 18),
  (4, 'Harshit', 'Noida', 9583920, 15);

Course

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:

CREATE TABLE Course (
  C_ID INT PRIMARY KEY,
  Cour_Name VARCHAR(50)
);

INSERT INTO Course (C_ID, Cour_Name)
VALUES
  (1, 'English'),
  (2, 'Maths'),
  (3, 'Science');

S_Course

S_ID	C_ID
S1	C3
S1	C2
S2	C3
S3	C3
S3	C1
S4	C2

Use this query to create the S_Course table:

CREATE TABLE S_Course (
  S_ID INT,
  C_ID INT,
  FOREIGN KEY (S_ID) REFERENCES Students(S_ID),
  FOREIGN KEY (C_ID) REFERENCES Course(C_ID)
);

INSERT INTO S_Course (S_ID, C_ID)
VALUES
  (1, 3),
  (1, 2),
  (2, 3),
  (3, 3),
  (3, 1),
  (4, 2);

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.

Select S_ID from S_Course where C_ID IN
(SELECT C_ID from Course where Cour_Name = 'English' or Cour_Name= 'Maths');

Output:

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:

Select S_NAME from Student where S_ID IN
(Select S_ID from S_Course where C_ID IN
(SELECT C_ID from Course where Cour_Name = 'English' or Cour_Name='Maths'));

Output:

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:

Select S_NAME from Student where S_ID NOT IN
(Select S_ID from S_Course where C_ID IN
(SELECT C_ID from Course where Cour_Name = 'English' or Cour_Name='Maths'));

Output:

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;

Select S_NAME from Student  S where  EXISTS
( select * from S_Course SC where S.S_ID=SC.S_ID and SC.C_ID=3);

Output:

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

← prev next →