What is Selecting all columns

The SELECT clause is often referred to as the SELECT list, because it lists the values to be returned in the query’s results.

The simplest form of a SELECT clause is the use of the asterisk character (*) to return all columns. When used in T-SQL queries, it is called a star. While SELECT * is suitable for a quick test, you should avoid using it in production work for the following reasons:

• Changes to the table that add or rearrange columns will be reflected in the query results, which may result in unexpected output for applications or reports that use the query.
• Returning data that is not needed can slow down your queries and cause performance issues if the source table contains a large number of rows.

For example, the following example retrieves all columns from the (hypothetical) Production.Product table.

SQLCopy

SELECT * FROM Production.Product;

The result from this query is a rowset that contains all columns for all rows of the table, which might look something like this:

ProductID

Name

ProductNum

Color

StandardCost

ListPrice

Size

Weight

ProductCatID

680

HL Road Frame – Black, 58

FR-R92B-58

Black

1059.31

1431.5

58

1016.04

18

706

HL Road Frame – Red, 58

FR-R92R-58

Red

1059.31

1431.5

58

1016.04

18

707

Sport-100 Helmet, Red

HL-U509-R

Red

13.0863

34.99

35

708

Sport-100 Helmet, Black

HL-U509

Black

13.0863

34.99

35

…

…

…

…

…

…

…

…

…

Selecting specific columns

An explicit column list allows you to have control over exactly which columns are returned and in which order. Each column in the result will have the name of the column as the header.

For example, consider the following query; which again uses the hypothetical Production.Product table.

SQLCopy

SELECT ProductID, Name, ListPrice, StandardCost
‎FROM Production.Product;

This time, the results include only the specified columns:

ProductID

Name

ListPrice

StandardCost

680

HL Road Frame – Black, 58

1431.5

1059.31

706

HL Road Frame – Red, 58

1431.5

1059.31

707

Sport-100 Helmet, Red

34.99

13.0863

708

Sport-100 Helmet, Black

34.99

13.0863

…

…

…

…

Selecting expressions

In addition to retrieving columns stored in the specified table, a SELECT clause can perform calculations and manipulations, which use operators to combine columns and values or multiple columns. The result of the calculation or manipulation must be a single-valued (scalar) result that will appear in the result as a separate column.

For example, the following query includes two expressions:

SQLCopy

SELECT ProductID,
      Name + '(' + ProductNumber + ')',
  ListPrice - StandardCost
FROM Production.Product;

The results from this query might look something like this:

ProductID

680

HL Road Frame – Black, 58(FR-R92B-58)

372.19

706

HL Road Frame – Red, 58(FR-R92R-58)

372.19

707

Sport-100 Helmet, Red(HL-U509-R)

21.9037

708

Sport-100 Helmet, Black(HL-U509)

21.9037

…

…

…

There are a couple of interesting things to note about these results:

• The columns returned by the two expressions have no column names. Depending on the tool you are using to submit your query, a missing column name might be indicated by a blank column header, a literal “no column name” indicator, or a default name like column1. We’ll see how to specify an alias for the column name in the query later in this section.
• The first expression uses the + operator to concatenate string (character-based) values, while the second expression uses the – operator to subtract one numeric value from another. When used with numeric values, the + operator performs addition. Clearly then, it is important to understand the data types of the columns you include in expressions. We’ll discuss data types in the next section.

Specifying column aliases

You can specify an alias for each column returned by the SELECT query, either as an alternative to the source column name or to assign a name to the output of an expression.

For example, here’s the same query as before, but with aliases specified for each of the columns:

SQLCopy

SELECT ProductID AS ID,
      Name + '(' + ProductNumber + ')' AS ProductName,
  ListPrice - StandardCost AS Markup
FROM Production.Product;

The results from this query include the specified column names:

ID

ProductName

Markup

680

HL Road Frame – Black, 58(FR-R92B-58)

372.19

706

HL Road Frame – Red, 58(FR-R92R-58)

372.19

707

Sport-100 Helmet, Red(HL-U509-R)

21.9037

708

Sport-100 Helmet, Black(HL-U509)

21.9037

…

…

…

 Note

The AS keyword is optional when specifying an alias, but it’s good practice to include it for clarification.