You can use Fetch clause in both main and subqueries. Explained with SQL queries how to use Fetch clause.
Fetch to use in main SQL
SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
ORDER BY SALARY DESC
FETCH FIRST 20 ROWS ONLY;
You can also use FETCH FIRST n ROWS ONLY within a subquery.
SELECT * FROM EMP
WHERE EMPNO IN (
SELECT RESPEMP FROM PROJECT
ORDER BY PROJNO FETCH FIRST 3 ROWS ONLY)
Query transformations become most important for complex queries, especially complex queries that are created by query generators.
DB2 might apply the following types of transformations to SQL statements, among others:
- Removal of unneeded or pre-evaluated predicates
- Addition of generated predicates
- Removal of table references for certain joins
- Correlation or de-correlation of subqueries
- Conversion of subqueries to joins
- Simplification and removal of subselects in statements that contain UNION ALL operators