| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Transact-SQL User's Guide |
![[-]](/dwicons/i_colpse.gif)
| Chapter 2 Queries: Selecting Data from a Table |
|
| Choosing columns: the select clause |
The items in the select clause make up the select list. When the select list consists of a column name, a group of columns, or the wildcard character (*), the data is retrieved in the order in which it is stored in the table (create table order).
The asterisk (*) selects all the column names in all the tables specified by the from clause. Use it to save typing time and errors when you want to see all the columns in a table. * retrieves the data in create table order.
The syntax for selecting all the columns in a table is:
select * from table_list
The following statement retrieves all columns in the publishers table and displays them in create table order. This statement retrieves all rows since it contains no where clause:
select * from publishers
The results look like this:
pub_id pub_name city state ----- -------------- --------- ----- 0736 New Age Books Boston WA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA (3 rows affected)
If you listed all the column names in the table in order after the select keyword, you would get exactly the same results:
select pub_id, pub_name, city, state from publishers
You can also use "*" more than once in a query:
select *, * from publishers
This query displays each column name and each piece of column data twice. Like a column name, you can qualify an asterisk with a table name. For example:
select publishers.* from publishers
However, because select * finds all the columns currently in a table, changes in the structure of a table such as adding, removing, or renaming columns automatically modify the results of select *. Listing columns individually gives you more precise control over the results.
To select only specific columns in a table, use:
select column_name[, column_name]...
from table_nameSeparate column names with commas, for example:
select au_lname, au_fname from authors
The order in which you list the column names in the select clause determines the order in which the columns display. The examples that follow show how to specify column order, displaying publisher names and identification numbers from all three rows in the publishers table. The first example prints pub_id first, followed by pub_name; the second reverses that order. The information is the same but the organization changes.
select pub_id, pub_name from publishers
pub_id pub_name ----- --------------- 0736 New Age Books 0877 Binnet & Hardley 1389 Algodata Infosystems (3 rows affected)
select pub_name, pub_id from publishers
pub_name pub_id --------------------- ------ New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389 (3 rows affected)
When query results display, the default heading for each column is the name given to it when it was created. You can rename a column heading for display purposes by using one of the following instead of only the column name in a select list.
column_heading = column_name
or:
column_name column_heading
or:
column_name as column_heading
This provides a substitute name for the column. For example, to change pub_name to "Publisher" in the previous query, type any of the following statements:
select Publisher = pub_name, pub_id from publishers
select pub_name Publisher, pub_id from publishers
select pub_name as Publisher, pub_id from publishers
The results of these statements look like this:
Publisher pub_id ---------------------- ------ New Age Books 0736 Binnet & Hardley 0877 Algodata Infosystems 1389 (3 rows affected)
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.
Quoted strings in column headingsYou 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.
Character strings in query resultsThe 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)Computed values in the select list
You can perform computations with data from numeric columns or on numeric constants in a select list.
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.
Operator | Meaning |
& | Bitwise and (two operands) |
| | Bitwise or (two operands) |
^ | Bitwise exclusive or (two operands) |
~ | Bitwise not (one operand) |
For more information on bitwise operators, see the Reference Manual.
Table 2-2 shows the available arithmetic operators.
Operator | Operation |
+ | Addition |
- | Subtraction |
/ | Division |
* | Multiplication |
% | Modulo |
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)Arithmetic operator precedence
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)
text and image values can be quite large. When a select list includes text and image values, the limit on the length of the data returned depends on the setting of the @@textsize global variable. The default setting for @@textsize depends on the software you use to access Adaptive Server; the default value is 32K for isql. To change the value, use the set command:
set textsize 25
With this setting of @@textsize, a select statement that includes a text column displays only the first 25 bytes of the data.
When you select image data, the returned value includes the characters "0x", which indicates that the data is hexadecimal. These two characters are counted as part of @@textsize.
To reset @@textsize to the Adaptive Server default value, use:
set textsize 0
The default display is the actual length of the data when its size is less than textsize. For more information about text and image datatypes, see Chapter 6, "Using and Creating Datatypes."
Using readtextThe readtext command provides a way to retrieve text and image values if you want to retrieve only a selected portion of a column's data. readtext requires the name of the table and column, the text pointer, a starting offset within the column, and the number of characters or bytes to retrieve. This example finds six characters in the copy column in the blurbs table:
declare @val binary(16) select @val = textptr(copy) from blurbs where au_id = "648-92-1872" readtext blurbs.copy @val 2 6 using chars
In the example, after the @val local variable has been declared, readtext displays characters 3 - 8 of the copy column, since the offset was 2.
Instead of storing potentially large text and image data in the table, Adaptive Server stores it in a special structure. A text pointer (textptr) which points to the page where the data is actually stored is assigned. When you retrieve data using readtext, you actually retrieve textptr, which is a 16-byte varbinary string. To avoid this, declare a local variable to hold textptr, and then use the variable with readtext, as in the example above.
See "Text functions used for text and image data" for an advanced discussion of the readtext command.
The select list can include * (all columns in create table order), a list of column names in any order, character strings, column headings, and expressions including arithmetic operators. You can also include aggregate functions, which are discussed in Chapter 3, "Using Aggregates, Grouping, and Sorting." Here are some select lists to try with the tables in the pubs2 sample database:
select titles.* from titles
select Name = au_fname, Surname = au_lname from authors
select Sales = total_sales * price, ToAuthor = advance, ToPublisher = (total_sales * price) - advance from titles
select "Social security #", au_id from authors
select this_year = advance, next_year = advance + advance/10, third_year = advance/2, "for book title #", title_id from titles
select "Total income is", Revenue = price * total_sales, "for", Book# = title_id from titles
|
|