Subqueries

INTRODUCTION:

DB2 allows us to write queries within a query and this concept is called sub-querying. This is basically just writing a nested SQL inside another SQL statement.
Sub-querying allows us to look up data on a file based on a subset data from same/another file in the system.

Sub query can be written in following places:

• In the SELECT clause
• In the FROM clause
• In the WHERE clause using IN/NOT IN/ANY/ALL/EXISTS/NOT EXISTS keywords.

Let us use the following tables to understand subqueries and its possibilities better.

STUDMAST:

A student master file/table.

SUBMAST:

A subject master file/table which holds minimum marks to pass against each subject.

STUDMARKS:

A file/table to hold marks and results that each student has attained against each subject.

1. SUBQUERY IN SELECT CLAUSE:

Let us now look at the table STUDMARKS where all data is available, but nothing is readily understandable. No one can say which student scored how much against what subject just with the help of this single table.

We will now make an SQL with subqueries to display ‘Student Full Name’ and ‘Subject Description’, so that it becomes easier to understand the data that is presented.

Select Marks.Stud_Id, 
(Select (Trim(Stud.Stud_Fname)||' '||Trim(Stud.Stud_Lname)) From DEMO.STUDMAST Stud
Where Stud.Stud_Id = Marks.Stud_ID) As Student_Name,  
(Select Sub.Sub_Desc From DEMO.SUBMAST Sub Where Sub.Sub_Id = Marks.Sub_Id),
Marks.Marks, Marks.Results
From  DEMO.STUDMARKS Marks

This query would fetch name and subject description from tables STUDMAST and SUBMAST respectively using the respective key values and return text.

QUERY RESULT:

This makes the data much easier to understand for everybody.

2. SUBQUERY IN FROM CLAUSE:

Let’s say the above data needs to be filtered out more and we need to only display the records of students who have failed, the subset can be fetched as follows.

Select Marks.Stud_Id,
(Select (Trim(Stud.Stud_Fname)||' '||Trim(Stud.Stud_Lname)) From DEMO.STUDMAST Stud 
Where Stud.Stud_Id = Marks.Stud_ID) As Student_Name, 
(Select Sub.Sub_Desc From DEMO.SUBMAST Sub Where Sub.Sub_Id = Marks.Sub_Id),
Marks.Marks, Marks.Results
From (Select * From DEMO.STUDMARKS Where Results = 'FAIL') As Marks

Though in this case the result could be achieved simply by placing the condition in the where clause. In real life, this result set could be narrowed down using complex queries over multiple files/tables and use that subset feed to out query.

3. SUBQUERY IN WHERE CLAUSE:

1. USING IN/NOT IN:A simple SQL with a sub query to just give the names of students who have failed will be as follows:

   Select * From  DEMO.STUDMAST Stud Where Stud.Stud_Id In 
   (Select Stud_Id From DEMO.STUDMARKS Where Results = 'FAIL')
   

   QUERY RESULT:

   

   A query to get the name of students who have not failed would go as follows:

   

   Select * From  DEMO.STUDMAST Stud Where Stud.Stud_Id Not In 
   (Select Stud_Id From DEMO.STUDMARKS Where Results = 'FAIL')
   

   QUERY RESULT:

   

2. USING EXISTS/NOT EXISTS:The subquery creates a result set which becomes a lookup for the main query to filter out and display the final results. The result set achieved by using IN/NOT IN can also be achieved with the EXISTS/NOT EXISTS clause.Find the names of students who have failed as follows

   Select * From  DEMO.STUDMAST Stud Where Exists
   (Select * From DEMO.STUDMARKS Where Results = 'FAIL' And Stud_Id = Stud.Stud_ID)
   

   QUERY RESULT:

   

   Find the names of students who have not failed as follows

   

   Select * From  DEMO.STUDMAST Stud Where Not Exists
   (Select * From DEMO.STUDMARKS Where Results = 'FAIL' And Stud_Id = Stud.Stud_ID)
   

   QUERY RESULT:

   

3. USING ANY:The ANY keyword when used before a subquery determines if any value in the result subset matches to the any of the values in the left hand side (or the main query) of subquery and returns result if TRUE.The subquery for finding out if there are any students who have scored above 95 is as follows.

   Select * From  DEMO.STUDMAST Stud Where Stud. 
   Stud_ID  = ANY(Select Stud_ID From  DEMO.STUDMARKS Where Marks >= 95)
   

   QUERY RESULT:

   

4. USING ALL:The ALL keyword when used before a subquery returns all values in the result subset that matches to the values in the left hand side (or the main query) of subquery and returns result if TRUE.The below query returns the list of subjects per student in which the students have scored above the average of the total marks per subject.

   Select Mark.Stud_ID, (Select Sub.Sub_Desc From DEMO.SUBMAST Sub Where Sub.Sub_Id = Mark.Sub_Id), Mark.Marks,
   Mark.Results From  DEMO.STUDMARKS Mark Where Mark.Marks >= ALL(Select AVG(MARKS) From DEMO.STUDMARKS GROUP BY Sub_Id)
   

   QUERY RESULT: