Transact-SQL User's Guide

Home

Chapter 2 Queries: Selecting Data from
a Table

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

What are queries?

A SQL query requests data from the database and receives the results. This process, also known as data retrieval, is expressed using the select statement. You can use it for selections, which retrieve a subset of the rows in one or more tables, and you can use it for projections, which retrieve a subset of the columns in one or more tables.

A simple example of a select statement is:

select select_list 
from table_list 
where search_conditions

The select clause specifies the columns you want to retrieve. The from clause specifies the tables to search. The where clause specifies which rows in the tables you want to see. For example, the following select statement finds the first and the last names of writers living in Oakland from the authors table, in the pubs2 database.

select au_fname, au_lname 
from authors 
where city = "Oakland"

Results of this query appear in columnar format:

au_fname        au_lname

--------------  ----------- 
Marjorie        Green 
Dick            Straight 
Dirk            Stringer 
Stearns         MacFeather 
Livia           Karsen
 
(5 rows affected)

select syntax

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 7, "Creating Databases and Tables."
at isolation is described in Chapter 18, "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".


	Expand Search