• Article

Using the Select List

The select list defines the columns in the result set of a SELECT statement. The select list is a series of expressions separated by commas. Each expression defines a column in the result set. The columns in the result set are in the same order as the sequence of expressions in the select list.

These attributes of the result set columns are defined by the expressions in the select list:

  • The data type, size, precision, and scale of the result set column are the same as those of the expression defining the column.

  • The name of the result set column is the name associated with the expression defining the column. The optional AS keyword can be used to change the name, or to assign a name if the expression has no name.

  • The data values for the result set column are derived from the evaluation of the expression for each row of the result set.

The select list can also contain the following keywords that control the final format of the result set:

The items in the select list can include the following:

  • A simple expression, such as: a reference to a function, a variable, a constant, or a column in a table or view.

  • A scalar subquery. This is a SELECT statement that evaluates to a single value for each result set row.

  • A complex expression that is built by using operators on one or more simple expressions. For more information about expressions, see Expressions (Transact-SQL).

    • Expressions can include the $ROWGUID keyword. This is resolved as a reference to the column in a table having the ROWGUIDCOL property.

  • The * symbol. This specifies that all columns in a table are returned.

  • Variable assignment in the form: *@*local_variable = expression. The SET *@*local_variable statement can also be used for variable assignment.

The following example shows many of the items that can be in a select list:

SELECT e.$IDENTITY AS "Employee ID",
    c.FirstName + ' ' + c.LastName AS "Employee Name", 
    c.Phone
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
ORDER BY LastName, FirstName ASC

See Also

Reference

SELECT (Transact-SQL)