Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
The UNION operator can be used between queries to combine their results into a single result set.
SELECT statement ::=
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[,...n] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } (expression) } [,...n]
[ BY expression [,...n] ]
]
[ FOR BROWSE ]
[ OPTION (<query_hint> [,...n]) ]
<query expression> ::=
{ <query specification> | (<query expression>) }
[UNION [ALL] <query specification | (<query expression>) [...n] ]
<query specification> ::=
SELECT [ ALL | DISTINCT ]
[ {TOP integer | TOP integer PERCENT} [ WITH TIES] ]
<select_list>
[ INTO new_table ]
[ FROM {<table_source>} [,...n] ]
[ WHERE <search_condition> ]
[ GROUP BY [ALL] group_by_expression [,...n]
[ WITH { CUBE | ROLLUP } ]
]
[ HAVING <search_condition> ]
Because of the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause:
SELECT Clause
INTO Clause
FROM Clause
WHERE Clause
GROUP BY Clause
HAVING Clause
UNION Operator
ORDER BY Clause
COMPUTE Clause
FOR BROWSE Clause
OPTION Clause
Specifies the columns to be returned by the query.
SELECT [ ALL | DISTINCT ]
[ TOP n [PERCENT] [ WITH TIES] ]
<select_list>
<select_list> ::=
{ *
| { table_name | view_name | table_alias }.*
| { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
[ [AS] column_alias ]
| column_alias = expression
} [,...n]
If the query includes an ORDER BY clause, the first n rows (or n percent of rows) ordered by the ORDER BY clause are output. If the query has no ORDER BY clause, the order of the rows is arbitrary.
If the more than one table in the FROM clause has a column with the IDENTITY property, IDENTITYCOL must be qualified with the specific table name, such as T1.IDENTITYCOL.
If the more than one table in the FROM clause with the ROWGUIDCOL property, ROWGUIDCOL must be qualified with the specific table name, such as T1.ROWGUIDCOL.
Aliases are used also to specify names for the results of expressions, for example:
USE Northwind
SELECT AVG(UnitPrice) AS 'Average Price'
FROM [Order Details]
column_alias can be used in an ORDER BY clause; however, it cannot be used in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE clause.
Creates a new table and inserts the resulting rows from the query into it.
The user executing a SELECT statement with the INTO clause must have CREATE TABLE permission in the destination database. SELECT...INTO cannot be used with the COMPUTE clause or inside an explicit transaction. For more information, see Transactions and Explicit Transactions.
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.
[ INTO new_table ]
When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.
To select into a permanent table, execute sp_dboption to turn on the select into/bulkcopy option before executing the SELECT statement.
Specifies the table(s) from which to retrieve rows. The FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names). For more information, see FROM.
[ FROM {<table_source>} [,...n] ]
<table_source> ::=
table_name [ [AS] table_alias ] [ WITH ( <table_hint> [,...n]) ]
| view_name [ [AS] table_alias ]
| rowset_function [ [AS] table_alias ]
| derived_table [AS] table_alias [ (column_alias [,...n] ) ]
| <joined_table>
<joined_table> ::=
<table_source> <join_type> <table_source> ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| <joined_table>
<join_type> ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ <join_hint> ]
JOIN
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
For more information about search conditions and predicates, see Search Condition.
SELECT * FROM T1, T2
SELECT * FROM T1 CROSS JOIN T2
Specifies a search condition to restrict the rows returned.
[WHERE <search_condition> | <old_outer_join>]
<old_outer_join> ::=
column_name { *= | =* } column_name
This example specifies a left outer join in which the rows from Tab1 that do not meet the specified condition are included in the result set:
SELECT Tab1.name, Tab2.id
FROM Tab1, Tab2
WHERE Tab1.id *=Tab2.id
Note Using this syntax for outer joins is discouraged because of the potential for ambiguous interpretation and because it is nonstandard. Instead, specify joins in the FROM clause.
It is possible to specify outer joins by using join operators in the FROM clause or by using the non-standard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement.
Specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT clause <select list>, calculates a summary value for each group. When a GROUP BY clause is used, each item in the select list must produce a single value for each group. Null values in one item are placed in one group.
Note If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order. It is recommended that you always use the ORDER BY clause to specify a particular ordering of the data.
[ GROUP BY [ALL] group_by_expression [,...n]
[ WITH { CUBE | ROLLUP } ]
]
GROUP BY ALL is not supported in queries that access remote tables.
Note Columns of type text, ntext, image, and bit cannot be used in group_by_expression.
For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group_by_expression items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate work table that is needed to hold intermediate query results. A maximum of 10 grouping expressions is permitted when CUBE or ROLLUP is specified.
The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Each operand (column) in the GROUP BY clause is bound under the grouping NULL and grouping is applied to all other operands (columns). Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.
Important Distinct aggregates, for example, AVG(DISTINCT column_name), COUNT(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, SQL Server returns an error message and cancels the query.
Specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
[ HAVING <search_condition> ]
The text, image, and ntext data types cannot be used in a HAVING clause.
Note Using the HAVING clause in the SELECT statement does not affect the way the CUBE operator groups the result set and returns summary aggregate rows.
Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. This is different from using joins that combine columns from two tables.
Two basic rules for combining the result sets of two queries with UNION are:
{ <query specification> | (<query expression>) }
UNION [ALL]
<query specification | (<query expression>)
[UNION [ALL] <query specification | (<query expression>)
[...n] ]
The table shows the rules for comparing the data types and options of corresponding (ith) columns.
| Data type of ith column | Data type of ith column of results table |
|---|---|
| Not data type-compatible (data conversion not handled implicitly by Microsoft SQL Server). | Error returned by SQL Server. |
| Both fixed-length char with lengths L1 and L2. | Fixed-length char with length equal to the greater of L1 and L2. |
| Both fixed-length binary with lengths L1 and L2. | Fixed-length binary with length equal to the greater of L1 and L2. |
| Either or both variable-length char. | Variable-length char with length equal to the maximum of the lengths specified for the ith columns. |
| Either or both variable-length binary. | Variable-length binary with length equal to the maximum of the lengths specified for the ith columns. |
| Both numeric data types (for example, smallint, int, float, money). | Data type equal to the maximum precision of the two columns. For example, if the ith column of table A is of type int and the ith column of table B is of type float, then the data type of the ith column of the results table is float because float is more precise than int. |
| Both columns’ descriptions specify NOT NULL. | Specifies NOT NULL. |
Specifies the sort for the result set. Subqueries and view definitions cannot include an ORDER BY clause.
[ORDER BY {order_by_expression [ ASC | DESC ] } [,...n] ]
Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.
The ORDER BY clause can include items not appearing in the select list; however, if SELECT DISTINCT is specified or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.
Note ntext, text, or image columns cannot be used in an ORDER BY clause.
Null values are treated as the lowest possible values.
There is no limit to the number of items in the ORDER BY clause; however, there is a limit of 8,060 bytes, for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.
Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | STDEV | STDEVP
|VAR | VARP | SUM }
(expression) } [,...n]
[ BY expression [,...n] ]
]
| Row aggregate function | Result |
|---|---|
| AVG | Average of the values in the numeric expression |
| COUNT | Number of selected rows |
| MAX | Highest value in the expression |
| MIN | Lowest value in the expression |
| STDEV | Statistical standard deviation for all values in the expression |
| STDEVP | Statistical standard deviation for the population for all values in the expression |
| SUM | Total of the values in the numeric expression |
| VAR | Statistical variance for all values in the expression |
| VARP | Statistical variance for the population for all values in the expression |
There is no equivalent to COUNT(*). To find the summary information produced by GROUP BY and COUNT(*), use a COMPUTE clause without BY.
These functions ignore null values.
The DISTINCT keyword is not allowed with row aggregate functions when they are specified with the COMPUTE clause.
When you add or average integer data, SQL Server treats the result as an int value, even if the data type of the column is smallint or tinyint. For more information about the return types of added or average data, see SUM and AVG.
Note To reduce the possibility of overflow errors in ODBC and DB-Library programs, make all variable declarations for the results of averages or sums the data type int.
Note ntext, text, or image data types cannot be specified in a COMPUTE or COMPUTE BY clause.
If you use COMPUTE BY, you must also use an ORDER BY clause. The expressions must be identical to or a subset of those listed after ORDER BY, and must be in the same sequence. 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
Note In a SELECT statement with a COMPUTE clause, the order of columns in the select list overrides the order of the aggregate functions in the COMPUTE clause. ODBC and DB-Library programmers must be aware of this order requirement to put the aggregate function results in the correct place.
You cannot use COMPUTE in a SELECT INTO statement because statements including COMPUTE generate tables and their summary results are not stored in the database. Therefore, any calculations produced by COMPUTE do not appear in the new table created with the SELECT INTO statement.
You cannot use the COMPUTE clause when the SELECT statement is part of a DECLARE CURSOR statement.
Specifies that updates be allowed while viewing data in client applications using DB-Library.
A table can be browsed in an application under these conditions:
For more information, see DB Library for C.
Note It is not possible to use the <lock_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.
[ FOR BROWSE ]
Specifies that the indicated query hint should be used throughout the entire query. Each query hint can be specified only once, although multiple query hints are permitted. The OPTION clause must be specified with the outermost query of the statement. The query hint affects all operators in the statement. If a UNION is involved in the main query, only the last query involving a UNION operator can have the OPTION clause. If one or more query hints causes the query optimizer to not generate a valid plan, SQL Server recompiles the query without the specified query hints, and issues a SQL Server Profiler event.
Caution Because the query optimizer usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, and <table_hint> be used only as a last resort by experienced database administrators.
[ OPTION (<query_hint> [,...n) ]
<query_hint> ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number
| ROBUST PLAN
| KEEP PLAN
}
The order of the clauses in the SELECT statement is significant. Any of the optional clauses can be omitted, but when used, they must appear in the appropriate order.
The length returned for text or ntext columns included in the select list defaults to the smallest of the actual size of the text, the default TEXTSIZE session setting, or the hard-coded 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 4,000 bytes.
SQL Server raises exception 511 and rolls back the current executing statement if either of these occur:
In SQL Server, an error occurs if no column name is given to a column created by a SELECT INTO or CREATE VIEW statement. For more information, see trace flag 246 in Trace Flags.
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. All rules and restrictions for the identity columns apply to the new table.
Earlier versions of SQL Server supported the definition of outer joins that used the *= and =* operators in the WHERE clause. SQL Server version 7.0 supports the SQL-92 standard, which provides join operators in the FROM clause. It is recommended that queries be rewritten to use the SQL-92 syntax.
This list shows the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause.
SELECT permissions default to members of the sysadmin fixed server role, members of the db_owner fixed database role, and the table owner, who can transfer permissions to other users. If the INTO clause is used to create a permanent table, the user must have CREATE TABLE permission in the destination database.