The select syntax can be simpler or more complex than shown in the previous example. A simple select statement contains only the select clause; the from clause is almost always included, but is necessary only in select statements that retrieve data from tables. All other clauses, including the where clause, are optional. The full syntax of the select statement includes these phrases and keywords:
select [all | distinct] select_list
[into [[database.]owner.] table_name]
[from [[database.]owner.]{view_name|table_name
[(index {index_name | table_name}
[parallel [degree_of_parallelism] ]
[prefetch size] [lru | mru])]}
[holdlock | noholdlock] [shared]
[,[[database.]owner.]{view_name| table_name
[(index {index_name | table_name}
[parallel [degree_of_parallelism] ]
[prefetch size] [lru | mru])]}
[holdlock | noholdlock] [shared]]...]
[where search_conditions]
[group by [all] aggregate_free_expression
[, aggregate_free_expression]...]
[having search_conditions]
[order by
{[[[database.]owner.]{table_name.|view_name.}]
column_name | select_list_number | expression} [asc | desc]
[,{[[[database.]owner.]{table_name|view_name.}]
column_name | select_list_number | expression}
[asc | desc]]...]
[compute row_aggregate(column_name)
[, row_aggregate(column_name)]...
[by column_name [, column_name]...]]
[for {read only | update [of column_name_list]}]
[at isolation {read uncommitted | read committed | serializable}]
[for browse]
Use the clauses in a select statement in the order shown above. For example, if the statement includes a group by clause and an order by clause, the group by clause must precede the order by clause.
Qualify the names of database objects if there is ambiguity about which object is being referred to. If several columns in multiple tables are called “name”, you may have to qualify “name” with the database name, owner name, or table name. For example:
select au_lname from pubs2.dbo.authors
Since the examples in this chapter involve single-table queries, column names in syntax models and examples are usually not qualified with the names of the tables, owners, or databases to which they belong. These elements are left out for readability; it is never wrong to include qualifiers. The remaining sections in this chapter analyze the syntax of the select statement in more detail.
This chapter describes only some of the clauses and keywords included in the syntax of the select command. The following clauses are discussed in other chapters:
group by, having, order by, and compute are described in Chapter 3, “Using Aggregates, Grouping, and Sorting.”
into is described in Chapter 8, “Creating Databases and Tables.”
at isolation is described in Chapter 19, “Transactions: Maintaining Data Consistency and Recovery.”
The holdlock, noholdlock, and shared keywords (which deal with locking in Adaptive Server) and the index clause are described in the Performance and Tuning Guide. For information about the for read only and for update clauses, see the declare cursor command in the Reference Manual.
The for browse clause is used only
in DB-Library applications. See the Open Client DB-Library/C
Reference Manual for details. See also “Using browse mode instead of cursors”.
