SELECT Statement

For SQL Server 6.5 information, see SELECT Statement, CUBE Operator, and ROLLUP Operator in What's New for SQL Server 6.5.

Retrieves rows from the database.

Syntax

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]

where

ALL
Retrieves all rows in the results. ALL is the default.
DISTINCT
Includes only unique rows in the results. Null values are considered equal for the purposes of the DISTINCT keyword; only one NULL is selected no matter how many are encountered.
select_list
Specifies the columns to select. Can be one or more of the following:

Note When the select_list includes a variable assignment(s), it cannot be combined with data-retrieval operations.

INTO new_table_name
Creates a new table based on the columns specified in the select_list and the rows chosen in the WHERE clause. To select into a permanent table, the select into/bulkcopy option must be on (by executing the sp_dboption system stored procedure). By default, the select into/bulkcopy option is off in newly created databases. The new table name (new_table_name) must follow the same rules as table_name (described later in this section) with these exceptions:

SELECT INTO is a two-step operation. The first step creates the table. The user executing the statement must have CREATE TABLE permission in the destination database. The second step inserts the specified rows into the new table. If the second step fails for any reason (hardware failure, exceeding a system resource, and so on), the new table will exist but have no rows.

You can use SELECT INTO to create an identical table definition (different table name) with no data by having a false condition in the WHERE clause.

You cannot use SELECT INTO with the COMPUTE clause or inside a user-defined transaction. For details about user-defined transactions, see the Transactions topic.

When selecting an existing identity column into a new table, the new column inherits the IDENTITY property unless one of the following conditions is true:

If any of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If none of the conditions is true, the new table will inherit the identity column. All rules, restrictions, and so on, for the identity columns apply to the new table.

FROM
Indicates the specific table(s) and view(s) that are used in the SELECT statement. FROM is required except when the select_list contains only constants, variables, and arithmetic expressions (no column names). The FROM clause allows a maximum of 16 tables and views. Tables in subqueries are counted as part of this total.
table_name | view_name =
[[database.]owner.]{table_name. | view_name.}

Specifies the name(s) of the table(s) and view(s) used in the SELECT statement. If the list contains more than one table or view, separate the names with commas. If the table(s) or view(s) exist in another database(s), use a fully qualified table or view name (database_name.owner.object_name).

Each table_name or view_name can be given an alias, either for convenience or to distinguish the different roles that a table or view plays in a self-join or subquery. Aliases (when defined) must be used for any ambiguous column references and must always match the alias reference (the full table name cannot be used if an alias has been defined). To use an alias, specify the object name, and then a space, and then the alias name, like this:

SELECT au_lname, au_fname, title
FROM titles t, authors a, titleauthor ta
WHERE ta.title_id = t.title_id 
AND ta.au_id = a.au_id
ORDER BY title, au_lname, au_fname

The order of the tables and views after the FROM keyword does not affect the results set returned.

(optimizer_hints)
Indicate that a specific locking method, a specific index, or no index (table scan) should be used (by the optimizer) with this table for this SELECT. Although this is an allowable option, in most cases you should allow the optimizer to pick the best optimization method.

Important You can use the optimizer_hints in any combination, but some of them do not make sense to use together¾for example, TABLOCK and PAGLOCK. When multiple options are specified, separate them with a space; the more restrictive option will take precedence.

An optimizer_hint can be one (or more) of the following:

INDEX = {index_name | index_id}
Specifies the index name or ID to use for that table. An index_id of 0 forces a table scan, 1 forces the use of a clustered index (if one exists).
NOLOCK
Allows "dirty reads," which means that no shared locks are issued and no exclusive locks are honored. This can result in higher concurrency, but at the cost of lower consistency. If this option is specified, it is possible to read an uncommitted transaction or to read a set of pages that are rolled back in the middle of the read, so error messages might result. If you receive error messages 605, 606, 624, or 625 when NOLOCK is specified, resolve them as you would a deadlock error (1205) and retry your statement.
HOLDLOCK
Makes a shared lock more restrictive by holding it until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The HOLDLOCK option applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement in which it is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option.
UPDLOCK
Takes update locks instead of shared locks while reading the table and holds them until the end-of-command or end-of-transaction.
TABLOCK
Takes a shared lock on the table that is held until the end-of-command. If HOLDLOCK is also specified, the shared lock is held until the end of the transaction.
PAGLOCK
Takes shared page locks where a single shared table lock would normally be taken.
TABLOCKX
Takes an exclusive lock on the table that is held until the end-of-command or end-of-transaction.
FASTFIRSTROW
Causes the optimizer to use the nonclustered index if one matches the ORDER BY clause. With sorting enhanced by the new asynchronous read ahead capability, the optimizer picks table scan and sort over using a nonclustered index in many cases because the total throughput for the sort is faster. If there is a sort, the first row cannot be sent until totally sorted. With this method, if the sort is large, there may be a considerable delay before the first row appears. FASTFIRSTROW causes the optimizer to use the nonclustered index; the first row will appear quickly, but the total return time for this option may be slower.
WHERE clause =
WHERE search_conditions

Specifies the restricting conditions for the rows returned in the results set. There is no limit to the number of search_conditions that can be included in an SQL statement. For more information, see the Search Conditions topic.

GROUP BY clause =
GROUP BY [ALL] aggregate_free_expression
[, aggregate_free_expression]...
GROUP BY
Specifies the groups into which the table will be partitioned and, if aggregate functions are included in the select_list, finds a summary value for each group. You can refer to these new summary columns in the HAVING clause. The text and image datatypes cannot be used in a GROUP BY clause.

When a GROUP BY clause is used, each item in the select_list must produce a single value for each group. A table can be grouped by any combination of columns; however, you cannot group by a column heading¾you must use a column name or an expression. In Transact-SQL, any expression is valid (although not with column headings). With standard SQL, you can group only by a column.

You can use GROUP BY for a column or expression that does not appear in the select_list. Null values in the GROUP BY column are put into a single group.

The aggregate functions, which calculate summary values from the non-null values in a column, can be divided into two groups:
Scalar Aggregate functions are applied to all the rows in a table (producing a single value per function). An aggregate function in the select_list with no GROUP BY clause applies to the whole table and is one example of a scalar.
Vector Aggregate functions are applied to all rows that have the same value in a specified column or expression with the GROUP BY clause and, optionally, the HAVING clause (producing a value for each group per function).

For the details about aggregate functions, see the Functions topic.

ALL
Includes all groups in the results, even those that don't have any rows that meet the search_conditions.
aggregate_free_expression
Is an expression that includes no aggregate functions. Aggregate functions can be used in the select_list preceding the GROUP BY clause.

For the details about aggregate functions, see the Functions topic.

HAVING clause =
HAVING search_conditions

Specifies a different type of restriction for aggregate functions in the select_list; the search_conditions restrict the rows returned by the query but do not affect the calculation(s) of the aggregate function(s). When a WHERE clause is used, the search_conditions restrict the rows that are included in the calculation of the aggregate function but do not restrict the rows returned by the query. The text and image datatypes cannot be used in a HAVING clause.

There is no limit on the number of conditions that can be included in search_conditions. You can use a HAVING clause without a GROUP BY clause. When the HAVING clause is used with GROUP BY ALL, the HAVING clause negates the meaning of ALL.

ORDER BY clause =
ORDER BY {{table_name. | view_name.}column_name
| select_list_number | expression} [ASC | DESC]
[...{{table_name16. | view_name16.}column_name
| select_list_number | expression} [ASC | DESC]]

Sorts the results by columns. You can sort as many as 16 columns. In Transact-SQL, the ORDER BY clause can include items that do not appear in the select_list. You can sort by a column name, a column heading (or alias), an expression, or a number representing the position of the item in the select_list (the select_list_number). If you sort by select_list_number, the columns to which the ORDER BY clause refers must be included in the select_list. The select_list can be a single asterisk (*). If you use COMPUTE BY, you must also specify an ORDER BY clause.

Null values are sorted before all others, and text or image columns cannot be used in an ORDER BY clause. Subqueries and view definitions cannot include an ORDER BY clause, a COMPUTE clause, or the INTO keyword. However, through Transact-SQL extensions, you can sort by expressions and aggregates if you use their select_list_number in the ORDER BY clause.

COMPUTE clause =
COMPUTE row_aggregate(column_name)
[, row_aggregate(column_name)...]
[BY column_name [, column_name]...]
COMPUTE
Used with row aggregate functions (SUM, AVG, MIN, MAX, and COUNT) to generate control-break summary values. The summary values appear as additional rows in the query results, allowing you to see detail rows and summary rows within one results set. You can calculate summary values for subgroups, and you can calculate more than one aggregate function for the same group.

The COMPUTE clause cannot be used with INTO and cannot contain aliases for column names, although aliases can be used in the select_list.

The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on. The ORDER BY clause is optional only if you use the COMPUTE keyword without BY.

BY
Indicates that values for row aggregate functions are to be calculated for subgroups. Whenever the value of BY changes, row aggregate function values are generated. If you use BY, you must also use an ORDER BY clause. Listing more than one item after BY breaks a group into subgroups and applies a function at each level of grouping. The columns listed after COMPUTE clause must be identical to or a subset of those listed after ORDER BY clause, and must be in the same left-to-right order, start with the same expression, and not skip any expression.

For example, if the ORDER BY clause is:

ORDER BY a, b, c

The COMPUTE clause can be any (or all) of these:

COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a
FOR BROWSE
Allows you to perform updates while viewing data in client application programs using DB-Library.

A table can be browsed in an application under the following conditions:

Do not use the optimizer_hint HOLDLOCK in a SELECT statement that includes the FOR BROWSE option.

The FOR BROWSE option cannot appear in SELECT statements joined by the UNION operator.

Remarks

The length returned for text columns included in the select_list defaults to whichever is the smallest ¾ the actual size of the text, the default TEXTSIZE session setting, or the hardcoded application limit. To change the length of returned text for the session, use the SET statement. By default, the limit on the length of text data returned with a SELECT statement is 4K.

To retrieve data from remote SQL Servers, you can call remote stored procedures. For more information, see the CREATE PROCEDURE and EXECUTE statements.

Using the GROUP BY clause and the HAVING clause

The following list shows the requirements for processing a SELECT with the GROUP BY clause and the HAVING clause, and it shows how the rows returned in the results set are derived:

  1. The WHERE clause excludes rows that do not meet its search_conditions.
  2. The GROUP BY clause collects the surviving rows into one group for each unique value in the GROUP BY clause. Omitting the GROUP BY clause creates a single group for the whole table.
  3. The HAVING clause excludes rows that do not meet its search_conditions. The HAVING clause tests only rows, but the presence or absence of a GROUP BY clause can make the behavior of a HAVING clause appear contradictory. For example:
  4. Aggregate functions specified in the select_list calculate summary values for each surviving group.

For the GROUP BY clause, the HAVING clause, and aggregate functions to accomplish the goal of one row and one summary value per group, ANSI-standard SQL requires:

Transact-SQL extensions to standard SQL make displaying data more flexible by allowing references to columns and expressions that are not used for creating groups or summary calculations. For example:

Permission

SELECT permission defaults to the owner of the table or view, who can grant it to other users using the GRANT statement. If the INTO clause is used to create a permanent table, then the user must have CREATE TABLE permission in the destination database.

Examples

A.    Simple SELECT: All Rows, All Columns

This example returns all rows (no WHERE clause) and all columns (*) from the publishers table in the pubs database.

SELECT * FROM publishers
B.    Simple SELECT: Subset of Columns, All Rows

This example returns all rows (no WHERE clause) and only a subset of the columns (pub_id, pub_name, city, state) from the publishers table in the pubs database.

SELECT pub_id, pub_name, city, state
        FROM publishers
C.    Simple SELECT: Subset of Rows, Subset of Columns

This examples returns only the rows where the advance given is less than $10,000 and there are current year-to-date sales.

SELECT pub_id, total = sum (ytd_sales)
        FROM titles
            WHERE advance < $10000
            AND ytd_sales IS NOT NULL
D.    SELECT with GROUP BY, COMPUTE, and ORDER BY Clauses

This example returns only those rows with current year-to-date sales and then computes the average book cost and total advances in descending order by type. Four columns of data are returned including a truncated title. Notice that all computed columns appear within the select_list.

SELECT title = CONVERT(char(20), title), type, price, advance
        FROM titles
            WHERE ytd_sales IS NOT NULL
            ORDER BY type DESC
                COMPUTE AVG(price), SUM(advance) BY type
                COMPUTE SUM(price), SUM(advance)
go
title    type    price    advance    
---------------------- ----------------- ------- ----------
Fifty Years in Bucki    trad_cook    11.95    4,000.00    
Onions, Leeks, and G    trad_cook    20.95    7,000    
Sushi, Anyone?    trad_cook    14.99    8,000.00    

            avg
            =========
            15.96    
                sum
                =========
                19,000.00

title    type    price    advance    
---------------------- ----------------- ------- ----------
Computer Phobic AND    psychology    21.59    7,000.00    
Emotional Security:    psychology    7.99    4,000.00    
Is Anger the Enemy?    psychology    10.95    2,275.00    
Life Without Fear    psychology    7.00    6,000.00    
Prolonged Data Depri    psychology    19.99    2,000.00    

            avg
            =========
            13.50    
                sum
                =========
                21,275.00


title    type    price    advance    
---------------------- ----------------- ------- ----------
But Is It User Frien    popular_comp    22.95    7,000.00    
Secrets of Silicon V    popular_comp    20.00    8,000.00    

            avg
            =========
            21.48    
                sum
                =========
                15,000.00

title    type    price    advance    
---------------------- ----------------- ------- ----------
Silicon Valley Gastr    mod_cook    19.99    0.00    
The Gourmet Microwav    mod_cook    2.99    15,000.00

            avg
            =========
            11.49    
                sum
                =========
                15,000.00

title    type    price    advance    
---------------------- ----------------- ------- ----------
Cooking with Compute    business    11.95    5,000.00    
Straight Talk About    business    19.99    5,000.00    
The Busy Executive's    business    19.99    5,000.00    
You Can Combat Compu    business    2.99    10,125.00
    
            avg
            =========
            13.73    
                sum
                =========
                25,125.00

            sum
            =========
            236.26    
                sum
                =========
                88,400.00    
(22 row(s) affected)
E.    All Rows with Computed Sums

This example shows only three columns in the select_list and gives totals based on all prices and all advances at the end of the results.

SELECT type, price, advance
        FROM titles
            COMPUTE SUM(price), SUM(advance)
go
type         price                      advance
------------ -------------------------- -------------------------- 
business     19.99                      5,000.00
business     11.95                      5,000.00
business     2.99                       10,125.00
business     19.99                      5,000.00
mod_cook     19.99                      0.00 
mod_cook     2.99                       15,000.00
UNDECIDED    (null)                     (null)
popular_comp 22.95                      7,000.00
popular_comp 20.00                      8,000.00
popular_comp (null)                     (null)
psychology   21.59                      7,000.00
psychology   10.95                      2,275.00
psychology   7.00                       6,000.00
psychology   19.99                      2,000.00
psychology   7.99                       4,000.00
trad_cook    20.95                      7,000.00
trad_cook    11.95                      4,000.00
trad_cook    14.99                      8,000.00

             sum
             ==========================
             236.26                     
                                        sum
                                        ==========================
                                        95,400.00

(19 row(s) affected)
F.    Create a Temporary Table with SELECT INTO

This example causes a temporary table to be created in tempdb. To use this table, always refer to it with the exact name shown, including the pound sign (#).

SELECT *
    INTO #coffeetabletitles
        FROM titles
            WHERE price < $20
go
SELECT name FROM sysobjects WHERE name LIKE '#c%'
go
name  
------------------------------ 

(0 row(s) affected)
SELECT name FROM tempdb..sysobjects WHERE name LIKE '#c%'
go
name     
------------------------------ 
#coffeetabletitles__0000EC153E 

(1 row(s) affected)
G.    Create a Permanent Table with SELECT INTO

This example shows the steps needed to create a permanent table.

USE master
go
sp_dboption 'pubs', 'select into', TRUE
go
CHECKPOINTing database that was changed.
USE pubs
go
SELECT *
    INTO newtitles
        FROM titles
            WHERE price > $25
            OR price < $20
go
(12 row(s) affected)
SELECT name FROM sysobjects WHERE name LIKE 'new%'
go
name 
------------------------------ 
newtitles 

(1 row(s) affected)
H.    Optimizer Hints: TABLOCK and HOLDLOCK

The following partial transaction shows how to place an explicit shared table lock on t1 without the overhead of reading any records from it.

BEGIN TRAN
SELECT count(*) FROM t1 (TABLOCK HOLDLOCK)
I.    Optimizer Hints: Using the Name of an Index

This example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table.

SELECT au_lname, au_fname, phone
    FROM authors (INDEX = aunmind)
        WHERE au_1name = 'Smith'
J.    Optimizer Hints: Forcing a Table Scan

This example shows that using an index of 0 will force a table scan.

SELECT emp_id, fname, lname, hire_date
    FROM employee (index = 0)
        WHERE hire_date > '10/1/1994'

See Also

CREATE TABLE statement Search Conditions
CREATE TRIGGER statement sp_dboption
CREATE VIEW statement Subqueries
DELETE statement UNION operator
Expressions UPDATE statement
Functions Variables
INSERT statement Wildcard Characters