SELECT Syntax

Note Most examples refer to the pubs sample database. The pubs sample database is included as a learning tool; it is created when the server is installed. To use the sample database, your system must have access to the pubs database. If you have a localized version of SQL Server and want to try the examples, drop the localized version of pubs and install the U.S. English version of pubs. To install the U.S. English version of pubs, run the INSTPUBS.SQL script with the isql command-line utility. This script can be found in the INSTALL directory of SQL Server. Also if other users have executed the examples in this chapter, it is likeley that the pubs database has been altered. This will change the output you see from some of the examples. You can reinstall the original pubs database at any time by running the INSTPUBS.SQL script.

To run the INSTPUBS.SQL script, from an operating-system prompt, type:

isql /Usa /Ppassword /Sserver -i\sql60\install\instpubs.sql

For details about isql, see the isql command-line utility in the Microsoft SQL Server Transact-SQL Reference.

The SELECT statement specifies the columns you want to retrieve. The FROM clause specifies the tables where the columns are located. The WHERE clause specifies which rows in the tables you want to see.

A simplified syntax for the SELECT statement is:

SELECT select_list
FROM table_list
WHERE search_conditions

For example, the following SELECT statement finds the first and last names of writers in the authors table who live in Oakland.

SELECT au_fname, au_lname
FROM authors
WHERE city = 'Oakland'

au_fname                au_lname
---------               --------------
Marjorie                Green
Dean                    Straight
Dirk                    Stringer
Stearns                 MacFeather
Livia                   Karsen

(5 row(s) affected)

The complete syntax of the SELECT statement includes the following phrases and keywords:

SELECT [ALL | DISTINCT] select_list
    [INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
    [[, {table_name2 | view_name2}[(optimizer_hints)]
    [..., {table_name16 | view_name16}[(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]

The clauses in a SELECT statement must be used in the order shown here. (For example, if the statement includes a GROUP BY clause and an ORDER BY clause, the GROUP BY clause precede the ORDER BY clause.)

The names of database objects must be qualified if there is ambiguity about which object is being referred to. You can uniquely identify a table or column by specifying other names that qualify it ¾ the database name, the owner's name, and the table name or view name for a column. Each of these qualifiers is separated from the next by a period, as shown here:

database.owner.table_name.column_name

For example, if the user sharon owns the authors table in the pubs database, the unique identifier of the city column in that table is:

pubs.sharon.authors.city

Since many 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. Although these elements are left out of the examples to promote readability, it is never wrong to include qualifiers.

Note that SELECT statement optimizer_hints are optional and delimited with a space, not a comma.

For example:

SELECT * FROM authors (HOLDLOCK TABLOCK)

You can order by either the column name, or by its ordinal number in the select list.

These two queries are equivalent:

SELECT au_frame, au_lname
FROM authors
ORDER BY au_lname

ORDER BY 2

For details about SELECT, see the SELECT statement in the Microsoft SQL Server Transact-SQL Reference.