Select (SQL)

"SELECT" redirects here. For the trade association, see SELECT (Electrical Contractors' Association of Scotland).

The SQL SELECT statement returns a result set of records from one or more tables.[1][2]

A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language (DML) command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

The SELECT statement has many optional clauses:

Contents

Examples

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

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

ISO SQL:2008 introduced the FETCH FIRST clause.

ROW_NUMBER() window function

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

SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,
    COLUMNS
  FROM tablename
) foo
WHERE 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 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.

FETCH FIRST clause

Since ISO SQL:2008 results limits can be specified as in the following example using the FETCH FIRST clause.

SELECT * FROM T FETCH FIRST 10 ROWS ONLY

This clause currently is supported by IBM DB2, Sybase SQL Anywhere, PostgreSQL, EffiProz and HSQLDB version 2.0.

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:

SELECT * FROM T LIMIT 10 OFFSET 20 Netezza, MySQL, PostgreSQL (also supports the standard, since version 8.4), SQLite, HSQLDB, H2, Vertica, Polyhedra
SELECT * from T WHERE ROWNUM <= 10 Oracle (also supports the standard, since Oracle8i)
SELECT FIRST 10 * from T Ingres
SELECT FIRST 10 * FROM T order by a Informix
SELECT SKIP 20 FIRST 10 * FROM T order by c, d Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)
SELECT TOP 10 * FROM T MS SQL Server, Sybase ASE, MS Access
SELECT TOP 10 START AT 20 * FROM T Sybase SQL Anywhere (also supports the standard, since version 9.0.1)
SELECT FIRST 10 SKIP 20 * FROM T Interbase, Firebird
SELECT * FROM T ROWS 20 TO 30 Firebird (since version 2.1)
SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY		 DB2
SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY		 DB2 (new rows are filtered after comparing with key column of table T)

Hierarchical query

Some databases provide specialised syntax for hierarchical data.

Window function

A window function in SQL:2003 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_CLAUSE> :: =
   OVER ( [ PARTITION BY <expr>, ... ]
          [ ORDER BY <expression> ] )

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

Query evaluation ANSI

The processing of a SELECT statement according to ANSI SQL would be the following:[3]

  1. SELECT g.*
FROM users u
INNER JOIN groups g ON g.Userid = u.Userid
WHERE u.LastName = 'Smith'
AND u.FirstName = 'John'
  2. the FROM clause is evaluated, a cross join or Cartesian product is produced for the first two tables in the FROM clause resulting in a virtual table as Vtable1
  3. the ON clause is evaluated for vtable1; only records which meet the join condition g.Userid = u.Userid are inserted into Vtable2
  4. If an outer join is specified, records which were dropped from vTable2 are added into VTable 3, for instance if the above query were: 
    SELECT u.*
FROM users u
LEFT JOIN groups g ON g.Userid = u.Userid
WHERE u.LastName = 'Smith'
AND u.FirstName = 'John'
    all users who did not belong to any groups would be added back into Vtable3
  5. the WHERE clause is evaluated, in this case only group information for user John Smith would be added to vTable4
  6. the GROUP BY is evaluated; if the above query were: 
    SELECT g.GroupName, count(g.*) AS NumberOfMembers
FROM users u
INNER JOIN groups g ON g.Userid = u.Userid
GROUP BY GroupName
    vTable5 would consist of members returned from vTable4 arranged by the grouping, in this case the GroupName
  7. the HAVING clause is evaluated for groups for which the HAVING clause is true and inserted into vTable6. For example: 
    SELECT g.GroupName, count(g.*) AS NumberOfMembers
FROM users u
INNER JOIN groups g ON g.Userid = u.Userid
GROUP BY GroupName
HAVING count(g.*) > 5
  8. the SELECT list is evaluated and returned as Vtable 7
  9. the DISTINCT clause is evaluated; duplicate rows are removed and returned as Vtable 8
  10. the ORDER BY clause is evaluated, ordering the rows and returning VCursor9. This is a cursor and not a table because ANSI defines a cursor as an ordered set of rows (not relational).

References

  1. ^ Microsoft. "Transact-SQL Syntax Conventions". http://msdn2.microsoft.com/en-us/library/ms189499.aspx. 
  2. ^ MySQL. "SQL SELECT Syntax". http://dev.mysql.com/doc/refman/5.0/en/select.html. 
  3. ^ Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan, Lubor Kollar, and Dejan Karka

External links

Versions
Keywords
Related