In This Chapter

This chapter increases the scope of what you can do with the SELECT statement and enables you to perform more complex database queries and data manipulation. Composing SELECT Statements, focused on five of the clauses in the SELECT statement syntax. This chapter 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 chapter 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 chapter show how to use some or all of the SELECT statement clauses in your queries. The clauses must appear in the following order:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. ORDER BY
6. INTO TEMP

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

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