Editing Select (SQL) (section)

== Overview ==

<code>SELECT</code> is the most common operation in SQL, called "the query". <code>SELECT</code> retrieves data from one or more [[Table (database)|table]]s, or expressions. Standard <code>SELECT</code> statements have no persistent effects on the database. Some non-standard implementations of <code>SELECT</code> can have persistent effects, such as the <code>SELECT INTO</code> syntax provided in some databases.<ref name="ms-sql-select-into">{{ cite book | chapter = Transact-SQL Reference | title = SQL Server Language Reference | series = SQL Server 2005 Books Online | publisher = Microsoft | date = 2007-09-15 | url = http://msdn.microsoft.com/en-us/library/ms188029.aspx | access-date = 2007-06-17 }}</ref>

Queries allow the user to describe desired data, leaving the [[Database management system|database management system (DBMS)]] to carry out [[query plan|planning]], [[query optimizer|optimizing]], and performing the physical operations necessary to produce that result as it chooses.

A query includes a list of columns to include in the final result, normally immediately following the <code>SELECT</code> keyword. An asterisk ("<code>*</code>") can be used to specify that the query should return all columns of all the queried tables. <code>SELECT</code> is the most complex statement in SQL, with optional keywords and clauses that include:

* The <code>[[From (SQL)|FROM]]</code> clause, which indicates the tables to retrieve data from. The <code>FROM</code> clause can include optional <code>[[Join (SQL)|JOIN]]</code> subclauses to specify the rules for joining tables.
* The <code>WHERE</code> clause includes a comparison predicate, which restricts the rows returned by the query. The <code>WHERE</code> clause eliminates all rows from the result set where the comparison predicate does not evaluate to True.
* The <code>GROUP BY</code> clause projects rows having common values into a smaller set of rows. <code>GROUP BY</code> is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The <code>WHERE</code> clause is applied before the <code>GROUP BY</code> clause.
* The <code>[[Having (SQL)|HAVING]]</code> clause includes a predicate used to filter rows resulting from the <code>GROUP BY</code> clause. Because it acts on the results of the <code>GROUP BY</code> clause, aggregation functions can be used in the <code>HAVING</code> clause predicate.
* The <code>[[Order by (SQL)|ORDER BY]]</code> clause identifies which columns to use to sort the resulting data, and in which direction to sort them (ascending or descending). Without an <code>ORDER BY</code> clause, the order of rows returned by an SQL query is undefined.
* The <code>DISTINCT</code> keyword<ref>
{{cite book
| title                 = SAS 9.4 SQL Procedure User's Guide
| date=10 July 2013 | url                   = https://books.google.com/books?id=ESjMAAAAQBAJ
| publisher             = SAS Institute
| publication-date      = 2013
| page                  = 248
| isbn                  = 9781612905686
| access-date            = 2015-10-21
| quote                 = Although the UNIQUE argument is identical to DISTINCT, it is not an ANSI standard.
}}
</ref> eliminates duplicate data.<ref>
{{cite book
| last1                 = Leon
| first1                = Alexis
| author-link1          = Alexis Leon
| last2                 = Leon
| first2                = Mathews
| year              = 1999
| chapter               = Eliminating duplicates - SELECT using DISTINCT
| title                 = SQL: A Complete Reference
| url                   = https://books.google.com/books?id=dmiPz2MMpfwC
| location              = New Delhi
| publisher             = Tata McGraw-Hill Education
| publication-date      = 2008
| page                  = 143
| isbn                  = 9780074637081
| access-date            = 2015-10-21
| quote                 = [...] the keyword DISTINCT [...] eliminates the duplicates from the result set.
}}
</ref>

The following example of a <code>SELECT</code> query returns a list of expensive books. The query retrieves all rows from the ''Book'' table in which the ''price'' column contains a value greater than 100.00. The result is sorted in ascending order by ''title''. The asterisk (*) in the ''select list'' indicates that all columns of the ''Book'' table should be included in the result set.

<syntaxhighlight lang="sql">
SELECT *
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;
</syntaxhighlight>

The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book.

<syntaxhighlight lang="sql">
    SELECT Book.title AS Title,
           count(*) AS Authors
     FROM  Book
     JOIN  Book_author
       ON  Book.isbn = Book_author.isbn
 GROUP BY Book.title;
</syntaxhighlight>

Example output might resemble the following:

 Title                  Authors
 ---------------------- -------
 SQL Examples and Guide 4
 The Joy of SQL         1
 An Introduction to SQL 2
 Pitfalls of SQL        1

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:

<syntaxhighlight lang="sql">
SELECT title,
       count(*) AS Authors
 FROM  Book
 NATURAL JOIN Book_author
 GROUP BY title;
</syntaxhighlight>

However, many{{quantify|date=October 2015}} vendors either do not support this approach, or require certain column-naming conventions for natural joins to work effectively.

SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the ''select list'' to project data, as in the following example, which returns a list of books that cost more than 100.00 with an additional ''sales_tax'' column containing a sales tax figure calculated at 6% of the ''price''.

<syntaxhighlight lang="sql">
SELECT isbn,
       title,
       price,
       price * 0.06 AS sales_tax
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;
</syntaxhighlight>

=== Subqueries ===

Queries can be nested so that the results of one query can be used in another query via a [[relational operator]] or aggregation function. A nested query is also known as a ''subquery''. 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. In the following example, the aggregation function <code>AVG</code> receives as input the result of a subquery:

<syntaxhighlight lang="sql">
SELECT isbn,
       title,
       price
 FROM  Book
 WHERE price < (SELECT AVG(price) FROM Book)
 ORDER BY title;
</syntaxhighlight>

A subquery can use values from the outer query, in which case it is known as a [[correlated subquery]].

Since 1999 the SQL standard allows WITH clauses, i.e. named subqueries often called [[common table expression]]s (named and designed after the IBM DB2 version 2 implementation; Oracle calls these [[subquery factoring]]). CTEs can also be [[recursive]] by referring to themselves; [[Hierarchical and recursive queries in SQL|the resulting mechanism]] allows tree or graph traversals (when represented as relations), and more generally [[fixpoint]] computations.

=== Derived table ===

A derived table is a subquery in a FROM clause. Essentially, the derived table is a subquery that can be selected from or joined to. Derived table functionality allows the user to reference the subquery as a table. The derived table also is referred to as an ''inline view'' or a ''select in from list''.

In the following example, the SQL statement involves a join from the initial Books table to the derived table "Sales".  This derived table captures associated book sales information using the ISBN to join to the Books table.  As a result, the derived table provides the result set with additional columns (the number of items sold and the company that sold the books):

<syntaxhighlight lang="sql">
SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
  JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
        FROM Book_Sales
        GROUP BY Company_Nm, ISBN) sales
  ON sales.isbn = b.isbn
</syntaxhighlight>