This concludes the discussion of the SQL queries and the SELECT statement that began in Chapter 6. As described in Chapters 6-9, the clauses of the SELECT statement provide a powerful, flexible set of capabilities for retrieving data from the database. Each clause plays a specific role in data retrieval:
- The FROM clause specifies the source tables that contribute data to the query results. Every column name in the body of the SELECT statement must unambiguously identify a column from one of these tables, or it must be an outer reference to a column from a source table of an outer query.
- The WHERE clause, if present, selects individual combinations of rows from the source tables to participate in the query results. Subqueries in the WHERE clause are evaluated for each individual row.
- The GROUP BY clause, if present, groups the individual rows selected by the WHERE clause into row groups.
- The HAVING clause, if present, selects row groups to participate in the query results. Subqueries in the HAVING clause are evaluated for each row group.
- The SELECT clause determines which data values actually appear as columns in the final query results.
- The DISTINCT keyword, if present, eliminates duplicate rows of query results.
- The UNION operator, if present, merges the query results produced by individual SELECT statements into a single set of query results.
- The ORDER BY clause, if present, sorts the final query results based on one or more columns.
The SQL2 query expression capabilities add row-valued and table-valued expressions and INTERSECT and EXCEPT operations to the SQL1 capabilities. The fundamental flow of query processing is not changed, but the capability to express queries within queries is greatly enhanced.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.