Partners
Download
BuySQLite Code Factory online Help
| Prev | Return to chapter overview | Next |
SELECT
sql-statement ::= |
SELECT [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] JOIN |
join-args ::= |
[ON expr] [USING ( id-list )] |
sort-expr-list ::= |
expr [sort-order] [, expr [sort-order]]* |
sort-order ::= |
ASC | DESC |
compound_op ::= |
UNION | UNION ALL | INTERSECT | EXCEPT |
The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result is specified by the expression list in between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. If a result expression is * then all columns of all tables are substituted for that one expression. If the expression is the name of a table followed by .* then the result is all columns in that one table.
The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the cross join of the various tables. The full SQL-92 join syntax can also be used to specify joins. A sub-query in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list.
The WHERE clause can be used to limit the number of rows over which the query operates.
The GROUP BY clauses causes one or more rows of the result 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 HAVING clause is similar to WHERE except that HAVING applies after grouping has occurred. The HAVING expression may refer to values, even aggregate functions, that are not in the result.
The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as the key for the sort. The expressions do not have to be part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each sort expression may be optionally followed by ASC or DESC to specify the sort order.
The LIMIT clause places an upper bound on the number of rows returned in the result. A LIMIT of 0 indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set.
A compound SELECT is formed from two or more simple SELECTs connected by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT. In a compound SELECT, all the constituent SELECTs must specify the same number of result columns. There may be only a single ORDER BY clause at the end of the compound SELECT. The UNION and UNION ALL operators combine the results of the SELECTs to the right and left into a single big table. The difference is that in UNION all result rows are distinct where in UNION ALL there may be duplicates. The INTERSECT operator takes the intersection of the results of the left and right SELECTs. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three are more SELECTs are connected into a compound, they group from left to right.
| Prev | Return to chapter overview | Next |
