7.1.4  Subquery syntax

A subquery is a SELECT inside another query or statement. Therefore, 

subqueries can be found in other SELECT either in the column list, the WHERE 

clause, or the HAVING clause, and in addition in INSERT, UPDATE, and 

DELETE statements. In Example 7 6 a sequence of subqueries is shown in a 

DELETE statement.

Example 7 6   Example for subqueries an a DELETE statement 

DELETE FROM t1

WHERE col1 > ANY

  (SELECT COUNT(*) FROM t2

   WHERE NOT EXISTS

     (SELECT col3 FROM t3

      WHERE col3 =

         (SELECT col4 FROM t4 UNION SELECT 1 FROM

            (SELECT col5 ROM t5) AS t5 )));

Our project uses MySQL Version 4.0.17 and subqueries are supported in MySQL 

from Version 4.1, and it therefore has not been possible to do a comprehensive 

testing of subqueries. However, after studying the reference manual for MySQL, 

the subquery implementation seems quite similar to the DB2 implementation. 

Nothing has been found causing serious issues when moving from MySQL to 

DB2.

7.1.5  Grouping, having, and ordering

All ANSI SQL 92 standard grouping functions available in MySQL Version 4.0 are 

also available in DB2. In general no significant differences were found in the area 

of grouping, having, and ordering. However, beyond the SQL 92 standard DB2 

provides some interesting functionality that may enhance the ported application 

significantly. Please refer to DB2 UDB manual

 SQL Reference, Volume 2, 

SC09 4845.

Table 7 1 lists the differences in two databases and provides 

conversion examples.

 Chapter 7. Application porting 

159