From Wikipedia, the free encyclopedia

This article does not cite any references or sources. (May 2007) Please help improve this article by adding citations to reliable sources. Unverifiable material may be challenged and removed.

An SQL SELECT statement returns a result set of records from one or more tables.

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.

Contents 1 Examples 2 Limiting result rows 2.1 ROW_NUMBER() window function 2.2 RANK() window function 2.3 Non-standard syntax 3 External links

This article does not cite any references or sources. (September 2007) Please help improve this article by adding citations to reliable sources. Unverifiable material may be challenged and removed.

Table "T"	Query	Result
C1 C2 1 a 2 b	SELECT * FROM T;	C1 C2 1 a 2 b
C1 C2 1 a 2 b	SELECT C1 FROM T;	C1 1 2
C1 C2 1 a 2 b	SELECT * FROM T WHERE C1 = 1;	C1 C2 1 a
C1 C2 1 a 2 b	SELECT * FROM T ORDER BY C1 DESC;	C1 C2 2 b 1 a

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.

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.

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.

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 SQL:2003, result sets may be limited by using

• cursors, or
• By introducing SQL window function to the SELECT-statement

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 (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS row_number,
    columns
  FROM tablename
) AS foo
WHERE row_number <= 10

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

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 foo
WHERE 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.

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:

SELECT * FROM T LIMIT 10	PostgreSQL, MySQL, SQLite, H2
SELECT * from T WHERE ROWNUM <= 10	Oracle (also supports the standard, since Oracle8i)
SELECT * FROM T FETCH FIRST 10 ROWS ONLY	DB2 (also supports the standard, since DB2 v8)
SELECT TOP 10 * FROM T	MS SQL Server (also supports the standard, since SQL Server 2005), Sybase ASE, SQL_Anywhere, MS Access

• W3 Schools, SQL SELECT

v • d • e Database management systems
Database models · Database normalization · Database storage · Distributed DBMS · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Transaction processing
Concepts	Database · ACID · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key
Objects	Trigger · View · Table · Cursor · Log · Transaction · Index · Stored procedure · Partition
SQL	Select · Insert · Update · Merge · Delete · Join · Union · Create · Drop · Begin work · Commit · Rollback · Truncate · Alter
Implementations	Relational · Flat file · Deductive · Dimensional · Hierarchical · Network  · Object-oriented · Object-relational · Temporal · XML data stores
Components	Concurrency control · Query language · Query optimizer · Query plan · ODBC · JDBC
Database products: Object-oriented (comparison) · Relational (comparison)