SELECT Clause (Transact-SQL)

Article
12/03/2008

Specifies the columns to be returned by the query.

Topic link icon Transact-SQL Syntax Conventions

Syntax

SELECT [ ALL | DISTINCT ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ] 
<select_list> 
<select_list> ::= 
    { 
      | { table_name | view_name | table_alias }.* 
      | {
          [ { table_name | view_name | table_alias }. ]
               { column_name | $IDENTITY | $ROWGUID } 
          | udt_column_name [ { . | :: } { { property_name | field_name } 
            | method_name ( argument [ ,...n] ) } ]
          | expression
          [ [ AS ] column_alias ] 
         }
      | column_alias = expression 
    } [ ,...n ]

Arguments

ALL
Specifies that duplicate rows can appear in the result set. ALL is the default.

DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

TOP expression [ PERCENT ] [ WITH TIES ]
Indicates that only a specified first set or percent of rows will be returned from the query result set. expression can be either a number or a percent of the rows.

The TOP clause can be used in SELECT, INSERT, UPDATE, and DELETE statements. Parentheses delimiting expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, using TOP expression without parentheses in SELECT statements is supported, but we do not recommend it. For more information, see TOP (Transact-SQL).

< select_list >
The columns to be selected for the result set. The select list is a series of expressions separated by commas. The maximum number of expressions that can be specified in the select list is 4096.

*
Specifies that all columns from all tables and views in the FROM clause should be returned. The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view.

table_ name| view_ name| table_ alias.*
Limits the scope of the * to the specified table or view.

column_ name
Is the name of a column to return. Qualify column_name to prevent an ambiguous reference, such as occurs when two tables in the FROM clause have columns with duplicate names. For example, the SalesOrderHeader and SalesOrderDetail tables in the AdventureWorks database both have a column named ModifiedDate. If the two tables are joined in a query, the modified date of the SalesOrderDetail entries can be specified in the select list as SalesOrderDetail.ModifiedDate.

expression
Is a constant, function, any combination of column names, constants, and functions connected by an operator or operators, or a subquery.

$IDENTITY
Returns the identity column. For more information, see IDENTITY (Property) (Transact-SQL), ALTER TABLE (Transact-SQL), and CREATE TABLE (Transact-SQL).

If more than one table in the FROM clause has a column with the IDENTITY property, $IDENTITY must be qualified with the specific table name, such as T1.$IDENTITY.

$ROWGUID
Returns the row GUID column.

If there is more than one table in the FROM clause with the ROWGUIDCOL property, $ROWGUID must be qualified with the specific table name, such as T1.$ROWGUID.

udt_column_name
Is the name of a common language runtime (CLR) user-defined type column to return.

Note

SQL Server Management Studio returns user-defined type values in binary representation. To return user-defined type values in string or XML format, use CAST or CONVERT.

{ . | :: }
Specifies a method, property, or field of a CLR user-defined type. Use . for an instance (nonstatic) method, property, or field. Use :: for a static method, property, or field. To invoke a method, property, or field of a CLR user-defined type, you must have EXECUTE permission on the type.

property_name
Is a public property of udt_column_name.

field_name
Is a public data member of udt_column_name.

method_name
Is a public method of udt_column_name that takes one or more arguments. method_name cannot be a mutator method.

The following example selects the values for the Location column, defined as type point, from the Cities table, by invoking a method of the type called Distance:
```
CREATE TABLE Cities (
     Name varchar(20),
     State varchar(20),
     Location point );
GO
DECLARE @p point (32, 23), @distance float
GO
SELECT Location.Distance (@p)
FROM Cities;
```

column_ alias
Is an alternative name to replace the column name in the query result set. For example, an alias such as Quantity, or Quantity to Date, or Qty can be specified for a column named quantity.

Aliases are used also to specify names for the results of expressions, for example:

USE AdventureWorks;

GO

SELECT AVG(UnitPrice) AS 'Average Price'

FROM Sales.SalesOrderDetail;

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.

Remarks

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.

The OPTION clause is not allowed on views.

A table variable, in its scope, can be accessed like a regular table and so can be used as a table source in a SELECT statement.

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 SELECT statements.

Some syntax restrictions apply to SELECT statements that involve remote tables. For more information, see Guidelines for Using Distributed Queries.

The length of data returned for text or ntext columns that are included in the select list is set to the smallest value of the following: the actual size of the text column, 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.

The SQL Server 2005 Database Engine raises exception 511 and rolls back the current running statement if either of the following behavior occurs:

The SELECT statement produces a result row or an intermediate work table row exceeding 8,060 bytes.
The DELETE, INSERT, or UPDATE statement tries an action on a row exceeding 8,060 bytes.

An error occurs if no column name is specified to a column created by a SELECT INTO or CREATE VIEW statement.

Selecting Identity Columns

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

The SELECT statement contains a join, GROUP BY clause, or aggregate function.
Multiple SELECT statements are joined by using UNION.
The identity column is listed more than one time in the select list.
The identity column is part of an expression.

If any one 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.

Old-Style Outer Joins

Earlier versions of SQL Server support the definition of outer joins that use the *= and =* operators in the WHERE clause. SQL Server version 7.0 supports the SQL-92 standard. This provides join operators in the FROM clause. When the compatibility level of the database is set to 90, the outer join operators (*= and =*) are not supported.

Processing Order of WHERE, GROUP BY, and HAVING Clauses

The following steps show the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause:

The FROM clause returns an initial result set.
The WHERE clause excludes rows not meeting its search condition.
The GROUP BY clause collects the selected rows into one group for each unique value in the GROUP BY clause.
Aggregate functions specified in the select list calculate summary values for each group.
The HAVING clause additionally excludes rows not meeting its search condition.

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.

If the INTO clause is used to create a permanent table, the user must have CREATE TABLE permission in the destination database.

SELECT Clause (Transact-SQL)

Syntax

Arguments

Remarks

Selecting Identity Columns

Old-Style Outer Joins

Processing Order of WHERE, GROUP BY, and HAVING Clauses

Permissions

See Also

Reference

Other Resources

Help and Information

Additional resources