Retrieves rows from the database and enables 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:
[ WITH <common_table_expression>]
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, EXCEPT and INTERSECT operators can be used between queries to combine or compare their results into one result set.
.gif)
Transact-SQL Syntax Conventions
Syntax
<SELECT statement> ::=
[WITH <common_table_expression> [,...n]]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]
]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]
Remarks
Because of the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause:
The order of the clauses in the SELECT statement is significant. Any one of the optional clauses can be omitted, but when the optional clauses are used, they must appear in the appropriate order.
SELECT statements are permitted in user-defined functions only if the select lists of these statements contain expressions that assign values to variables that are local to the functions.
A four-part name constructed with the OPENDATASOURCE function as the server-name part can be used as a table source wherever a table name can appear in aSELECT statement.
Some syntax restrictions apply to SELECT statements that involve remote tables. For more information, see Guidelines for Using Distributed Queries.
Processing Order of the SELECT statement
The following steps show the processing order for a SELECT statement.
-
FROM
-
ON
-
JOIN
-
WHERE
-
GROUP BY
-
WITH CUBE or WITH ROLLUP
-
HAVING
-
SELECT
-
DISTINCT
-
ORDER BY
-
TOP
Permissions
Requires membership in the sysadmin fixed server role, the db_owner and db_datareader fixed database roles, and ownership of the table. Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.
See Also