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.
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
column_heading = column_name
or
column_name column_heading
The column_heading must be in quotation marks if spaces are used. For example:
SELECT 'Author Last Name' = au_lname FROM authors
@variable = expression
Note When the select_list includes a variable assignment(s), it cannot be combined with data-retrieval operations.
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.
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.
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:
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.
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.
For the details about aggregate functions, see the Functions topic.
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.
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.
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.
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
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.
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.
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:
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:
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.
This example returns all rows (no WHERE clause) and all columns (*) from the publishers table in the pubs database.
SELECT * FROM publishers
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
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
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)
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)
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)
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)
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)
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'
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'