HP 3000 Manuals HP 3000 Manuals
The SELECT Command Structure
The SELECT command consists of the keyword SELECT followed by one or more
clauses. Each of these is illustrated in this appendix. The SELECT
command uses the following format:
_____________________________________________________
| |
| SELECT Selectlist <=== required|
| FROM Tablename(s) <=== required |
| WHERE Search condition |
| GROUP BY Columnlist |
| HAVING Group search condition |
| ORDER BY Column names or numbers |
_____________________________________________________
You do not have to put each clause on a separate line; it is done here
for ease of reading.
Only the SELECT and FROM parts of the command are required. When the
other clauses are used, they must be used in the order shown.
The keyword SELECT is followed by a select list which identifies the
columns you want. You can select all of the columns, or a subset of the
columns in one or more tables or views.
The FROM clause follows the select list. It identifies the tables from
which columns are selected.
The WHERE clause specifies search conditions that determine which rows
are selected for the query result.
The GROUP BY clause specifies how information is grouped when you apply
an aggregate function to groups of rows. Aggregate functions are
discussed later in this section.
The HAVING clause limits which groups of rows are selected after being
operated on by an aggregate function.
The ORDER BY clause specifies the order in which rows should be sorted.
Some Definitions
Definitions of terms contained in the SELECT command are listed here.
These terms are illustrated in examples throughout this appendix.
* An expression specifies a value to be obtained from a column or
columns in a table. You can use one or more expressions in a select
list. An expression can consist of a column name, a constant, or an
aggregate function. If you use more than one column name, constant,
or aggregate function, they must be connected by an arithmetic
operator.
* An aggregate function computes a column value for groups of rows.
The aggregate function is applied to a column name or expression
which is enclosed in parentheses. ALLBASE/SQL uses five aggregate
functions:
* AVG is used with an expression to compute the arithmetic mean;
null values are ignored.
* SUM finds the total of the values in the expression; null values
are ignored.
* MAX finds the largest of the values in the expression; null
values are ignored.
* MIN finds the smallest of the values in the expression; null
values are ignored.
* COUNT counts the number of rows in all the columns or the number
of rows in specified columns. COUNT (*) counts all rows in all
columns, including rows containing null values.
* A constant is a specific numeric or character value. You can use a
numeric constant within an expression to perform a calculation.
* A search condition is made up of a single predicate or several
predicates, each connected by one of the logical operators AND or OR.
A predicate is a comparison of expressions that evaluates to true or
false. If a predicate evaluates to true for a row, the row is
selected for the query result. If the predicate evaluates to false
or is not known for a row, the row is not selected. You can also use
NOT with any of the predicates. When NOT is used, a row evaluates to
true if it does not meet the specified criteria, and to false if it
does meet the criteria.
The SELECT command can retrieve rows from several tables that have been
joined together. Refer to "Joining Multiple Tables" at the end of this
appendix.
More complex forms of the SELECT command exist. Several SELECT commands
can be nested in a UNION or UNION ALL command. In addition, subqueries
can be nested within a SELECT command. The chapter "SQL Queries" in the
ALLBASE/SQL Reference Manual contains many examples of these more complex
query forms.