SUBQUERIES-SELEGT statements - IBM Mainframe

Sub queries are nested SELEGT statements. Or in other words, sub queries are SELECT...FROM... WHERE... expressions nested inside another such expression. A subquery enables a user to base the search criteria of one SELECT statement on the results of another SELECT statement.

Even though, there are many ways in which sub queries can be formed, they typically are expressed as one SELECT statement connected to another in one of the following ways:

  • Using IN or NOT IN predicate
  • Specifying the equality(=) or inequality(o) predicate
  • Specifying a predicate using a comparative operator (<,>, <=,>= ) Following examples illustrate the use of sub queries:

SUBQUERIES-SELEGT statements

DB2 processes this SQL by first evaluating the nested query and will retrieve all the depart numbers in the employee table where the salary is more than 20000. Say the departments which have employees whose salary is more than 20000 are Dl, D3 and D4 (Here we assume that there are no duplicates, if duplicates exists that can be eliminated using a SELECT DISTINCT deptno..). SO the above query transforms into:

query transforms

Here also if you want to eliminate the duplicates the DISTINCT clause can be used.

eliminate the duplicates the DISTINCT clause

The important thing to remember while using the = predicate is that if the nested SELECT returns more than one value the query will fail. So for the above query to work there should be only one department with name 'CONSULTANCY'.

SUBQUERIES-SELEGT statements

This is a specialized form of subquery called a correlated subquery, which provides further level of flexibility by permitting the nested SELECT statement to refer back to columns in the previous SELECT statements. Correlated sub queries differ from the normal sub queries in that the nested SELECT statement refers back to the table in the first SELECT statement. The above query gets the name and salary of all the employees whose salaries greater than the average salary of his department.

In other words a correlated subquery is one whose value depends upon some variable that receives its value in some outer query. A non-correlated subquery, as said before is evaluated in a bottom-to-top manner. That is the inner most query is evaluated first. But a correlated subquery is resolved in a top-to-bottom fashion. The top most query is analyzed and based on that result the next query is initiated. Such a subquery has to be evaluated repeatedly, once for each value of the variable in question, instead of once and for all.

Subquery and Outer Query Referring to Same Table

The subquery and the outer query, for that matter the correlated subquery and the outer query can refer to the same table. For example, if you want to get the supplier numbers for suppliers who supply at least one part supplied by supplied S2. The SQL will be:

SELECT DISTINCT S# FROM SP WHERE P# IN (SELECT P# FROM SP WHERE S#=,S2');

This query will first get all the Part numbers from the shipment table where the Supplier number is equal to S2 and then using those results will evaluate the outer query which will give you the Supplier numbers who supplies at least one part supplied by supplier S2.

For an example where a correlated subquery and outer query using the same table, consider you want to find out the part numbers supplied by more than one supplier. The SQL will be something like:

Subquery and Outer Query Referring to Same Table

The above query will be executed as follows. For each row SPX of table SP, the P# value is extracted, if and only if that P# value appears in some row SPY of table SP whose S# value is not equal to the S# value in row SPX. Note that at least one explicit range variable must be used in this query.

However, the correlated subquery is no the only way of obtaining the above results. The same results can be achieved by using a GROUP BY and HAVING clause as follows:

GROUP BY and HAVING clause as follows


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics