Nested Query In SQLIn 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:
Use the given query to create the given table: Course The table contains the given information:
Use the given insert query to create the above table: S_Course
Use this query to create the S_Course table: Types of Nested QueriesTwo types of Nested Queries can be implemented in SQL. These are as follows: Independent Nested QueriesIn 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. 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: 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: 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 QueriesIn 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; 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 |