SELECT (Transact-SQL)

SQL Server 2008 Books Online (October 2009)

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.

Topic link icon 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:

WITH common_table_expression	UNION
SELECT Clause	EXCEPT and INTERSECT
INTO Clause	ORDER BY
FROM	COMPUTE
WHERE	FOR Clause
GROUP BY	OPTION Clause
HAVING

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.

Reference

SELECT Examples (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance