save transaction  set

Chapter 1: Commands

select

Description

Retrieves rows from database objects.

Syntax

select ::=
	select [ all | distinct ] select_list 
	[into_clause ]
	[from_clause ]
	[where_clause ]
	[group_by_clause]
	[having_clause ]
	[order_by_clause ]
	[compute_clause ]
	[read_only_clause ]
	[isolation_clause ]
	[browse_clause ]
	[plan_clause ]

select_list ::=

NoteFor details on select_list, see the parameters description.

into_clause ::=
	into [[database.]owner.]table_name
	[ lock {datarows | datapages | allpages } ]
	[ with into_option [, into_option] ...]

	into_option ::= 
		| max_rows_per_page = num_rows
		| exp_row_size = num_bytes
		| reservepagegap = num_pages
		| identity_gap = gap 
		[existing table table_name]
		[[external type] at “path_name”
		[column delimiter delimiter]]

from_clause ::=
	from table_reference [,table_reference]...

	table_reference ::= 
		table_view_name | ANSI_join

		table_view_name ::=
			[[database.]owner.] {{table_name | view_name} 
			[as] [correlation_name]
			[index {index_name | table_name }] 
			[parallel [degree_of_parallelism]]
			[prefetch size ][lru | mru]}
		[holdlock | noholdlock] 
		[readpast]
		[shared]

		ANSI_join ::=
			table_reference join_type join table_reference 
					join_conditions
				join_type ::= inner | left [outer] | right [outer]
				join_conditions ::= on search_conditions

where_clause ::= 
	where search_conditions

group_by_clause ::=
	group by [all] aggregate_free_expression 
		[, aggregate_free_expression]...

having_clause ::=
	having search_conditions

order_by_clause ::=
	order by sort_clause [, sort_clause]...

	sort_clause ::=
		{ [[[database.]owner.]{table_name.|view_name.}]column_name 
		| select_list_number 
		| expression }
		[asc | desc]

compute_clause ::=
	compute row_aggregate(column_name)
		[, row_aggregate(column_name)]...
	[by column_name [, column_name]...]

read_only_clause ::=
	for {read only | update [of column_name_list]}

isolation_clause ::=
	at isolation
		{ read uncommitted | 0 }
		| { read committed | 1 }
		| { repeatable read | 2 } 
		| { serializable | 3 }

browse_clause ::=
	for browse

plan_clause ::=
	plan "abstract plan"

Parameters

all

includes all rows in the results. all is the default.

distinct

includes only unique rows in the results. distinct must be the first word in the select list. distinct is ignored in browse mode.

Null values are considered equal for the purposes of the keyword distinct: only one NULL is selected, no matter how many are encountered.

select_list

consists of one or more of the following items:

The select_list can also assign values to variables, in the form:

    @variable = expression
        [, @variable = expression ...]

You cannot combine variable assignment with any other select_list option.

into

creates a new table based on the columns specified in the select list and the rows chosen in the where clause. See “Using select into” in this section.

lock datarows | datapages | allpages

specifies the locking scheme to be used for a table created with a select into command. The default is the server-wide

ting for the configuration parameter lock scheme.

max_rows_per_page

limits the number of rows on data pages for a table created with select into. Unlike fillfactor, the max_rows_per_page value is maintained when data is inserted or deleted. max_rows_per_page is not supported on data-only-locked tables.

existing table table_name

indicates that you are selecting data into a proxy table. You cannot use this select into any other table type except proxy. The column list in the select list must match the type, length, and number in the proxy table.

at "path_name"

indicates the full path name of the external file you are selecting into. You can only use the at parameter to select into a proxy table.

external [table | file]

indicates that the type of the external object is either a file or a table. If you do indicate either a file or a table, select into assumes that you are using a table.

column delimeter “delimiter”

indicates the delimiter that you are using to separate columns. If you do not specify a delimiter, select into uses the tab character.

exp_row_size = num_bytes

specifies the expected row size for a table created with the select into command. Valid only for datarows and datapages locking schemes and only for tables that have variable-length rows. Valid values are 0, 1, and any value greater than the minimum row length and less than the maximum row length for the table. The default value is 0, which means that a server-wide default is used.

reservepagegap = num_pages

specifies a ratio of filled pages to empty pages that is to be left as select into allocates extents to store data. This option is valid only for the select into command. For each specified num_pages, one empty page is left for future expansion of the table. Valid values are 0 – 255. The default value is 0.

readpast

specifies that the query should silently skip rows with exclusive locks, without waiting and without generating a message.

with identity_gap

specifies the identity gap for the table. This value overrides the system identity gap setting for this table only.

value

is the identity gap amount.

If you are creating a table in a select into statement from a table that has a specific identity gap setting, the new table does not inherit the identity gap setting from the parent table. Instead, the new table uses the identity burning set factor setting. To give the new table a specific identity_gap setting, specify the identity gap in the select into statement. You can give the new table an identity gap that is the same as or different from the parent table.

from

indicates which tables and views to use in the select statement. It is required except when the select list contains no column names (that is, it contains constants and arithmetic expressions only):

select 5 x, 2 y, "the product is", 5*2 Result 

x       y                          Result
------- ------- ------------------ -----------
        5       2 the product is   10

At most, a query can reference 50 tables and 14 worktables (such as those created by aggregate functions). The 50-table limit includes:

view_name, table_name

lists tables and views used in the select statement. Specify the database name if the table or view is in another database, and specify the owner’s name if more than one table or view of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

If there is more than one table or view in the list, separate their names by commas. The order of the tables and views following the keyword from does not affect the results.

You can query tables in different databases in the same statement.

Table names and view names can be given correlation names (aliases), either for clarity or to distinguish the different roles that tables or views play in self-joins or subqueries. To assign a correlation name, give the table or view name, then a space, then the correlation name, like this:

select pub_name, title_id
    from publishers pu, titles t
    where t.pub_id = pu.pub_id

All other references to that table or view (for example in a where clause) must use the correlation name. Correlation names cannot begin with a numeral.

index index_name

specifies the index to use to access table_name. You cannot use this option when you select from a view, but you can use it as part of a select clause in a create view statement.

parallel

specifies a parallel partition or index scan, if Adaptive Server is configured to allow parallel processing.

degree_of_parallelism

specifies the number of worker processes that will scan the table or index in parallel. If set to 1, the query executes serially.

prefetch size

specifies the I/O size, in kilobytes, for tables bound to caches with large I/Os configured. You cannot use this option when you select from a view, but you can use it as part of a select clause in a create view statement. sp_helpcache shows the valid sizes for the cache an object is bound to or for the default cache. To configure the data cache size, use sp_cacheconfigure.

When using prefetch and designating the prefetch size (size), the minimum is 2K and any power of two on the logical page size up to 16K. prefetch size options in kilobytes are:

Logical page size

Prefetch size options

2

2, 4, 8 16

4

4, 8, 16, 32

8

8, 16, 32, 64

16

16, 32, 64, 128

The prefetch size specified in the query is only a suggestion. To allow the size specification, configure the data cache at that size. If you do not configure the data cache to a specific size, the default prefetch size is used.

If Component Integration Services is enabled, you cannot use prefetch for remote servers.

lru | mru

specifies the buffer replacement strategy to use for the table. Use lru to force the optimizer to read the table into the cache on the MRU/LRU (most recently used/least recently used) chain. Use mru to discard the buffer from cache and replace it with the next buffer for the table. You cannot use this option when you select from a view, but you can use it as part of a select clause in a create view statement.

holdlock

makes a shared lock on a specified table or view more restrictive by holding it until the transaction completes (instead of releasing the shared lock as soon as the required data page is no longer needed, whether or not the transaction has completed).

The holdlock option applies only to the table or view for which it is specified, and only for the duration of the transaction defined by the statement in which it is used. Setting the transaction isolation level 3 option of the set command implicitly applies a holdlock for each select statement within a transaction. The keyword holdlock is not permitted in a select statement that includes the for browse option. You cannot specify both a holdlock and a noholdlock option in a query.

If Component Integration Services is enabled, you cannot use holdlock for remote servers.

noholdlock

prevents the server from holding any locks acquired during the execution of this select statement, regardless of the transaction isolation level currently in effect. You cannot specify both a holdlock and a noholdlock option in a query.

shared

instructs Adaptive Server to use a shared lock (instead of an update lock) on a specified table or view. This allows other clients to obtain an update lock on that table or view. You can use the shared keyword only with a select clause included as part of a declare cursor statement. For example:

declare shared_crsr cursor
for select title, title_id 
from titles shared
where title_id like "BU%"

You can use the holdlock keyword in conjunction with shared after each table or view name, but holdlock must precede shared.

ANSI join

an inner or outer join that uses the ANSI syntax. The from clause specifies which tables are to be joined.

inner

includes only the rows of the inner and outer tables that meet the conditions of the on clause. The result set of a query that includes an inner join does not include any null supplied rows for the rows of the outer table that do not meet the conditions of the on clause.

outer

includes all the rows from the outer table whether or not they meet the conditions of the on clause. If a row does not meet the conditions of the on clause, values from the inner table are stored in the joined table as null values. The where clause of an ANSI outer join restricts the rows that are included in the query result.

left

left joins retain all the rows of the table reference listed on the left of the join clause. The left table reference is referred to as the outer table or row-preserving table.

In the queries below, T1 is the outer table and T2 is the inner table:

T1 left join T2
T2 right join T1
right

right joins retain all the rows of the table reference on the right of the join clause (see example above).

search_conditions

used to set the conditions for the rows that are retrieved. A search condition can include column names, expressions, arithmetic operators, comparison operators, the keywords not, like, is null, and, or, between, in, exists, any, and all, subqueries, case expressions, or any combination of these items. See where clause for more information.

group by

finds a value for each group. These values appear as new columns in the results, rather than as new rows.

When group by is used with standard SQL, each item in the select list must either have a fixed value in every row in the group or be used with aggregate functions, which produce a single value for each group. Transact-SQL has no such restrictions on the items in the select list. Also, Transact-SQL allows you to group by any expression (except by a column alias); with standard SQL, you can group by a column only.

You can use the aggregates listed in Table 1-34 with group by (expression is almost always a column name):

Table 1-34: Results of using aggregates with group by

Aggregate function

Result

sum([all | distinct] expression)

Total of the values in the numeric column.

avg([all | distinct] expression)

Average of the values in the numeric column.

count([all | distinct] expression)

Number of (distinct) non-null values in the column.

count(*)

Number of selected rows.

max(expression)

Highest value in the column.

min(expression)

Lowest value in the column.

See group by and having clauses for more information.

A table can be grouped by any combination of columns—that is, groups can be nested within each other. You cannot group by a column heading; you must use a column name, an expression, or a number representing the position of the item in the select list.

group by all

includes all groups in the results, even those that do not have any rows that meet the search conditions. See group by and having clauses for an example.

aggregate_free_expression

is an expression that includes no aggregates.

having

sets conditions for the group by clause, similar to the way that where sets conditions for the select clause. There is no limit on the number of conditions that can be included.

You can use a having clause without a group by clause.

If any columns in the select list do not have aggregate functions applied to them and are not included in the query’s group by clause (illegal in standard SQL), the meanings of having and where are somewhat different.

In this situation, a where clause restricts the rows that are included in the calculation of the aggregate, but does not restrict the rows returned by the query. Conversely, a having clause restricts the rows returned by the query, but does not affect the calculation of the aggregate. See group by and having clauses for examples.

order by

sorts the results by columns. In Transact-SQL, you can use order by for items that do not appear in the select list. You can sort by a column name, a column heading (or alias), an expression, or a number representing the position of the item in the select list (the select_list_number). If you sort by select list number, the columns to which the order by clause refers must be included in the select list, and the select list cannot be * (asterisk).

asc

sorts results in ascending order (the default).

desc

sorts results in descending order.

compute

used with row aggregates (sum, avg, min, max, and count) to generate control break summary values. The summary values appear as additional rows in the query results, allowing you to see detail and summary rows with one statement.

You cannot use a select into clause with compute.

If you use compute by, you must also use an order by clause. The columns listed after compute by must be identical to or a subset of those listed after order by, and must be in the same left-to-right order, start with the same expression, and not skip any expressions.

For example, if the order by clause is order by a, b, c, the compute by clause can be any (or all) of these:

compute by a, b, c 
compute by a, b 
compute by a

The keyword compute can be used without by to generate grand totals, grand counts, and so on. order by is optional if you use compute without by. See compute clause for details and examples.

If Component Integration Services is enabled, compute is not forwarded to remote servers.

for {read only | update}

specifies that a cursor result set is read-only or updatable. You can use this option only within a stored procedure and only when the procedure defines a query for a cursor. In this case, the select is the only statement allowed in the procedure. It defines the for read only or for update option (instead of the declare cursor statement). This method of declaring cursors provides the advantage of page-level locking while fetching rows.

If the select statement in the stored procedure is not used to define a cursor, Adaptive Server ignores the for read only | update option. See the Embedded SQL™ documentation for more information about using stored procedures to declare cursors. For information about read-only or updatable cursors, see the Transact-SQL User’s Guide.

of column_name_list

is the list of columns from a cursor result set defined as updatable with the for update option.

at isolation

specifies the isolation level (0, 1, 2 or 3) of the query. If you omit this clause, the query uses the isolation level of the session in which it executes (isolation level 1 by default). The at isolation clause is valid only for single queries or within the declare cursor statement. Adaptive Server returns a syntax error if you use at isolation:

If there is a union operator in the query, you must specify the at isolation clause after the last select. If you specify holdlock, noholdlock, or shared in a query that also specifies at isolation read uncommitted, Adaptive Server issues a warning and ignores the at isolation clause. For the other isolation levels, holdlock takes precedence over the at isolation clause. For more information about isolation levels, see the Transact-SQL User’s Guide.

If Component Integration Services is enabled, you cannot use at isolation for remote servers.

read uncommitted | 0

specifies isolation level 0 for the query.

read committed | 1

specifies isolation level 1 for the query.

repeatable read | 2

specifies transaction isolation level 2 for the query.

serializable | 3

specifies isolation level 3 for the query.

for browse

must be attached to the end of a SQL statement sent to Adaptive Server in a DB-Library browse application. See the Open Client DB-Library Reference Manual for details.

plan "abstract plan"

specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. See Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide for more information.

Examples

Example 1

Selects all rows and columns from the publishers table:

select * from publishers

pub_id pub_name                    city                 state
------ --------------------------- -------------------- -----
0736    New Age Books              Boston               MA   
0877    Binnet & Hardley           Washington           DC   
1389    Algodata Infosystems       Berkeley             CA   

Example 2

Selects all rows from specific columns of the publishers table:

select pub_id, pub_name, city, state from publishers

Example 3

Selects all rows from specific columns of the publishers table, substituting one column name and adding a string to the output:

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

                       Publisher                      pub_id
----------------------- ----------------------------- ------
The publisher’s name is New Age Books                 0736  
The publisher’s name is Binnet & Hardley              0877  
The publisher’s name is Algodata Infosystems          1389  

Example 4

Selects all rows from specific columns of the titles table, substituting column names:

select type as Type, price as Price
from titles

Example 5

Specifies the locking scheme and the reserve page gap for select into:

select title_id, title, price 
into bus_titles
lock datarows with reservepagegap = 10
from titles
where type = "business"

Example 6

Selects only the rows that are not exclusively locked. If any other user has an exclusive lock on a qualifying row, that row is not returned:

select title, price
from titles readpast
    where type = "news"
    and price between $20 and $30

Example 7

Selects specific columns and rows, placing the results into the temporary table #advance_rpt:

select pub_id, total = sum (total_sales) 
    into #advance_rpt
from titles 
where advance < $10000 
    and total_sales is not null 
group by pub_id 
having count(*) > 1

Example 8

Concatenates two columns and places the results into the temporary table #tempnames:

select "Author_name" = au_fname + " " + au_lname
    into #tempnames
    from authors

Example 9

Selects specific columns and rows, returns the results ordered by type from highest to lowest, and calculates summary information:

select type, price, advance from titles 
order by type desc 
compute avg(price), sum(advance) by type 
compute sum(price), sum(advance)

Example 10

Selects specific columns and rows, and calculates totals for the price and advance columns:

select type, price, advance from titles compute sum(price), sum(advance)

Example 11

Creates the coffeetabletitles table, a copy of the titles table which includes only books priced over $20:

select * into coffeetabletitles from titles 
where price > $20

Example 12

Creates the newtitles table, an empty copy of the titles table:

select * into newtitles from titles 
where 1 = 0

Example 13

Updates the existing authors table to include only books priced over $20:

select * into authors from titles 
where price > $20

Example 14

Gives an optimizer hint:

select title_id, title
    from titles (index title_id_ind prefetch 16)
    where title_id like "BU%"

Example 15

Selects the IDENTITY column from the sales_east and sales_west tables by using the syb_identity keyword:

select sales_east.syb_identity, sales_west.syb_identity
from sales_east, sales_west

Example 16

Creates the newtitles table, a copy of the titles table with an IDENTITY column:

select *, row_id = identity(10)
into newtitles from titles

Example 17

Specifies a transaction isolation level for the query.

select pub_id, pub_name
from publishers
at isolation read uncommitted

Example 18

Selects from titles using the repeatable read isolation level. No other user can change values in or delete the affected rows until the transaction completes:

begin tran
select type, avg(price)
    from titles
    group by type
at isolation repeatable read

Example 19

Gives an optimizer hint for the parallel degree for the query:

select ord_num from salesdetail 
    (index salesdetail parallel 3)

Example 20

Joins the titleauthor and the titles tables on their title_id columns. The result set only includes those rows that contain a price greater than 15:

select au_id, titles.title_id, title, price
from titleauthor inner join titles
on titleauthor.title_id = titles.title_id
and price > 15

Example 21

The result set contains all the authors from the authors table. The authors who do not live in the same city as their publishers produce null values in the pub_name column. Only the authors who live in the same city as their publishers, Cheryl Carson and Abraham Bennet, produce a non-null value in the pub_name column:

select au_fname, au_lname, pub_name
from authors left join publishers
on authors.city = publishers.city

Example 22

Create a new table (newtable) from the existing table (oldtable) with an identity gap, you specify it in the select into statement:

select identity into newtable 
with identity_gap = 20
from oldtable

For more information about identity gaps, see “Managing Identity Gaps in Tables” in Chapter 7, “Creating Databases and Tables” in the Transact-SQL User’s Guide.

Usage


Using ANSI join syntax


Using select into


Converting the NULL properties of a target column with select...into


Specifying a lock scheme with select...into


Using index, prefetch, and lru | mru


Using parallel


Using readpast

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The following are Transact-SQL extensions:

Permissions

select permission defaults to the owner of the table or view, who can transfer it to other users.

See also

Commands compute clause, create index, create trigger, delete, group by and having clauses, insert, order by clause, set, union operator, update, where clause

Functions avg, count, isnull, max, min, sum

System procedures sp_cachestrategy, sp_chgattribute, sp_dboption




Copyright © 2004. Sybase Inc. All rights reserved. set

View this book as PDF