Select (SQL): Difference between revisions

Revision as of 00:52, 12 June 2024

Latest revision as of 05:24, 13 August 2025

(10 intermediate revisions by 10 users not shown)

Line 1:

{{excessive examples|date=October 2024}}

{{trivia|date=October 2024}}

{{Short description|SQL statement that returns a result set of rows from one or more tables}}

The [[SQL]] '''SELECT''' statement returns a [[result set]] of rows, from one or more [[Table (database)|tables]].<ref>{{cite web |url=http://msdn2.microsoft.com/en-us/library/ms189499.aspx |title=Transact-SQL Syntax Conventions |author=Microsoft |date=23 May 2023 }}</ref><ref>{{cite web |url=http://dev.mysql.com/doc/refman/5.0/en/select.html|title=SQL SELECT Syntax |author=MySQL}}</ref>

The [[SQL]] '''SELECT''' statement returns a [[result set]] of rows, from one or 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.

TheA SELECT statement retrieves zero or more rows from one or more [[SQLDatabase Tables|database tables]] '''or database [[View (database)|views]]. In most applications, <code>SELECT'''</code> statementis the most commonly used [[data manipulation language]] (DML) command. As SQL returnsis a [[resultdeclarative setprogramming]] oflanguage, rows<code>SELECT</code> queries specify a result set, frombut onedo ornot 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:

Line 7 ⟶ 12:

* <code>[[Alias (SQL)|AS]]</code> optionally provides an alias for each column or expression in the <code>SELECT</code> list. This is the relational algebra [[Rename_(relational_algebra)|rename]] operation.

* <code>[[From (SQL)|FROM]]</code> specifies from which table to get the data.<ref>Omitting FROM clause is not standard, but allowed by most major DBMSes.</ref>

* <code>[[Where (SQL)|WHERE]]</code> specifies which rows to retrieve. This is approximately the relational algebra [[Selection_(relational_algebra)|selection]] operation.

* <code>[[Group by (SQL)|GROUP BY]]</code> groups rows sharing a property so that an [[aggregate function]] can be applied to each group.

* <code>[[Having (SQL)|HAVING]]</code> selects among the groups defined by the GROUP BY clause.

Line 20 ⟶ 25:

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 table(s)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 (SQL)|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 column[s]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

Line 254 ⟶ 259:

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

<syntaxhighlight lang="sqltsql" highlight="3">

SELECT * FROM

( SELECT

Line 265 ⟶ 270:

</syntaxhighlight>

{{mono|ROW_NUMBER}} can be [[Nondeterministic algorithm|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 <code>RANK() OVER</code> window function acts like {{mono|ROW_NUMBER}}, but may return more or less than ''n'' rows in case of tie conditions, e.g. to return the top-10 youngest persons:

<syntaxhighlight lang="sqltsql" highlight="3">

SELECT * FROM (

SELECT

Line 704 ⟶ 709:

|7= the HAVING clause is evaluated for groups for which the HAVING clause is true and inserted into vTable6. For example:

<syntaxhighlight lang="postgresql">

select g.GroupName, count(g.*) as NumberOfMembers

select relational).

from users u inner join groups g on g.Userid = u.Userid

group by GroupName

having count(g.*) > 5

</syntaxhighlight>

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

}}