DB2 for z/OS - How a SELECT statement works

SQL statements, including SELECT, are made up a series of clauses that are defined by SQL as being executed in a logical order. SELECT statements allow users to definite and organize information that is retrieved from a specified table.

Start of change The following clause list shows the logical order of clauses in a statement: End of change

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

In addition:

Subselects are processed from the innermost to the outermost subselect. A subselect in a WHERE clause or a HAVING clause of another SQL statement is called a subquery.
The ORDER BY clause can be included in a subselect, a fullselect, or in a SELECT statement.
If you use an AS clause to define a name in the outermost SELECT clause, only the ORDER BY clause can refer to that name. If you use an AS clause in a subselect, you can refer to the name that it defines outside the subselect.

Example 1: Consider this SELECT statement, which is not valid:

SELECT EMPNO, (SALARY + COMM) AS TOTAL_SAL
  FROM EMP
  WHERE TOTAL_SAL> 50000;

The WHERE clause is not valid because DB2® does not process the AS TOTAL_SAL portion of the statement until after the WHERE clause is processed. Therefore, DB2 does not recognize the name TOTAL_SAL that the AS clause defines.

Example 2: The following SELECT statement, however, is valid because the ORDER BY clause refers to the column name TOTAL_SAL that the AS clause defines:

SELECT EMPNO, (SALARY + COMM) AS TOTAL_SAL
  FROM EMP
  ORDER BY TOTAL_SAL;