Compose advanced SELECT statements

This section increases the scope of what you can do with the SELECT statement and enables you to perform more complex database queries and data manipulation. Compose SELECT statements, focused on five of the clauses in the SELECT statement syntax. This section adds the GROUP BY clause and the HAVING clause. You can use the GROUP BY clause with aggregate functions to organize rows returned by the FROM clause. You can include a HAVING clause to place conditions on the values that the GROUP BY clause returns.

This section also extends the earlier discussion of joins. It illustrates self-joins, which enable you to join a table to itself, and four kinds of outer joins, in which you apply the keyword OUTER to treat two or more joined tables unequally. It also introduces correlated and uncorrelated subqueries and their operational keywords, shows how to combine queries with the UNION operator, and defines the set operations known as union, intersection, and difference.

Examples in this section show how to use some or all of the SELECT statement clauses in your queries. The clauses must appear in the following order:
  1. Projection
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
  7. INTO TEMP

For an example of a SELECT statement that uses all these clauses in the correct order, see Query.

An additional SELECT statement clause, INTO, which you can use to specify program and host variables in SQL APIs, is described in SQL programming, as well as in the publications that come with the product.

This section also describes nested SELECT statements, in which subqueries are specified within the Projection, FROM, or WHERE clauses of the main query. Other sections show how SELECT statements can define and manipulate collections, and how to perform set operations on query results.