SELECT

SELECT [ALL | DISTINCT] result [FROM table-list]
 [WHERE expr]
 [GROUP BY expr-list [HAVING expr compound-op select]* ]
 [ORDER BY sort-expr-list]
 [LIMIT integer [( OFFSET | , ) integer]]

result = result-column [, result-column]*

result-column = * | table-name . * | expr [ [AS] string ]

table-list = table [join-op table join-args]*

table = table-name [AS alias] | ( select ) [AS alias]

join-op = , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN

join-args = [ON expr] [USING ( id-list )]

sort-expr-list = expr [sort-order] [, expr [sort-order]]*

sort-order = [ COLLATE collation-name ] [ ASC | DESC ]

compound-op = UNION | UNION ALL | INTERSECT | EXCEPT

Description:

The SELECT command queries the database. The result is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result set is specified by the expression list between the SELECT and FROM keywords. Any arbitrary expression can be used to specify the result. If the expression is “*”, then all columns of all tables are substituted for that one expression. If the expression is a table name followed by “.*”, then the result is all columns in that table.

ALL and DISTINCT keywords

By default, all result rows are returned by a SELECT statement; you can explicitly request this behavior with the keyword ALL. The DISTINCT keyword (in place of ALL) causes a subset of result rows to be returned, in which each row is different but NULL values are not treated as distinct from each other.

The query is executed against one or multiple tables specified after the FROM keyword. If multiple table names are separated by commas, then the query is executed against the cross join of the tables. The full SQL-92 join syntax can also be used to specify joins.

A subquery in parentheses may be substituted for any table name in the FROM clause. The entire clause itself may be omitted—the result is a single row containing the values of the expression list.

WHERE clause

The WHERE clause can be used to filter the rows returned in the result set. If a WHERE clause is specified, the subsequent expression is evaluated as a boolean expression for each row in the input data. Only rows for which the WHERE expression evaluates to true are included in the result.

GROUP BY clause

The GROUP BY clause causes one or more rows of the result set to be combined into a single row of output. This is especially useful when the result contains aggregate functions. The expressions in the GROUP BY clause do not have to be expressions that appear in the result.

The optional HAVING clause is similar to WHERE except that it applies after grouping has occurred. The HAVING expression may refer to values, even aggregate functions, that are not in the result.

ORDER BY clause

The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions to be used as the sorting key. For a simple SELECT, the expressions do not have to be part of the result. For a compound SELECT, each expression must match one of the result columns. Any sorting expression may be followed by the COLLATE keyword, the name of a collating function used for ordering text, and one of the ASC or DESC keywords, which specify the sorting order.

LIMIT clause

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET keyword (which follows the LIMIT keyword) specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may appear only on the final SELECT statement. The limit is applied to the entire query, not to the individual SELECT statement to which it is attached.

If the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradiction is intentional because it maximizes compatibility with legacy SQL database systems.

Compound SELECT statements

A compound SELECT statement is formed from two or more simple SELECT statements connected by one of the UNION, UNION ALL, INTERSECT, or EXCEPT operators. All the constituent statements must specify the same number of result columns. There may be only one ORDER BY clause at the end of a compound SELECT.

The UNION and UNION ALL operators combine the results of the SELECT statements to the right and the left into a single big table. The difference is that with UNION, all result rows are distinct; with UNION ALL, there may be duplicates. The INTERSECT operator takes the intersection of the results of the left and right statements. EXCEPT takes the result of the left statement after removing the result of the right statement. When three or more statements are connected in a compound statement, they group from left to right.