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 keywords controlling the final format of the result set:

• DISTINCT

  The DISTINCT keyword eliminates duplicate rows from a result set. For example, there are many rows in the Northwind Orders table with the same value for ShipCity. To get a list of the ShipCity values with duplicates removed:

  SELECT DISTINCT ShipCity, ShipRegion
  FROM Orders
  ORDER BY ShipCity
  

• TOP n

  The TOP keyword specifies that the first n rows of the result set are returned. If ORDER BY is specified, the rows are selected after the result set is ordered. n is the number of rows to return, unless the PERCENT keyword is specified. PERCENT specifies that n is the percentage of rows in the result set that are returned. For example, this SELECT statement returns the first 10 cities, in alphabetic sequence, from the Orders table:

  SELECT DISTINCT TOP 10 ShipCity, ShipRegion
  FROM Orders
  ORDER BY ShipCity
  

The items in the select list can include:

• A simple expression: a reference to a function, a local variable, a constant, or a column in a table or view.
  
  
• A scalar subquery, which is a SELECT statement that evaluates to a single value for each result set row.
  
  
• A complex expression built by using operators on one or more simple expressions.
  
  
• The * keyword, which 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 IDENTITYCOL keyword, which is resolved as a reference to the column in the table having the IDENTITY property. For example, the IDENTITY property has been defined for the OrderID column in the Northwind Orders table, so the expression Orders. IDENTITYCOL is equal to Orders.OrderID.
  
  
• The ROWGUILDCOL keyword, which is resolved as a reference to the column in a table having the ROWGUIDCOL property.
  
  
• Creating a new column (using SELECT INTO) that uses the IDENTITY property by using the specified syntax. For example, to create a new column named counter in the authors table that is an int column, you should start at a value of 100 and increment by values of 1 for each succeeding number, use counter = IDENTITY(int, 100, 1).
  
  
• Temporarily adding a column to the query results that designates whether the CUBE or ROLLUP operation added the row or not. Use the GROUPING keyword.

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

SELECT FirstName + ' ' + LastName AS "Employee Name", 
       IDENTITYCOL AS "Employee ID",
       HomePhone,
       Region,
       10 AS Constant
FROM Northwind.dbo.Employees
ORDER BY LastName, FirstName ASC

See Also

SELECT