Select (SQL)

A SQL SELECT statement returns a result set of records from one or more tables. [cite web |url=http://msdn2.microsoft.com/en-us/library/ms189499.aspx |title=Transact-SQL Syntax Conventions |accessdate= |author=Microsoft ] [cite web |url=http://dev.mysql.com/doc/refman/5.0/en/select.html|title=SQL SELECT Syntax |accessdate= |author=MySQL]

It retrieves zero or more rows from one or more base tables, temporary tables, or views in a database. In most applications, SELECT is the most commonly used Data Manipulation Language (DML) command. As SQL is a non-procedural language, SELECT queries specify a result set, but do not specify how to calculate it: translating the query into an executable "query plan" is left to the database system, more specifically to the query optimiser.

The SELECT statement has many optional clauses:
* WHERE specifies which rows to retrieve.
* GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.
* HAVING selects among the groups defined by the GROUP BY clause.
* ORDER BY specifies an order in which to return the rows.

Examples

-
align="center"
|SELECT * FROM T WHERE C1 = 1;
align="center"
Given a table T, the "query" SELECT * FROM T will result in all the elements of all the rows of the table being shown.

With the same table, the query SELECT C1 FROM T will result in the elements from the column C1 of all the rows of the table being shown. This is similar to a "projection" in Relational algebra, except that in the general case, the result may contain duplicate rows. This is also known as a Vertical Partition in some database terms, restricting query output to view only specified fields or columns.

With the same table, the query SELECT * FROM T WHERE C1 = 1 will result in all the elements of all the rows where the value of column C1 is '1' being shown — in Relational algebra terms, a "selection" will be performed, because of the WHERE clause. This is also known as a Horizontal Partition, restricting rows output by a query according to specified conditions.

The last query SELECT * FROM T ORDER BY C1 DESC will output the same rows as the first query, however the results will be sorted in reverse order of the values in column C1 (Z-A) because of the ORDER BY clause. This query doesn't have a WHERE clause, so anything and everything will be returned. Multiple expressions can be specified in the ORDER BY clause (separated by comma [eg. ORDER BY C1 ASC, C2 DESC] ) to further refine sorting. [cite web |url=http://msdn2.microsoft.com/en-us/library/ms187731.aspx|title=SELECT Examples (Transact-SQL) |accessdate= |author=Microsoft ]

Limiting result rows

Often it is convenient to indicate a maximum number of rows that are returned. This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected. The approach to do this often varies per vendor.

In ISO , result sets may be limited by using
* cursors, or
* By introducing "SQL window function" to the SELECT-statement

ROW_NUMBER() window function

ROW_NUMBER() OVER may be used for a "simple limit" on the returned rows. E.g., to return no more than ten rows:

SELECT * FROM --emp //commented by Udhayakumar N P,([e-mail])( SELECT ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number, columns FROM tablename) AS fooWHERE row_number <= 10

--oracle versionSELECT * FROM( SELECT ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number, columns FROM tablename) fooWHERE foo.row_number <= 10

ROW_NUMBER can be non-deterministic: if "sort_key" is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where "sort_key" is the same. When "sort_key" is unique, each row will always get a unique row number.

RANK() window function

The RANK() OVER window function acts like ROW_NUMBER, but may return more than "n" rows in case of tie conditions. E.g., to return the top-10 youngest persons:

SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person) AS fooWHERE ranking <= 10

The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows.

Non-standard syntax

Result limits

Not all DBMSes support the mentioned window functions, and non-standard syntax has to be used. Below, variants of the "simple limit" query for different DBMSes are listed:

Hierarchical query

Some databases provide allow specialised syntax for hierarchical data.

Window function

A window function in is an aggregate function applied to a partition of the result set.

For example,

sum(population) OVER( PARTITION BY city )

calculates the sum of the populations of all rows having the same "city" value as the current row.

Partitions are specified using the OVER clause which modifies the aggregate. Syntax:

:: = OVER ( [ PARTITION BY , ... ] [ ORDER BY ] )

The OVER clause can partition and order the result set. Ordering is used for order-relative functions such as row_number.

References

* Horizontal & Vertical Partitioning, Microsoft SQL Server 2000 Books Online

External links

* [http://www.w3schools.com/sql/sql_select.asp W3 Schools, SQL SELECT]
* [http://wwwdvs.informatik.uni-kl.de/courses/NEDM/WS0607/Vorlesungsunterlagen/NEDM.Chapter.06.Windows_and_Query_Functions_in_SQL.pdf Windowed Tables and Window function in SQL] , Stefan DeBloch