Home

	Expand Search

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

Choosing columns: the select clause

Expressions

Expressions

The select statement can also include one or more expressions, which allow you to manipulate the data retrieved.

select expression [, expression]... 
     from table_list 

An expression is any combination of constants, column names, functions, subqueries, or case expressions, connected by arithmetic or bitwise operators and parentheses.

If any table or column name in the list does not conform to the rules for valid identifiers, set the quoted_identifier option on and enclose the identifier in double quotes.

You can include any characters--even blanks--in a column heading if you enclose the entire heading in quotation marks. You do not need to set the quoted_identifier option on. If the column heading is not enclosed in quotation marks, it must conform to the rules for identifiers. Both of the following queries produce the same result:

select "Publisher's Name" = pub_name 
from publishers

select pub_name "Publisher's Name" 
from publishers

Publisher's Name 
---------------- 
New Age Books 
Binnet & Hardley 
Algodata Infosystems
 
(3 rows affected)

In addition, you can use Transact-SQL reserved words in quoted column headings. For example, the following query, using the reserved word sum as a column heading, is valid:

select "sum" = sum(total_sales) from titles

Quoted column headings cannot be more than 30 bytes long.

Before using quotes around a column name in a create table, alter table, select into, or create view statement, you must set quoted_identifier on.

The select statements you have seen so far produce results showing data in the database. You can also write queries so that the results contain strings of characters.

Enclose the string you want to include in single or double quotation marks and separate it from other elements in the select list with a comma. Use double quotation marks if there is an apostrophe in the string--otherwise, the apostrophe is interpreted as a single quotation mark.

Here is a query with a character string:

select "The publisher's name is", Publisher = pub_name 
from publishers 

                              Publisher 
------------------------      --------------------
The publisher's name is       New Age Books 
The publisher's name is       Binnet & Hardley 
The publisher's name is       Algodata Infosystems 
 
(3 rows affected) 

You can perform computations with data from numeric columns or on numeric constants in a select list.

Bitwise operators

The bitwise operators are a Transact-SQL extension that you can use with integers. These operators convert each integer operand into its binary representation, then evaluates the operands column by column. A value of 1 corresponds to true; a value of 0 corresponds to false.

Table 2-1 shows the bitwise operators.

For more information on bitwise operators, see the Reference Manual.

Arithmetic operators

Table 2-2 shows the available arithmetic operators.

With the exception of the modulo operator, you can use any arithmetic operator on any numeric column (int, smallint, tinyint, numeric, decimal, float, or money) A modulo, which can be used on all numeric columns except money, finds the integer remainder after a division involving two whole numbers. For example, 21 % 11 = 10 because 21 divided by 11 equals 1, with a remainder of 10.

You can perform certain arithmetic operations on datetime columns using the date functions. See Chapter 10, "Using the Built-In Functions in Queries," for information. You can use all of these operators in the select list with column names and numeric constants in any combination. For example, to see what a projected sales increase of 100 percent for all the books in the titles table looks like, enter:

select title_id, total_sales, total_sales * 2 
from titles 

Here are the results:

title_id     total_sales 
--------     -----------       ---------
BU1032              4095            8190 
BU1111              3876            7752 
BU2075             18722           37444 
BU7832              4095            8190 
MC2222              2032            4064 
MC3021             22246           44492 
MC3026              NULL            NULL 
PC1035              8780           17560 
PC8888              4095            8190 
PC9999              NULL            NULL 
PS1372               375             750 
PS2091              2045            4090 
PS2106               111             222 
PS3333              4072            8144 
PS7777              3336            6672 
TC3218               375             750 
TC4203             15096           30192 
TC7777              4095            8190 
 
(18 rows affected) 

Notice the null values in the total_sales column and the computed column. Null values have no explicitly assigned values. When you perform any arithmetic operation on a null value, the result is NULL. You can give the computed column a heading, "proj_sales" for example, by entering:

select title_id, total_sales, 
     proj_sales = total_sales * 2 
from titles 

title_id     total_sales     proj_sales  

---------    -----------     ----------- 

 BU1032             4095            8190

 ....

Try adding character strings such as "Current sales =" and "Projected sales are" to the select statement. The column from which the computed column is generated does not have to appear in the select list. The total_sales column, for example, is shown in these sample queries only for comparison of its values with the values from the total_sales * 2 column. To see only the computed values, enter:

select title_id, total_sales * 2 
from titles 

Arithmetic operators also work directly with the data values in specified columns, when no constants are involved. For example:

select title_id, total_sales * price 
from titles 

title_id 
--------     ---------- 
BU1032        81,859.05 
BU1111        46,318.20 
BU2075        55,978.78 
BU7832        81,859.05 
MC2222        40,619.68 
MC3021        66,515.54 
MC3026             NULL 
PC1035       201,501.00 
PC8888        81,900.00 
PC9999             NULL 
PS1372         8,096.25 
PS2091        22,392.75 
PS2106           777.00 
PS3333        81,399.28 
PS7777        26,654.64 
TC3218         7,856.25 
TC4203       180,397.20 
TC7777        61,384.05 
 
(18 rows affected) 

Computed columns can also come from more than one table. The joining and subqueries chapters in this manual include information on multitable queries.

As an example of a join, this query multiplies the number of copies of a psychology book sold by an outlet (the qty column from the salesdetail table) by the price of the book (the price column from the titles table).

select salesdetail.title_id, stor_id, qty * price 
from titles, salesdetail 
where titles.title_id = salesdetail.title_id 
and titles.title_id = "PS2106"

title_id         stor_id
---------------- -----------  ------
PS2106           8042         210.00  
PS2106           8042         350.00  
PS2106           8042         217.00  
 
(3 rows affected) 

When there is more than one arithmetic operator in an expression, multiplication, division, and modulo are calculated first, followed by subtraction and addition. If all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right. Expressions in parentheses take precedence over all other operations.

For example, the following select statement multiplies the total sales of a book by its price to calculate a total dollar amount, then subtracts from that one half of the author's advance.

select title_id, total_sales * price - advance / 2 
from titles 

The product of total_sales and price is calculated first, because the operator is multiplication. Next, the advance is divided by 2, and the result is subtracted from total_sales * price.

To avoid misunderstandings, use parentheses. The following query has the same meaning and gives the same results as the previous one, but it is easier to understand:

select title_id,(total_sales * price) - (advance /2) 
from titles 

title_id 
--------     ----------
 BU1032       79,359.05 
 BU1111       43,818.20 
 BU2075       50,916.28 
 BU7832       79,359.05 
 MC2222       40,619.68 
 MC3021       59,015.54 
 MC3026            NULL 
 PC1035      198,001.00 
 PC8888       77,900.00 
 PC9999            NULL 
 PS1372        4,596.25 
 PS2091        1,255.25 
 PS2106       -2,223.00 
 PS3333       80,399.28 
 PS7777       24,654.64 
 TC3218        4,356.25 
 TC4203      178,397.20 
 TC7777       57,384.05 
 
(18 rows affected)

Use parentheses to change the order of execution; calculations inside parentheses are handled first. If parentheses are nested, the most deeply nested calculation has precedence. For example, the result and meaning of the preceding example is changed if you use parentheses to force evaluation of the subtraction before the division:

select title_id, (total_sales * price - advance) /2 
from titles 

title_id                           
--------   -----------------------  
BU1032                  38,429.53  
BU1111                  20,659.10  
BU2075                  22,926.89  
BU7832                  38,429.53  
MC2222                  20,309.84  
MC3021                  25,757.77  
MC3026                       NULL 
PC1035                  97,250.50  
PC8888                  36,950.00  
PC9999                       NULL  
PS1372                     548.13  
PS2091                  10,058.88  
PS2106                  -2,611.50  
PS3333                  39,699.64  
PS7777                  11,327.32  
TC3218                     428.13  
TC4203                  88,198.60  
TC7777                  26,692.03  
 
(18 rows affected)