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.

Begin general-use programming interface information.

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

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
End general-use programming interface information.

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.
Begin general-use programming interface information.
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;
End general-use programming interface information.