Subqueries | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 4. Advanced SQL Coding

Subqueries

A subquery is an SQL statement specifying a search condition that contains either a subselect or a fullselect, and is used inside another SQL statement. A subquery can include search conditions of its own. Each search condition can also contain subqueries. Any SQL statement can contain a hierarchy of subqueries. The SQL query block that contains subqueries is at a higher level than the subqueries they contain. This is important for how columns are referenced between levels.

A subquery can contain search conditions that reference not only columns of the tables identified by its FROM clause, but also columns of tables identified at any higher level. The reverse, however, is not true. A query at a higher level cannot reference columns from a subquery, although outer query columns can be compared to the resultant columns from a subquery. A reference within a subquery to a column of a table from a higher level is called a correlated referenc e (or correlation predicate).

When specified in a search condition, many operands can be used and are generally categorized as either single-result, IN list, or existence subqueries.

NOTE

Quantified predicates are another form and compare a value (or values) to a collection of values by preceding the comparison operator (=, >, <, etc.) with either ANY, SOME, or ALL. To avoid confusion, quantified predicates will be ignored in this text.

This example demonstrates a single-result subquery to return the test center with the highest number of seats.

 SELECT TCID,name,NOSEATS  FROM test_center WHERE NOSEATS= (SELECT MAX(NOSEATS) FROM test_CENTER)

The subquery can return only one result, either a null if there are no qualifying rows in the subquery table or a value that represents the maximum value of the column noseats in the TEST_CENTER table. This value is compared with the outer table rows, and only those rows that equal the maximum value for NOSEATS are returned.

This type of subquery is known as a noncorrelated subquery, because there is no correlation between the subquery and the outer query. The subquery is independent of the outer query and merely returns a single result to the outer query for comparison.

To demonstrate an IN list subquery, consider the difficulty of producing a report on the number of DB2 Certification exams if the name and number of the exams is unknown. The following SQL produces a report that includes the names and phone numbers of all the test centers that have candidates registered for the DB2 Certification exams. In the example, we use the word "db2" as the search string to find the numbers of the DB2 Certification program exams:

 SELECT DISTINCT name,phone  FROM test_center tc, test_taken tt WHERE tc.tcid=tt.tcid AND number IN (SELECT number FROM test WHERE name LIKE 'DB2%')

In this example, the subquery appears as part of the IN clause. In the subquery, we are retrieving all the numbers for those tests that have the word "db2" in their name.

NOTE

You will never see the subquery output. The subquery is merely to create a list of values that will be used later by the outer SELECT statement.

Both previous examples have demonstrated a noncorrelated subquery. The most common forms of noncorrelated subqueries are the single-result and the IN list subqueries.

NOTE

The third alternative, an EXISTS subquery, is generally not coded for a noncorrelated subquery. Since an EXISTS subquery returns a true or false, it will be either true for every row of the outer table or false for every row of the outer table.

In contrast, a correlated subquery is a query in which the subquery references values of the immediate outer SELECT or any subselect at a higher level. The most common forms are the single-result and the EXISTS correlated subquery. Whereas the noncorrelated subquery is evaluated once and a result returned to the outer query, the correlated subquery is potentially evaluated once for every outer row.

NOTE

The IN subquery is generally not coded for a correlated subquery due to the potential for the IN list to be built numerous times.

As with the noncorrelated example, a single-result correlated subquery will return a single result for every execution. The difference is that a noncorrelated subquery is executed once rather than many times.

Consider a query to return the highest score for each test and the associated details. The following correlated subquery returns the desired result.

 SELECT C.FNAME, TT.NUMBER, TT.DATE_TAKEN, TT.SCORE  FROM TEST_TAKEN TT, CANDIDATE C WHERE C.CID = TT.CID AND TT.SCORE = (SELECT MAX(TT2.SCORE)  FROM TEST_TAKEN TT2  WHERE TT2.NUMBER = TT.NUMBER) FNAME  NUMBER   DATE_TAKEN SCORE ------ -------- ---------- ----- Bill      501   02/02/2000    73 Susan     500   03/01/2000    82 Susan     502   03/02/2000    92   3 record(s) selected.

For each outer row, the test number for that row is passed to the subquery, and the maximum score for that test is returned to the outer query for comparison. If the maximum score equals the score of the current row, then that row is returned. If not, then the row is discarded. It is possible for the outer query to return more than one row for each test if more than one person achieves the maximum score.

The next example is to demonstrate an EXISTS subquery in which we rewrite the previous noncorrelated IN list SQL example as a correlated subquery. This query produces a report that includes the names and phone numbers of all the test centers that have candidates registered for the DB2 Certification exams. To match the problem description to the query, the problem description can be rewritten as follows : Produce a report that includes the names and phone numbers of all test centers where there exists a candidate registered for the DB2 Certification exams.

 SELECT name,phone  FROM test_center tc WHERE exists (SELECT * FROM test t, test_taken tt WHERE t.name LIKE 'DB2%' and   t.number = tt.number and   tt.tcid=tc.tcid)

NOTE

As with standard WHERE clause predicates, the reverse condition can be achieved by prefixing the condition with the NOT keyword, such as NOT EXISTS or NOT IN. Remember, however, that a null will not qualify against either an IN or a NOT IN condition.

Observe the WHERE clause in the subquery in this example. It references a table that is listed in the outer FROM clause.

The EXISTS subquery will return a true or a false to the outer query. If true, then the outer row is considered to be a match and the row is returned to the application. The correlated EXISTS subquery will terminate after the first true condition is found or when a false can be determined, as the requirement is only to return a true or false condition, not to list how many occurrences match.

NOTE

Since DB2 prunes the SELECT list for EXISTS subqueries, it is not important for access path or performance what columns are listed in the subquery select list of an EXISTS subquery. Therefore, whether you specify SELECT * or SELECT 1, DB2 will still return a true or false and will not retrieve all columns as an outer query would if SELECT * were specified.


Team-Fly

Top