The SQL SELECT statement returns a result set of rows, from one or more tables.
In most applications, SELECT is the most commonly used data manipulation language (DML) command.
[4] Queries allow the user to describe desired data, leaving the database management system (DBMS) to carry out planning, optimizing, and performing the physical operations necessary to produce that result as it chooses.
The query retrieves all rows from the Book table in which the price column contains a value greater than 100.00.
The asterisk (*) in the select list indicates that all columns of the Book table should be included in the result set.
Example output might resemble the following: Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Book table, one could re-write the query above in the following form: However, many[quantify] vendors either do not support this approach, or require certain column-naming conventions for natural joins to work effectively.
While joins and other table operations provide computationally superior (i.e. faster) alternatives in many cases (all depending on implementation), the use of subqueries introduces a hierarchy in execution that can be useful or necessary.
CTEs can also be recursive by referring to themselves; the resulting mechanism allows tree or graph traversals (when represented as relations), and more generally fixpoint computations.
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.
This is also known as a Horizontal Partition, restricting rows output by a query according to specified conditions.
This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected.
Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is considered part of the ORDER BY clause.
More recently, vendors have added new extensions to the standard, e.g. array aggregation functions.
Method to generate data based on the union all SQL Server 2008 supports the "row constructor" feature, specified in the SQL:1999 standard