SQL Tutorial and Tips

      Select | Insert | Update | Delete

      Open Cursor | Fetch

      Close Cursor | Union

        Structured Query Language
SELECT

Syntax:

SELECT [ALL | DISTINCT] select_list
[INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
[[, {table_name2 | view_name2}[(optimizer_hints)]
[..., {table_name16 | view_name16}[(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]




ALL
Retrieves all rows in the results. ALL is the default.

DISTINCT
Includes only unique rows in the results. 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
Specifies the columns to select. Can be one or more of the following:

- Asterisk (*), representing all columns listed in the order in which they were specified in the CREATE TABLE statement for all tables in the FROM clause, in the order they appear.

- A list of column names, specified in the order in which you want to see them. If the select_list contains multiple column names, separate the names with commas.

- A column name and column heading that will replace the default column heading (the column name), in the following form:

column_heading = column_name
or
column_name column_heading



The column_heading must be in quotation marks if spaces are used. For example:

SELECT 'Author Last Name' = au_lname
FROM authors
- An expression (a column name, constant, function, or any combination of column names,
constants, and functions connected by an operator(s), a CASE expression, or a subquery). For details, see the Expressions topic.

- The IDENTITYCOL keyword instead of the name of a column that has the IDENTITY property.
For details, see the CREATE TABLE statement.

- A local or global variable. For details, see the Variables topic.

- A local variable assignment, in the form:

@variable = expression

Note When the select_list includes a variable assignment(s), it cannot be combined with data-retrieval operations.

INTO new_table_name
Creates a new table based on the columns specified in the select_list and the rows chosen in the WHERE clause. To select into a permanent table, the select into/bulkcopy option must be on (by executing the sp_dboption system stored procedure). By default, the select into/bulkcopy option is off in newly created databases. The new table name (new_table_name) must follow the same rules as table_name (described later in this section) with these exceptions:

- If select into/bulkcopy is on in the database where the table is to be created, a permanent table is created. The table name must be unique in the database and conform to the rules for Identifiers.

- If select into/bulkcopy is not on in the database where the table is to be created, permanent tables cannot be created using SELECT INTO; only local or global temporary tables can be created. To create a temporary table, the table name must begin with a pound sign (#). For details on temporary tables, see the CREATE TABLE statement.

SELECT INTO is a two-step operation. The first step creates the table. The user executing the statement must have CREATE TABLE permission in the destination database. The second step inserts the specified rows into the new table. If the second step fails for any reason (hardware failure, exceeding a system resource, and so on), the new table will exist but have no rows.

You can use SELECT INTO to create an identical table definition (different table name) with no data by having a false condition in the WHERE clause. You cannot use SELECT INTO with the COMPUTE clause or inside a user-defined transaction. For details about user-defined transactions, see the Transactions topic. When selecting an existing identity column into a new table, the new column inherits the IDENTITY property unless one of the following conditions is true:

- The SELECT statement contains a join, GROUP BY clause, or aggregate function.
- Multiple SELECT statements are joined with UNION.
- The identity column is listed more than once in the select_list.
- The identity column is part of an expression.

If any of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If none of the conditions is true, the new table will inherit the identity column. All rules, restrictions, and so on, for the identity columns apply to the new table.


FROM
Indicates the specific table(s) and view(s) that are used in the SELECT statement. FROM is required except when the select_list contains only constants, variables, and arithmetic expressions (no column names). The FROM clause allows a maximum of 16 tables and views. Tables in subqueries are counted as part of this total.

table_name | view_name =
[[database.]owner.]{table_name. | view_name.}

Specifies 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 (database_name.owner.object_name).

Each table_name or view_name can be given an alias, either for convenience or to distinguish the different roles that a table or view plays in a self-join or subquery. Aliases (when defined) must be used for any ambiguous column references and must always match the alias reference (the full table name cannot be used if an alias has been defined). To use an alias, specify the object name, and then a space, and then the alias name, like this:

SELECT au_lname, au_fname, title
FROM titles t, authors a, titleauthor ta
WHERE ta.title_id = t.title_id
AND ta.au_id = a.au_id
ORDER BY title, au_lname, au_fname

The order of the tables and views after the FROM keyword does not affect the results set returned.

WHERE clause =
WHERE search_conditions
Specifies the restricting 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. For more information, see the Search Conditions topic.

GROUP BY clause =
GROUP BY [ALL] aggregate_free_expression
[, aggregate_free_expression]...
GROUP BY
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. You can refer to these new summary columns in the HAVING clause. The text and image datatypes cannot be used in a GROUP BY clause.

When a GROUP BY clause is used, each item in the select_list must produce a single value for each group. A table can be grouped by any combination of columns; however, you cannot group by a column heading-you must use a column name or an expression. In Transact-SQL, any expression is valid (although not with column headings). With standard SQL, you can group only by a column. You can use GROUP BY for a column or expression that does not appear in the select_list. Null values in the GROUP BY column are put into a single group. The aggregate functions, which calculate summary values from the non-null values in a column, can be divided into two groups:

Scalar Aggregate functions are applied to all the rows in a table (producing a single value per function). An aggregate function in the select_list with no GROUP BY clause applies to the whole table and is one example of a scalar.

Vector Aggregate functions are applied to all rows that have the same value in a specified column or expression with the GROUP BY clause and, optionally, the HAVING clause (producing a value for each group per function).

For the details about aggregate functions, see the Functions topic.

ALL
Includes all groups in the results, even those that don't have any rows that meet the search_conditions.

aggregate_free_expression
Is an expression that includes no aggregate functions. Aggregate functions can be used in the select_list preceding the GROUP BY clause. For the details about aggregate functions, see the Functions topic.

HAVING clause =
HAVING search_conditions
Specifies a different type of restriction for aggregate functions in the select_list; the search_conditions restrict the rows returned by the query but do not affect the calculation(s) of the aggregate function(s). When a WHERE clause is used, the search_conditions restrict the rows that are included in the calculation of the aggregate function but do not restrict the rows returned by the query. The text and image datatypes cannot be used in a HAVING clause.
There is no limit on the number of conditions that can be included in search_conditions. You can use a HAVING clause without a GROUP BY clause. When the HAVING clause is used with GROUP BY ALL, the HAVING clause negates the meaning of ALL.

ORDER BY clause =
ORDER BY {{table_name. | view_name.}column_name
| select_list_number | expression} [ASC | DESC]
[...{{table_name16. | view_name16.}column_name
| select_list_number | expression} [ASC | DESC]]

Sorts the results by columns. You can sort as many as 16 columns. In Transact-SQL, the ORDER BY clause can include 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. The select_list can be a single asterisk (*). If you use COMPUTE BY, you must also specify an ORDER BY clause.

Null values are sorted before all others, and text or image columns cannot be used in an ORDER BY clause. Subqueries and view definitions cannot include an ORDER BY clause, a COMPUTE clause, or the INTO keyword. However, through Transact-SQL extensions, you can sort by expressions and aggregates if you use their select_list_number in the ORDER BY clause.

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

COMPUTE
Used with row aggregate functions (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 rows and summary rows within one results set. You can calculate summary values for subgroups, and you can calculate more than one aggregate function for the same group.

The COMPUTE clause cannot be used with INTO and cannot contain aliases for column names, although aliases can be used in the select_list. The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on. The ORDER BY clause is optional only if you use the COMPUTE keyword without BY.

BY
Indicates that values for row aggregate functions are to be calculated for subgroups. Whenever the value of BY changes, row aggregate function values are generated. If you use BY, you must also use an ORDER BY clause. Listing more than one item after BY breaks a group into subgroups and applies a function at each level of grouping. The columns listed after COMPUTE clause must be identical to or a subset of those listed after ORDER BY clause, and must be in the same left-to-right order, start with the same expression, and not skip any expression.

For example, if the ORDER BY clause is:
ORDER BY a, b, c

The COMPUTE clause can be any (or all) of these:
COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a

FOR BROWSE
Allows you to perform updates while viewing data in client application programs using DB-Library.

A table can be browsed in an application under the following conditions:
- The table includes a time-stamped column (defined with the timestamp datatype).
- The table has a unique index.
- The FOR BROWSE option is at the end of the SELECT statement(s) sent to SQL Server.

For details, see Microsoft SQL Server Programming DB-Library for C.

Do not use the optimizer_hint HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. The FOR BROWSE option cannot appear in SELECT statements joined by the UNION operator.

Remarks:
The length returned for text columns included in the select_list defaults to whichever is the smallest > the actual size of the text, the default TEXTSIZE session setting, or the hardcoded application limit. To change the length of returned text for the session, use the SET statement. By default, the limit on the length of text data returned with a SELECT statement is 4K.

To retrieve data from remote SQL Servers, you can call remote stored procedures. For more information, see the CREATE PROCEDURE and EXECUTE statements.

Using the GROUP BY clause and the HAVING clause The following list shows the requirements for processing a SELECT with the GROUP BY clause and the HAVING clause, and it shows how the rows returned in the results set are derived:

1. The WHERE clause excludes rows that do not meet its search_conditions.

2. The GROUP BY clause collects the surviving rows into one group for each unique value in the GROUP BY clause. Omitting the GROUP BY clause creates a single group for the whole table.

3. The HAVING clause excludes rows that do not meet its search_conditions. The HAVING clause tests only rows, but the presence or absence of a GROUP BY clause can make the behavior of a HAVING clause appear contradictory. For example:

- When the query includes a GROUP BY clause, the HAVING clause excludes groups from the results.

- By default, the HAVING clause can refer to aggregates only when the query contains no GROUP BY clause.

- To allow queries that contain aggregates or a GROUP BY clause with items in the select_list that are not in the GROUP BY clause and are not aggregate functions, set trace flag 204. For details, see the Trace Flags topic.

4. Aggregate functions specified in the select_list calculate summary values for each surviving group.

For the GROUP BY clause, the HAVING clause, and aggregate functions to accomplish the goal of one row and one summary value per group, ANSI-standard SQL requires:

- Columns in a select_list must also be in the GROUP BY clause or be parameters of aggregate functions.

- Columns in a HAVING clause must have only one value.

- A query with a HAVING clause should have a GROUP BY clause. But if it doesn't, all the rows not excluded by the WHERE clause are considered to be a single group.

Transact-SQL extensions to standard SQL make displaying data more flexible by allowing references to columns and expressions that are not used for creating groups or summary calculations. For example:

- The GROUP BY clause can include expressions.
- GROUP BY ALL displays all groups, even those excluded from calculations by a WHERE clause.

Permission:
SELECT permission defaults to the owner of the table or view, who can grant it to other users using the GRANT statement. If the INTO clause is used to create a permanent table, then the user must have CREATE TABLE permission in the destination database.

Examples:

A. Simple SELECT: All Rows, All Columns This example returns all rows (no WHERE clause) and all columns (*) from the publishers table in the pubs database.

SELECT * FROM publishers

B. Simple SELECT: Subset of Columns, All Rows This example returns all rows (no WHERE clause) and only a subset of the columns (pub_id, pub_name, city, state) from the publishers table in the pubs database.

SELECT pub_id, pub_name, city, state

FROM publishers


C. Simple SELECT: Subset of Rows, Subset of Columns This examples returns only the rows where the advance given is less than $10,000 and there are current year-to-date sales.

SELECT pub_id, total = sum (ytd_sales)
FROM titles
WHERE advance < $10000
AND ytd_sales IS NOT NULL


D. SELECT with GROUP BY, COMPUTE, and ORDER BY Clauses This example returns only those rows with current year-to-date sales and then computes the average book cost and total advances in descending order by type. Four columns of data are returned including a truncated title. Notice that all computed columns appear within the select_list.

SELECT title = CONVERT(char(20), title), type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)


title type price advance
---------------------- ----------------- ------- ----------
Fifty Years in Bucki trad_cook 11.95 4,000.00
Onions, Leeks, and G trad_cook 20.95 7,000
Sushi, Anyone? trad_cook 14.99 8,000.00

avg
=========
15.96
sum
=========
19,000.00

title type price advance ---------------------- ----------------- ------- ---------- Computer Phobic AND psychology 21.59 7,000.00
Emotional Security: psychology 7.99 4,000.00
Is Anger the Enemy? psychology 10.95 2,275.00
Life Without Fear psychology 7.00 6,000.00
Prolonged Data Depri psychology 19.99 2,000.00

avg
=========
13.50
sum
=========
21,275.00

title type price advance
---------------------- ----------------- ------- ----------
But Is It User Frien popular_comp 22.95 7,000.00
Secrets of Silicon V popular_comp 20.00 8,000.00

avg
=========
21.48
sum
=========
15,000.00

title type price advance
---------------------- ----------------- ------- ----------
Silicon Valley Gastr mod_cook 19.99 0.00
The Gourmet Microwav mod_cook 2.99 15,000.00

avg
=========
11.49
sum
=========
15,000.00

title type price advance
---------------------- ----------------- ------- ----------
Cooking with Compute business 11.95 5,000.00
Straight Talk About business 19.99 5,000.00
The Busy Executive's business 19.99 5,000.00
You Can Combat Compu business 2.99 10,125.00

avg
=========
13.73
sum
=========
25,125.00

sum
=========
236.26
sum
=========
88,400.00
(22 row(s) affected)

E. All Rows with Computed Sums
This example shows only three columns in the select_list and gives totals based on all prices and all advances at the end of the results.

SELECT type, price, advance
FROM titles
COMPUTE SUM(price), SUM(advance)


type price advance
------------ -------------------------- --------------------------
business 19.99 5,000.00
business 11.95 5,000.00
business 2.99 10,125.00
business 19.99 5,000.00
mod_cook 19.99 0.00
mod_cook 2.99 15,000.00
UNDECIDED (null) (null)
popular_comp 22.95 7,000.00
popular_comp 20.00 8,000.00
popular_comp (null) (null)
psychology 21.59 7,000.00
psychology 10.95 2,275.00
psychology 7.00 6,000.00
psychology 19.99 2,000.00
psychology 7.99 4,000.00
trad_cook 20.95 7,000.00
trad_cook 11.95 4,000.00
trad_cook 14.99 8,000.00

sum
==========================
236.26

sum
==========================
95,400.00

(19 row(s) affected) F. Create a Temporary Table with SELECT INTO This example causes a temporary table to be created in tempdb. To use this table, always refer to it with the exact name shown, including the pound sign (#). SELECT * INTO #coffeetabletitles FROM titles WHERE price < $20 SELECT name FROM sysobjects WHERE name LIKE '#c%' name ------------------------------ (0 row(s) affected) SELECT name FROM tempdb..sysobjects WHERE name LIKE '#c%' go name ------------------------------ #coffeetabletitles__0000EC153E (1 row(s) affected) G. Create a Permanent Table with SELECT INTO This example shows the steps needed to create a permanent table. USE master go sp_dboption 'pubs', 'select into', TRUE go CHECKPOINTing database that was changed. USE pubs go SELECT * INTO newtitles FROM titles WHERE price > $25 OR price < $20 (12 row(s) affected) SELECT name FROM sysobjects WHERE name LIKE 'new%' go name ------------------------------ newtitles (1 row(s) affected) H. Optimizer Hints: TABLOCK and HOLDLOCK The following partial transaction shows how to place an explicit shared table lock on t1 without the overhead of reading any records from it. BEGIN TRAN SELECT count(*) FROM t1 (TABLOCK HOLDLOCK) I. Optimizer Hints: Using the Name of an Index This example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table. SELECT au_lname, au_fname, phone FROM authors (INDEX = aunmind) WHERE au_1name = 'Smith' J. Optimizer Hints: Forcing a Table Scan This example shows that using an index of 0 will force a table scan. SELECT emp_id, fname, lname, hire_date FROM employee (index = 0) WHERE hire_date '10/1/1994'


Home         Business Directory

Festivals and Fairs (USA)