SELECT Statement

SELECT Statement (version 6.5)

Provides the WITH CUBE and WITH ROLLUP options as part of the GROUP BY clause. These operators produce an n-dimensional results set rather than the typical zero or one-dimensional results set.

In SQL Server 6.5, the LIKE operator will search on strings that contain wildcard characters by using an ESCAPE option.

For additional syntax information for SELECT statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

SELECT [ALL | DISTINCT] select_list
INTO [new_table_name]
[FROM table_name [, table_name2 […, table_name16]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]]

where

ALL

Specifies that duplicate rows can appear in the results set.

DISTINCT

Specifies that only unique rows can appear in the results set. Null values are considered equal for the purposes of the DISTINCT keyword; only one NULL is selected no matter how many are encountered.

select_list

Is the list of columns to select and can be one or more of the following.



Column	Description
Asterisk (*)	Represents all columns in the order in which they were specified in the CREATE TABLE statement. Affects all tables in the FROM clause.
A list of column names	Specifies the order in which you want to see column names. If the select_list contains multiple column names, separate the names with commas.
A column name and column heading	Specifies a heading to replace the default column heading (the column name), in the following form: column_heading = column_name Or column_name column_heading Or column_name AS column_heading
An expression	Specifies a column name, constant, function, or any combination of column names, constants, and functions connected by an operator(s), or a subquery. The expression can be used to set up an identity column by using the IDENTITY() function with SELECT INTO. For example, ID = IDENTITY(int,1,1).
IDENTITYCOL keyword	Specifies the IDENTITYCOL keyword instead of the name of a column that has the IDENTITY property.
Local or global variable	Specifies a local or global variable.
Local variable assignment	Specifies a local variable assignment in the form: @variable = expression

INTO new_table_name

Is the name of a new table to be created based on the columns specified in the select_list and the rows chosen in the WHERE clause.

FROM

Specifies the table(s) and view(s) used in the SELECT statement.

table_name =

{simple_table_name | derived_table | joined_table}

where

simple_table_name = [[database.] owner.] {table_name | view_name} [[AS] alias_name] [(optimizer_hints)]

Specifies the name of the table or view used in the SELECT statement.

where

table_name | view_name

Is the name(s) of the table(s) and view(s) used in the SELECT statement. If the list contains more than one table or view, separate the names with commas. If the table(s) or view(s) exist in another database(s), use a fully qualified table or view name, such as database.owner.object_name.

In SQL Server 6.5, errors are reported when redundant table names appear in the FROM clause. For example, these SELECT statements were supported in earlier releases but generate errors in SQL Server 6.5. In the first SELECT statement the tables were treated as two different tables. In the second SELECT statement the second authors reference is discarded.

SELECT * FROM pubs..authors,pubs.dbo.authors

SELECT * FROM authors,authors

To avoid generating these errors, use trace flag 110. This flag causes SQL Server to revert to the table resolution methods of earlier releases. For more information about trace flag 110, see Trace Flags.

alias_name

Is the name of an alias. Each table_name or view_name can be given an alias, either for convenience or to distinguish a table or view in a self-join or subquery.

optimizer_hints

Specify a locking method, an index, or no index (table scan) be used by the optimizer with this table and for this SELECT. Although this is an option, the optimizer can usually pick the best optimization method without hints being specified.

derived_table = (select_statement) [AS] alias_name

Is a subquery as specified in select_statement and uses the results set of the subquery as the input to the query being executed.

where

select_statement

Is the statement that retrieves rows from the database.

joined_table = {table_name CROSS JOIN table_name | table_name [join_type] JOIN table_name ON search_conditions}

Is the results set that is the product of two or more tables being combined.

join_type = {INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]}

The following types of ANSI joins are permitted:

CROSS JOIN: Is the cross product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style join.
INNER: Specifies that all inner rows be returned. Discards unmatched rows.
LEFT [OUTER]: Specifies that all left outer rows be returned. Specifies that all rows from the left table that did not meet the condition specified be included in the results set, and output columns from the other table be set to NULL.
RIGHT [OUTER]: Specifies that all rows from the right table that did not meet the condition specified will be included in the results set, and output columns that correspond to the other table be set to NULL.
FULL [OUTER]: If a row from either table does not match the selection criteria, specifies the row be included in the results set and its output columns that correspond to the other table be set to NULL.

Earlier versions of SQL Server outer joins are supported in the following examples:

Right Outer Join (*=)

Select authors.au_name, titleauthor.title_id
FROM authors.titleauthor
WHERE authors.au_id *=titleauthor.au_id

Left Outer Join (=*)

SELECT authors.au_lname, titleauthor.title_id
FROM authors, titleauthor
WHERE titleauthor.au_id =* authors.au_id

Note Earlier versions of SQL Server joins cannot be used within the same statement as ANSI-style joins.

WHERE clause =

WHERE search_conditions

Specifies the conditions for the rows returned in the results set. There is no limit to the number of search_conditions that can be included in an SQL statement.

The LIKE operator has an ESCAPE option, which allows wildcard characters to be searched for in strings.

Consider the column description in a table, finances contains the value 'lending_rate%'.

The characters '%' and '_' are wildcard characters. If you want to search for rows where the column contains the sequence 'g_', you need to use the ESCAPE option because '_' is a wildcard character.

SELECT * FROM finances 
WHERE description LIKE 'gS_' ESCAPE 'S'

The ESCAPE clause uses "S" as an escape character and if the search comes across "S" in the pattern string, it will take the next character "_" as a normal character rather than a wildcard character.

GROUP BY clause = GROUP BY [ALL] aggregate_free_expression [, aggregate_free_expression]...[, aggregate_free_expression] [WITH {CUBE | ROLLUP}]

Specifies the groups into which the table will be partitioned and, if aggregate functions are included in the select_list, finds a summary value for each group.

The maximum number of bytes allowed in the GROUP BY clause is 900.

When the CUBE operator is specified, in addition to the usual aggregate rows, super-aggregate rows are introduced into the results set. A super-aggregate row is a summary row for all of the aggregate rows produced for one of the aggregate-free expressions in the GROUP BY clause.

For example, if your query was computing the average price of an automobile, grouped by manufacturer, a single super-aggregate row would be added that contained the average price of all cars. Suppose your query was grouped instead by manufacturer and color. The results set would then contain super-aggregates for each manufacturer, regardless of color, and separate super-aggregates for each color, regardless of manufacturer.

If the WITH ROLLUP clause is specified, a subset of super-aggregates is computed along with the usual aggregate rows. This is useful when you have sets within sets. Consider grouping by the expressions, column1, column2, and column3. Applying the ROLLUP operator results in these rows:

column1        column2        column3
column1        column2        (null)
column1        (null)        (null)
(null)        (null)        (null)

A maximum of 10 grouping expressions are permitted in a GROUP BY clause when WITH CUBE or WITH ROLLUP is specified. Otherwise, 16 grouping expressions are permitted.

You cannot specify the GROUP BY ALL clause when you use the CUBE or ROLLUP operators.

The CUBE and ROLLUP operators can be disabled when trace flag 204 is ON.

HAVING clause =

HAVING search_conditions

Specifies the conditions for aggregate functions in the select_list; the search_conditions restrict the rows returned by the query but do not affect the calculations of the aggregate function(s).

ORDER BY clause

Is the sort order used on columns returned.

COMPUTE clause =

COMPUTE row_aggregate(column_name)
[, row_aggregate(column_name)...]
[BY column_name [, column_name]...]

where

COMPUTE: Specifies that control-break summary changes be generated for row aggregate functions (SUM, AVG, MIN, MAX, and COUNT).
BY: Specifies that the values for row-aggregate functions be calculated for subgroups.
FOR BROWSE: Specifies that updates be allowed while viewing data in client applications that use DB-Library.

Remarks

Important When creating a derived table, the SELECT statement does not support the use of the INTO, ORDER BY, COMPUTE, or COMPUTE BY clauses.

The FROM clause has been changed to support the ANSI-SQL syntax for joined tables and derived tables. ANSI-SQL syntax provides the INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER join operators.

Although the outer join operators *= and =* from earlier versions of SQL Server are supported, you cannot use both outer join operators and ANSI-SQL style joined tables in the same query.

When trace flag 204 is enabled, only the SQL Server version 6.0 and earlier SELECT statement syntax is permitted: joined tables, derived tables, and other ANSI features are not. Trace flag 204 is otherwise unchanged. Also, trace flag 330 enables full output that contains information about joins when you are using SHOWPLAN. For more information about trace flags 204 and 330, see Trace Flags .

UNION and JOIN within a FROM clause are supported within views as well as in derived tables and subqueries.

A self-join is a table that joins upon itself. Inserts or updates that are based on a self-join follow the order in the FROM clause.

Note In SQL Server version 6.5, an error occurs if no column name is given to a column created by a SELECT INTO or CREATE VIEW statement. For more information, see the discussion of trace flag 246 in Trace Flags.

The CUBE operator provides a superset of data in the results set. Not only is the typical joined data returned, each operand (column) in the GROUP BY clause is bound under the NULL keyword and applied to all other operands (columns).

The NULL keyword in this case represents all the values in a particular column.

Note Using the HAVING clause in the SELECT statement does not effect the way the CUBE operator groups the results set and returns summary aggregate rows.

Examples

A. Use an Aggregate Function in a SELECT Statement

This example uses the following table to demonstrate how the CUBE operator affects the results set.

product_ID	customer_ID	number_of_orders
----------	-----------	----------------
10	a	10
20	b	10
10	a	20
30	b	10
30	a	10
20	b	20
10	b	30
10	c	40
20	c	10
30	b	40
30	a	10
10	a	50

First, issue a typical query with a GROUP BY clause and the results set.

SELECT product_ID, customer_ID, SUM(number_of_orders)
FROM sales
GROUP BY product_ID, customer_ID

The GROUP BY causes the results set to form groups within groups. This is the results set:

product_ID	customer_ID	Sum (number_of_orders)
---------	---------	---------------
10	a	80
10	b	30
10	c	40
20	b	30
20	c	10
30	a	20
30	b	50

B. Use the CUBE Operator

Next, do a query with a GROUP BY clause by using the CUBE operator. The results set should include the same information plus super-aggregate information for each of the GROUP BY columns.

SELECT product_ID, customer_ID, SUM(number_of_orders)
FROM sales
GROUP BY product_ID, customer_ID
WITH CUBE

The results set for the CUBE operator holds the values from the simple GROUP BY result set above and adds the super-aggregates for each column in the GROUP BY clause. (null) represents all values in the set from which the aggregate is computed.

product_ID	customer_ID	SUM (number_of_orders)
-----------	------------	------------------
10	a	80
10	b	30
10	c	40
10	(null)	150
20	b	30
20	c	10
20	(null)	40
30	a	20
30	b	50
30	(null)	70
(null)	(null)	260
(null)	a	100
(null)	b	110
(null)	c	50

C. Use the ROLLUP Operator with the COUNT Aggregate

This example performs a ROLLUP operation on the company and department columns and totals the number of employees.

The ROLLUP operator produces a summary of aggregates. This is useful in situations where summary information is needed but a full CUBE provides extraneous data. This is also useful when you have sets within sets. For example, you could think of departments within a company as a set within a set.

SELECT company, department COUNT(*) FROM personnel GROUP BY company, department WITH ROLLUP

This is the results set:

company	department	Number of Employees
abc	finance	10
abc	engineering	40
abc	marketing	40
abc	(null)	90
def	accounting	20
def	personnel	30
def	payroll	40
def	(null)	90
(null)	(null)	180

D. Use the ROLLUP Operator

This example modifies the SELECT statement above to use the ROLLUP operator.

SELECT product_ID, customer_ID, SUM(number_of_orders) 
AS 'Sum number_of_orders'
FROM sales
GROUP BY product_ID, customer_ID
WITH ROLLUP

This is the results set:

product_ID	customer_ID	Sum number_of_orders
----------	-----------	----------------
10	a	80
10	b	30
10	c	40
10	(null)	150
20	b	30
20	c	10
20	(null)	40
30	a	20
30	b	50
30	(null)	70
(null)	(null)	260

The GROUPING function allows you to discriminate between a true null value and a null value representing an ALL value.



Expression evaluation	GROUPING returns	Description
(null)	1	Represents the set of all values.
any other value	0	Represents a particular value.

A new function GROUPING can be used only with CUBE or ROLLUP. The GROUPING function returns 1 when an expression evaluates to NULL because the column value is (null) and represents the set of all values. The GROUPING function returns 0 when the column has a value other than (null). The returned value has a tinyint datatype.

E. Use the GROUPING Function

This example modifies the SELECT statement that was used in the previous example.

SELECT product_ID, customer_ID, SUM(number_of_orders) 
AS 'Sum number_of_orders',
GROUPING(product_ID) AS 'Grouping product_ID',GROUPING(customer_ID) AS 'Grouping customer_ID'
FROM sales
GROUP BY product_ID, customer_ID
WITH ROLLUP

This is the results set:

product_ID	customer_ID	Sum number _of_orders	Grouping product_ID	Grouping customer_ID
----------	-----------	----------	----------	----------
10	a	80	0	0
10	b	30	0	0
10	c	40	0	0
10	(null)	150	0	1
20	b	30	0	0
20	c	10	0	0
20	(null)	40	0	1
30	a	20	0	0
30	b	50	0	0
30	(null)	70	0	1
(null)	(null)	260	1	1

F. Use Left Outer Join

This example joins two tables on au_id and preserves the unmatched rows from the left table. The authors table is matched with the titleauthor table on the au_id columns in each table. All authors appear in the results set, whether or not they have published any books.

SELECT authors.au_lname, authors.au_fname, titleauthor.title_id
FROM authors LEFT OUTER JOIN titleauthor 
ON authors.au_id =titleauthor.au_id

G. Use Inner Join with Three Tables

This example joins three tables: authors, titleauthors and titles. The results set contains a list of authors and royalties paid to date.

SELECT authors.au_fname, authors.au_lname, sum(titles.royalty * titles.ytd_sales/100)
FROM authors 
JOIN titleauthor ON authors.au_id=titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
GROUP BY authors.au_lname, authors.au_fname
ORDER BY authors.au_lname

H. Use Full Outer Join

This example returns the book title and its corresponding publisher in the titles table. It also returns any publishers who have not published books listed in the titles table, and any book titles with a publisher other than the one listed in the publishers table.

SELECT titles.title, publishers.pub_name
FROM publishers FULL OUTER JOIN titles
ON titles.pub_id = publishers.pub_id
WHERE titles.pub_id IS NULL OR publishers.pub_id IS NULL

I. Use Right Outer Join

This example joins two tables on pub_id and preserves the unmatched rows from the right table. The publishers table is matched with the titles table on the pub_id column in each table. All publishers appear in the results set, whether or not they have published any books.

SELECT publishers.pub_id, titles.title, titles.title_id
FROM titles RIGHT OUTER JOIN publishers 
ON publishers.pub_id = titles.pub_id

J. Use Cross Join

This example returns the cross product of the two tables authors and publishers. A list of all possible combinations of au_lname rows and all pub_name rows are returned.

SELECT au_lname, pub_name 
FROM authors CROSS JOIN publishers