Small. Fast. Reliable.
Choose any three.

SQLite里的SQL
SQL As Understood By SQLite

[Top]

SELECT

select-stmt:

syntax diagram select-stmt

common-table-expression:

compound-operator:

expr:

join-clause:

ordering-term:

result-column:

table-or-subquery:

SELECT语句是用来查询数据库的。SELECT返回的结果为零或多行固定列数的数据。SELECT语句不会对数据库做任何修改。
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. A SELECT statement does not make any changes to the database.

上面的 "select-stmt"语法图试图在一张图里展示尽可能多的SELECT语法,这是因为很多读者认为这个图非常有帮助。接下来的 "factored-select-stmt"表达了同样的语法,与上图不同的是,将上面的语法图拆分成了多个较小的语法块。
The "select-stmt" syntax diagram above attempts to show as much of the SELECT statement syntax as possible in a single diagram, because some readers find that helpful. The following "factored-select-stmt" is an alternative syntax diagrams that expresses the same syntax but tries to break the syntax down into smaller chunks.

factored-select-stmt:

注意语法图中有一些语法路径在实际中是不允许的,例如:
Note that there are paths through the syntax diagrams that are not allowed in practice. Some examples:

还有其他一些类似的语法限制都会在本文进行详细介绍。
These and other similar syntax restrictions are described in the text.

SELECT语句是SQL语言中最复杂的命令。为了使描述更易于理解,下面的一些短文通过将决定SELECT语句返回什么数据的逻辑分解为多个步奏的方式来讲解。需要特别明确的是,这么做只是纯粹为了说明理解,在实际使用中,无论SQLite还是其他SQL引擎都不会要求必须使用这样的流程活着其它特殊的流程。
The SELECT statement is the most complicated command in the SQL language. To make the description easier to follow, some of the passages below describe the way the data returned by a SELECT statement is determined as a series of steps. It is important to keep in mind that this is purely illustrative - in practice neither SQLite nor any other SQL engine is required to follow this or any other specific process.

简单SELECT处理
Simple Select Processing

"简单 SELECT" 是SELECT语句的核心内容,下面的select-coresimple-select-stmt语法图展示了 "简单 SELECT"的内容。在实际使用中,大多数的SELECT语句都是简单SELECT语句。
The core of a SELECT statement is a "simple SELECT" shown by the select-core and simple-select-stmt syntax diagrams below. In practice, most SELECT statements are simple SELECT statements.

simple-select-stmt:

syntax diagram simple-select-stmt

common-table-expression:

expr:

ordering-term:

select-core:

syntax diagram select-core

join-clause:

result-column:

table-or-subquery:

下面分四个步奏来介绍一条简单SELECT语句是如何生成结果的。
Generating the results of a simple SELECT statement is presented as a four step process in the description below:

  1. FROM 子句过程:决定简单SELECT语句的输入数据。输入数据既可以是默认的一行零列的数据(如果没有FROM子句)也可以由FROM子句来指定。
    FROM clause processing: The input data for the simple SELECT is determined. The input data is either implicitly a single row with 0 columns (if there is no FROM clause) or is determined by the FROM clause.

  2. WHERE 子句过程:使用WHERE子句表达式来过滤数据。
    WHERE clause processing: The input data is filtered using the WHERE clause expression.

  3. GROUP BY, HAVING and result-column 表达式过程: 计算结果集中的每一行数据还需要依照GROUP BY子句来聚合数据和为过滤后的输入数据集计算结果集表达式。
    GROUP BY, HAVING and result-column expression processing: The set of result rows is computed by aggregating the data according to any GROUP BY clause and calculating the result-set expressions for the rows of the filtered input dataset.

  4. DISTINCT/ALL 关键词过程:如果查询语句是一条"SELECT DISTINCT"查询,那么重复的行会从结果集中去除。
    DISTINCT/ALL keyword processing: If the query is a "SELECT DISTINCT" query, duplicate rows are removed from the set of result rows.

简单SELECT语句包含两种类型——聚合查询和非聚合查询。如果一个简单SELECT语句包含GROUP BY子句,或者在结果集中使用了一个以上的聚合函数,那么这就是一个聚合查询。相反,如果一个简单SELECT语句不包含GROUP BY子句和聚合函数,那么就是一个非聚合函数。
There are two types of simple SELECT statement - aggregate and non-aggregate queries. A simple SELECT statement is an aggregate query if it contains either a GROUP BY clause or one or more aggregate functions in the result-set. Otherwise, if a simple SELECT contains no aggregate functions or a GROUP BY clause, it is a non-aggregate query.

1. 指定输入数据(FROM子句过程)
1. Determination of input data (FROM clause processing).

简单SELECT查询使用的输入数据就是一个N行,每行M列的集合。
The input data used by a simple SELECT query is a set of N rows each M columns wide.

如果简单SELECT语句中省略了FROM子句,那么就暗示输入数据是一个一行零列的数据(N=1, M=0)。
If the FROM clause is omitted from a simple SELECT statement, then the input data is implicitly a single row zero columns wide (i.e. N=1 and M=0).

如果指定了FROM子句,那么简单SELECT查询操作的输入数据则来自于跟随在FROM关键字后面的一张或多张表或者子查询(圆括号内的SELECT语句)。简单SELECT语句中FROM后面指定的子查询会被当做一个包含子查询语句计算结果的表来使用。子查询中的每一列都根据子查询语句中对应的表达式推算出排序器类型亲和力
If a FROM clause is specified, the data on which a simple SELECT query operates comes from the one or more tables or subqueries (SELECT statements in parenthesis) specified following the FROM keyword. A subquery specified in the table-or-subquery following the FROM clause in a simple SELECT statement is handled as if it was a table containing the data returned by executing the subquery statement. Each column of the subquery has the collation sequence and affinity of the corresponding expression in the subquery statement.

如果FROM子句中只有一张表或者一个子查询,那么SELECT语句使用的输入数据就是这个表的内容或者子查询的结果。如果FROM子句中包含超过一张表或者一个子查询,那么SELECT语句操作的输入数据就是所有的表和子查询的内容连接在一起生成的一个单独的大数据集。join-operatorjoin-constraint准确介绍了如何将表和子查询中的数据结合到一起。
If there is only a single table or subquery in the FROM clause, then the input data used by the SELECT statement is the contents of the named table. If there is more than one table or subquery in FROM clause then the contents of all tables and/or subqueries are joined into a single dataset for the simple SELECT statement to operate on. Exactly how the data is combined depends on the specific join-operator and join-constraint used to connect the tables or subqueries together.

SQLite中的所有连接操作都是以左侧或右侧数据集的笛卡尔积为基础的。笛卡尔积数据集中的列是由左边数据集的所有列和右边数据集的所有列按顺序排列在一起组成的。笛卡尔积数据集中的行是由左右两边数据集中每一行的不同组合组成的。换句话说,如果左边数据集包含NleftMleft列,右边数据集包含NrightMright列,那么笛卡尔积数据集则包含Nleft×Nright行,每行包含Mleft+Mright列。
All joins in SQLite are based on the cartesian product of the left and right-hand datasets. The columns of the cartesian product dataset are, in order, all the columns of the left-hand dataset followed by all the columns of the right-hand dataset. There is a row in the cartesian product dataset formed by combining each unique combination of a row from the left-hand and right-hand datasets. In other words, if the left-hand dataset consists of Nleft rows of Mleft columns, and the right-hand dataset of Nright rows of Mright columns, then the cartesian product is a dataset of Nleft×Nright rows, each containing Mleft+Mright columns.

如果连接操作符是"CROSS JOIN", "INNER JOIN", "JOIN" 或者一个逗号 (",")并且没有ON或者USING子句,那么连接的结果就是左右两边数据集的简单笛卡尔积。如果连接操作符包含ON或USING子句,那么会按照下面这些情况来处理:
If the join-operator is "CROSS JOIN", "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, then the result of the join is simply the cartesian product of the left and right-hand datasets. If join-operator does have ON or USING clauses, those are handled according to the following bullet points:

当FROM子句中需要将两个以上的表连接到一起时,连接操作符会按照从左往右依次操作。换句话说,FROM子句(A join-op-1 B join-op-2 C)会被按照((A join-op-1 B) join-op-2 C)来计算。
When more than two tables are joined together as part of a FROM clause, the join operations are processed in order from left to right. In other words, the FROM clause (A join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).

旁注:CROSS JOIN的特殊处理。"INNER JOIN", "JOIN" 和 ","连接符之间是没有区别的,在SQLite中是完全可以进行互换的。"CROSS JOIN"连接操作可以产生与"INNER JOIN", "JOIN" 和 ","连接符相同的结果,但是在查询优化器处理上有所不同,它会阻止查询优化器在连接表时重排表。应用程序开发者可以使用CROSS JOIN操作来直接影响SELECT语句的实现算法的选择。除非希望人工控制查询优化器,否则应该避免使用CROSS JOIN。应该在应用开发初期避免使用CROSS JOIN,因为这是过早优化的行为。对CROSS JOIN的特殊处理只是SQLite的一个特性,而不是SQL标准规范中规定的。
Side note: Special handling of CROSS JOIN. There is no difference between the "INNER JOIN", "JOIN" and "," join operators. They are completely interchangeable in SQLite. The "CROSS JOIN" join operator produces the same result as the "INNER JOIN", "JOIN" and "," operators, but is handled differently by the query optimizer in that it prevents the query optimizer from reordering the tables in the join. An application programmer can use the CROSS JOIN operator to directly influence the algorithm that is chosen to implement the SELECT statement. Avoid using CROSS JOIN except in specific situations where manual control of the query optimizer is desired. Avoid using CROSS JOIN early in the development of an application as doing so is a premature optimization. The special handling of CROSS JOIN is an SQLite-specific feature and is not a part of standard SQL.

2.WHERE子句过滤
2. WHERE clause filtering.

如果制定了WHERE子句,那么WHERE表达式会作为一个布尔表达式对输入数据的每一行进行计算。只有当WHERE子句表达式计算结果为true的行才会被包含进后续处理的数据集中,WHERE子句计算结果为NULL活着false的行会从结果中排除出去。
If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing. Rows are excluded from the result if the WHERE clause evaluates to either false or NULL.

对于JOIN、INNER JOIN和CROSS JOIN来说,WHERE子句中的约束表达式和ON子句中的表达式是没有区别的。可是对于LEFT JOIN 和LEFT OUTER JOIN来说区别就非常大了。在LEFT JOIN中,为右边表格插入额外NULL行的操作是发生在ON子句处理之后的,但是实在WHERE子句之前。在ON子句中"left.x=right.y"这种形式的约束会允许右边表格对应列填入NULL的额外行进入结果集。但是如果在WHERE子句中有同样的约束,那么"right.y"中的NULL值会使表达式 "left.x=right.y"返回false,从而将这些额外的行从结果中排除。
For a JOIN or INNER JOIN or CROSS JOIN, there is no difference between a constraint expression in the WHERE clause and one in the ON clause. However, for a LEFT JOIN or LEFT OUTER JOIN, the difference is very important. In a LEFT JOIN, the extra NULL row for the right-hand table is added after ON clause processing but before WHERE clause processing. A constraint of the form "left.x=right.y" in an ON clause will therefore allow through the added all-NULL rows of the right table. But if that same constraint is in the WHERE clause a NULL in "right.y" will prevent the expression "left.x=right.y" from being true, and thus exclude that row from the output.

3.生成结果集
3. Generation of the set of result rows.

一旦来自FROM子句的数据通过了WHERE子句表达式的过滤,简单SELECT的结果集就会被计算出来了,具体如何计算决定于简单SELECT是一个聚合查询还是非聚合查询,是否指定了GROUP BY子句。
Once the input data from the FROM clause has been filtered by the WHERE clause expression (if any), the set of result rows for the simple SELECT are calculated. Exactly how this is done depends on whether the simple SELECT is an aggregate or non-aggregate query, and whether or not a GROUP BY clause was specified.

在SELECT和FROM关键词之间的表达式列表被称为结果表达式列表。如果结果表达式是特殊表达式"*"那么会以输入数据中的所有列替代这个表达式。如果表达式是一个FROM子句中的表或者子查询的别名加上".*"后缀,那么会以名字指定的表或者子查询的所有列替代这个单独的表达式。在除了结果表达式列表以外的任何上下文环境中使用"*"和"alias.*"都会导致错误。在一个没有FROM子句的简单SELECT语句的结果表达式列表中使用"*"和"alias.*"也同样会导致错误。
The list of expressions between the SELECT and FROM keywords is known as the result expression list. If a result expression is the special expression "*" then all columns in the input data are substituted for that one expression. If the expression is the alias of a table or subquery in the FROM clause followed by ".*" then all columns from the named table or subquery are substituted for the single expression. It is an error to use a "*" or "alias.*" expression in any context other than a result expression list. It is also an error to use a "*" or "alias.*" expression in a simple SELECT query that does not have a FROM clause.

简单SELECT语句返回的结果行中的列数与结果表达式列表替换完*和alias.*之后的表达式个数相同。结果的每行数据都是输入数据中的每行数据经过结果表达式列表的计算得出的,如果是一个聚合查询,那么结果对应的是一组行的计算结果。
The number of columns in the rows returned by a simple SELECT statement is equal to the number of expressions in the result expression list after substitution of * and alias.* expressions. Each result row is calculated by evaluating the expressions in the result expression list with respect to a single row of input data or, for aggregate queries, with respect to a group of rows.

4.移除重复行(DISTINCT处理)。
4. Removal of duplicate rows (DISTINCT processing).

在简单SELECT语句中SELECT关键词后可以跟随一个ALL或者DISTINCT关键词。如果简单SELECT是一个SELECT ALL,那么SELECT会返回所有的结果行。如果既没有指定ALL也没有指定DISTINCT,那么会默认指定ALL。如果简单SELECT是一个SELECT DISTINCT,那么会在结果集返回之前去处结果集中重复的行。为了检查重复行,两个NULL值会被认为相等。会按照通常规则来选择一个排序器进行字符比较。
One of the ALL or DISTINCT keywords may follow the SELECT keyword in a simple SELECT statement. If the simple SELECT is a SELECT ALL, then the entire set of result rows are returned by the SELECT. If neither ALL or DISTINCT are present, then the behavior is as if ALL were specified. If the simple SELECT is a SELECT DISTINCT, then duplicate rows are removed from the set of result rows before it is returned. For the purposes of detecting duplicate rows, two NULL values are considered to be equal. The normal rules for selecting a collation sequence to compare text values with apply.

复合SELECT语句
Compound Select Statements

可以使用UNION、 UNION ALL、 INTERSECT 或 EXCEPT 操作符将两个以上的简单 SELECT语句连接到一起构成一个复合SELECT。如下面的语法图:
Two or more simple SELECT statements may be connected together to form a compound SELECT using the UNION, UNION ALL, INTERSECT or EXCEPT operator, as shown by the following diagram:

compound-select-stmt:

syntax diagram compound-select-stmt

common-table-expression:

expr:

ordering-term:

select-core:

在复合SELECT中,所有的SELECT成员都必须返回相同列数的结果。复合SELECT的组件必须是一个简单SELECT,且不能包含ORDER BYLIMIT子句。ORDER BYLIMIT子句只能出现在整个复合SELECT的末尾处,并且要求复合的最后一个元素不能是VALUES子句。
In a compound SELECT, all the constituent SELECTs must return the same number of result columns. As the components of a compound SELECT must be simple SELECT statements, they may not contain ORDER BY or LIMIT clauses. ORDER BY and LIMIT clauses may only occur at the end of the entire compound SELECT, and then only if the final element of the compound is not a VALUES clause.

使用UNION ALL创建的复合SELECT会返回UNION ALL左边的SELECT的所有结果行和右边的SELECT的所有行。UNION操作符除了会在最终结果集中去处相同行外,其他与UNION ALL相同。INTERSECT操作符会返回左右两边SELECT结果集的交集。EXCEPT操作符会返回左边SELECT结果集中没有在右边SELECT结果集中出现的行记录。INTERSECT 和 EXECPT操作符都会在返回前去除结果集中的重复行。
A compound SELECT created using UNION ALL operator returns all the rows from the SELECT to the left of the UNION ALL operator, and all the rows from the SELECT to the right of it. The UNION operator works the same way as UNION ALL, except that duplicate rows are removed from the final result set. The INTERSECT operator returns the intersection of the results of the left and right SELECTs. The EXCEPT operator returns the subset of rows returned by the left SELECT that are not also returned by the right-hand SELECT. Duplicate rows are removed from the results of INTERSECT and EXCEPT operators before the result set is returned.

为了确定复合SELECT的结果集中的重复行,所有的NULL值会被当做相同值,且与其他非NULL值不同。选择用来比较两个文本的排序器如同左右两边SELECT语句的列在等号(=)两边一样,除非后缀一个COLLATE操作符来指定最高优先级的排序器。在复合SELECT中比较行时不会发生亲和力转换。
For the purposes of determining duplicate rows for the results of compound SELECT operators, NULL values are considered equal to other NULL values and distinct from all non-NULL values. The collation sequence used to compare two text values is determined as if the columns of the left and right-hand SELECT statements were the left and right-hand operands of the equals (=) operator, except that greater precedence is not assigned to a collation sequence specified with the postfix COLLATE operator. No affinity transformations are applied to any values when comparing rows as part of a compound SELECT.

当一个复合SELECT需要连接三个以上的简单SELECT时,会从左到右分组。换句话说如果"A"、 "B" 和 "C"都是简单SELECT语句, (A op B op C) 会按照 ((A op B) op C)来处理。
When three or more simple SELECTs are connected into a compound SELECT, they group from left to right. In other words, if "A", "B" and "C" are all simple SELECT statements, (A op B op C) is processed as ((A op B) op C).

ORDER BY 子句
The ORDER BY clause

如果一个返回多行记录的SELECT语句没有使用ORDER BY子句,那么返回行的顺序就是未定义的。相反,如果一个SELECT语句包含了一个ORDER BY 子句,那么按照ORDER BY后跟随的表达式来决定返回给用户的顺序。
If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined. Or, if a SELECT statement does have an ORDER BY clause, then the list of expressions attached to the ORDER BY determine the order in which rows are returned to the user.

在一个复合 SELECT语句中,只有最右边的简单 SELECT可以包含一个ORDER BY子句,这个ORDER BY子句可以对复合的所有元素进行排序。如果复合 SELECT的最右边的元素是一个VALUES子句,那么这个语句中不允许使用ORDER BY子句。
In a compound SELECT statement, only the last or right-most simple SELECT may have an ORDER BY clause. That ORDER BY clause will apply across all elements of the compound. If the right-most element of a compound SELECT is a VALUES clause, then no ORDER BY clause is allowed on that statement.

记录行首先按照ORDER BY列表中最左边的表达式计算结果进行排序,然后再按照左边第二个表达式来计算排序。两行记录如果ORDER BY的所有表达式计算结果都相同,那么最后返回的顺序将是不确定的。每个ORDER BY表达式后面都可以跟一个ASC(先返回较小值)或者DESC(先返回较大值)关键词。如果既没有指定ASC也没有指定DESC,记录行会默认按照升序(先返回较小值)返回。
Rows are first sorted based on the results of evaluating the left-most expression in the ORDER BY list, then ties are broken by evaluating the second left-most expression and so on. The order in which two rows for which all ORDER BY expressions evaluate to equal values are returned is undefined. Each ORDER BY expression may be optionally followed by one of the keywords ASC (smaller values are returned first) or DESC (larger values are returned first). If neither ASC or DESC are specified, rows are sorted in ascending (smaller values first) order by default.

每个ORDER BY表达式都会按照如下方式来处理:
Each ORDER BY expression is processed as follows:

  1. 如果ORDER BY表达式是一个整数常数K,那么表达式会被当做是指定了结果集中的第K列(从左往右,从1开始为列编号)。
    If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set (columns are numbered from left to right starting with 1).

  2. 如果ORDER BY表达式是一个与输出结果的其中一列的别名相同的标示符,那么表达式表示指定了这一列。
    If the ORDER BY expression is an identifier that corresponds to the alias of one of the output columns, then the expression is considered an alias for that column.

  3. 否则,如果ORDER BY表达式是其他表达式,那么会使用这个表达式的计算结果来对输出行进行排序。如果是一个简单SELECT,那么ORDER BY 可以包含任意的表达式。可是,如果是一个复合SELECT,那么ORDER BY表达式不能是输出列的别名,必须和输出列表达式一致。(最后句似乎不对,#TODO#)
    Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows. If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions. However, if the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column.

为了对记录行进行排序,数值的比较是按照比较表达式相同的方式。使用什么排序器来对两个文本值进行排序按照下面的方式决定的:
For the purposes of sorting rows, values are compared in the same way as for comparison expressions. The collation sequence used to compare two text values is determined as follows:

  1. 如果ORDER BY表达式使用COLLATE 操作符后缀指定了一个排序器,那么久使用这个指定的排序器。
    If the ORDER BY expression is assigned a collation sequence using the postfix COLLATE operator, then the specified collation sequence is used.

  2. 否则,如果ORDER BY表达式是一个表达式的别名,并且这个表达式使用COLLATE 操作符后缀分配了一个排序器,那么就使用分配给别名指向的表达式的排序器。
    Otherwise, if the ORDER BY expression is an alias to an expression that has been assigned a collation sequence using the postfix COLLATE operator, then the collation sequence assigned to the aliased expression is used.

  3. 否则,如果ORDER BY表达式是一个列或者一个列表达式别名,那么使用这一列的默认排序器。
    Otherwise, if the ORDER BY expression is a column or an alias of an expression that is a column, then the default collation sequence for the column is used.

  4. 否则,使用BINARY排序器。
    Otherwise, the BINARY collation sequence is used.

在一个复合 SELECT语句中,所有的ORDER BY表达式都使用别名来处理复合的结果列。如果ORDER BY表达式不是一个整数别名,那么SQLite会搜索最左边的SELECT的结果列来匹配名字,否则,再搜索左边第二个SELECT,直到找到。如果在所有的组成SELECT结果列中没有找到匹配的名字,那么会引发一个错误。ORDER BY中的每个元素都死分别做匹配处理的,并且可能会从不同的SELECT语句中匹配到结果。
In a compound SELECT statement, all ORDER BY expressions are handled as aliases for one of the result columns of the compound. If an ORDER BY expression is not an integer alias, then SQLite searches the left-most SELECT in the compound for a result column that matches either the second or third rules above. If a match is found, the search stops and the expression is handled as an alias for the result column that it has been matched against. Otherwise, the next SELECT to the right is tried, and so on. If no matching expression can be found in the result columns of any constituent SELECT, it is an error. Each term of the ORDER BY clause is processed separately and may be matched against result columns from different SELECT statements in the compound.

LIMIT子句
The LIMIT clause

LIMIT子句是用来设置整个SELECT语句返回记录行数的上限。
The LIMIT clause is used to place an upper bound on the number of rows returned by the entire SELECT statement.

在一个复合 SELECT语句中,只有最右边的简单 SELECT可以包含LIMIT子句。在一个复合 SELECT语句中,LIMIT适用于整个复合体,而不只是最后一个SELECT。如果最右边的简单 SELECT是一个VALUES 子句,那么则不能使用LIMIT子句。
In a compound SELECT, only the last or right-most simple SELECT may contain a LIMIT clause. In a compound SELECT, the LIMIT clause applies to the entire compound, not just the final SELECT. If the right-most simple SELECT is a VALUES clause then no LIMIT clause is allowed.

任何标量表达式都可以适用于LIMIT子句,只要计算的结果是一个整数或者可以无损的转换到一个整数即可。如果表达式计算结果是个NULL或者是其它无法无损的转换为整数的值,那么会返回一个错误。如果LIMIT表达式计算返回一个负数,那么表示对返回结果行数上限没有限制。否则,SELECT只会返回结果集中的前N行记录,N是LIMIT计算出来的。或者,如果SELECT语句在没有LIMIT子句的情况下返回少于N条记录,那么整个结果集都会返回。
Any scalar expression may be used in the LIMIT clause, so long as it evaluates to an integer or a value that can be losslessly converted to an integer. If the expression evaluates to a NULL value or any other value that cannot be losslessly converted to an integer, an error is returned. If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned. Otherwise, the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to. Or, if the SELECT statement would return less than N rows without a LIMIT clause, then the entire result set is returned.

LIMIT子句表达式可以选择附加一个可选的OFFSET子句,OFFSET子句表达式计算结果也必须是一个整数,或者可以无损的转换成整数。如果一个表达式包含一个OFFSET子句,那么SELECT子句返回结果的前M行会被忽略掉,后续的N行会返回,M和N是OFFSET和LIMIT子句分别计算得出的。或者,如果一个SELECT不使用LIMIT子句返回的结果行少于M+N行,那么前M行会被跳过,剩下的行(如果还有)会返回。如果OFFSET子句计算结果是个负数,那么结果和计算出零效果是一样的。
The expression attached to the optional OFFSET clause that may follow a LIMIT clause must also evaluate to an integer, or a value that can be losslessly converted to an integer. If an expression has an OFFSET clause, then the first M rows are omitted from the result set returned by the SELECT statement and the next N rows are returned, where M and N are the values that the OFFSET and LIMIT clauses evaluate to, respectively. Or, if the SELECT would return less than M+N rows if it did not have a LIMIT clause, then the first M rows are skipped and the remaining rows (if any) are returned. If the OFFSET clause evaluates to a negative value, the results are the same as if it had evaluated to zero.

LIMIT子句可以指定逗号分隔的两个标量表达式,来替代单独的OFFSET子句。这种情况下,第一个表达式会当做OFFSET表达式使用,第二个表达式会当做LIMIT表达式。如果第一个参数是LIMIT表达式,第二个参数是OFFSET表达式,这样是反直觉的。所以有意翻转了OFFSET和LIMIT的位置——并且尽可能的兼容其他SQL数据库系统。不管怎样,为了避免混淆,程序员应该尽量使用LIMIT子句加OFFSET关键字,而避免使用LIMIT和逗号分隔的OFFSET。
Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma. In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression. This is counter-intuitive, as when using the OFFSET clause the second of the two expressions is the OFFSET and the first the LIMIT. This reversal of the offset and limit is intentional - it maximizes compatibility with other SQL database systems. However, to avoid confusion, programmers are strongly encouraged to use the form of the LIMIT clause that uses the "OFFSET" keyword and avoid using a LIMIT clause with a comma-separated offset.

VALUES子句
The VALUES clause

"VALUES(expr-list)"短语的含义与"SELECT expr-list"是一样的。"VALUES(expr-list-1),...,(expr-list-N)"与 "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N"含义是相同的。使用二者都是一样的。两个格式都产生相同的结果,使用同样多的内存和处理时间。
The phrase "VALUES(expr-list)" means the same thing as "SELECT expr-list". The phrase "VALUES(expr-list-1),...,(expr-list-N)" means the same thing as "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N". There is no advantage to using one form over the other. Both forms yield the same result and both forms use the same amount of memory and processing time.

这里有一些语法图上没有展示的VALUE子句使用限制。
There are some restrictions on the use of a VALUES clause that are not shown on the syntax diagrams:

WITH子句
The WITH Clause

SELECT语句有一个可选的WITH 子句前缀,这可以在SELECT语句中为用户定义一个或多个通用表表达式
SELECT statements may be optionally preceded by a single WITH clause that defines one or more common table expressions for use within the SELECT statement.