Documentation Source Text

        {line VALUES {loop {line ( {loop expr ,} )} ,}}
     }
     compound-operator
  }
  simple-select-stmt {
     or
        {stack

            {line SELECT {or nil DISTINCT ALL}
                                           {loop result-column ,}}
            {optx FROM {loop
                          table-or-subquery
                          {join-operator table-or-subquery join-constraint}}}
            {optx WHERE expr}
            {optx GROUP BY {loop expr ,} {optx HAVING expr}}


        }
        {line VALUES {loop {line ( {loop expr ,} )} ,}}
  }
  extended-simple-select-stmt {
    stack
      {opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}}
      {line simple-select-stmt}
      {optx ORDER BY {loop ordering-term ,}}
      {optx LIMIT expr {optx {or OFFSET ,} expr}}
  }
  compound-select-stmt {
    stack
      {opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}}
      {line simple-select-stmt {loop
                    {line {or UNION {line UNION ALL} INTERSECT EXCEPT}
                          simple-select-stmt} nil}}
      {optx ORDER BY {loop ordering-term ,}}
      {optx LIMIT expr {optx {or OFFSET ,} expr}}
  }
  table-or-subquery {
     or
       {stack
          {line
             {optx /database-name .} /table-name
             {optx {optx AS} /table-alias}

set syntax_linkage(alter-table-stmt) {column-def sql-stmt}
set syntax_linkage(analyze-stmt) {{} sql-stmt}
set syntax_linkage(attach-stmt) {expr sql-stmt}
set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(common-table-expression) {select-stmt {compound-select-stmt extended-simple-select-stmt select-stmt}}
set syntax_linkage(compound-operator) {{} select-stmt}
set syntax_linkage(compound-select-stmt) {{common-table-expression expr ordering-term simple-select-stmt} {}}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {{expr indexed-column} sql-stmt}
set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt}
set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt}
set syntax_linkage(create-view-stmt) {select-stmt sql-stmt}
set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt}
set syntax_linkage(cte-table-name) {{} {recursive-cte with-clause}}
set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited extended-simple-select-stmt insert-stmt join-constraint ordering-term result-column select-stmt simple-select-stmt table-constraint update-stmt update-stmt-limited}}
set syntax_linkage(extended-simple-select-stmt) {{common-table-expression expr ordering-term simple-select-stmt} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-constraint) {expr {select-stmt simple-select-stmt table-or-subquery}}
set syntax_linkage(join-operator) {{} {select-stmt simple-select-stmt}}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited extended-simple-select-stmt select-stmt update-stmt-limited}}
set syntax_linkage(pragma-stmt) {pragma-value sql-stmt}
set syntax_linkage(pragma-value) {signed-number pragma-stmt}
set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}}
set syntax_linkage(raise-function) {{} expr}
set syntax_linkage(recursive-cte) {cte-table-name {}}
set syntax_linkage(reindex-stmt) {{} sql-stmt}
set syntax_linkage(release-stmt) {{} sql-stmt}
set syntax_linkage(result-column) {expr {select-stmt simple-select-stmt}}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-constraint join-operator ordering-term result-column table-or-subquery} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-select-stmt) {{expr join-constraint join-operator result-column table-or-subquery} {compound-select-stmt extended-simple-select-stmt}}
set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(table-or-subquery) {{join-constraint select-stmt} {select-stmt simple-select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause cte-table-name recursive-cte common-table-expression delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt pragma-stmt pragma-value reindex-stmt select-stmt simple-select-stmt extended-simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}

<p>^The REPLACE command is an alias for the "[ON CONFLICT | INSERT OR REPLACE]"
variant of the [INSERT] command.  
This alias is provided for compatibility other SQL database engines.  See the 
[INSERT] command documentation for additional information.</p>  

<tcl>
###############################################################################
Section {WITH clause} with {{common table expressions} WITH}

RecursiveBubbleDiagram with-clause
</tcl>

<p>Common Table Expressions or CTEs act like temporary [views] that exist
only for the duration of a single SQL statement.  There are two kinds of
common table expressions: "ordinary" and "recursive". Ordinary 

<p>A recursive common table expression can be used to write a query that
walks a tree or graph.  A recursive common table expression has the same
basic syntax as an ordinary common table expression, but with the following
additional features:

<ol>
<li> The "[select-stmt]"
     must be a [compound select] where the right-most [compound-operator] is
     either UNION or UNION ALL.
<li> The table named on the left-hand side of the AS keyword must appear
     exactly once in the FROM clause of the right-most SELECT statement
     of the compound select, and nowhere else.
</ol>

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

<p>The "[select-stmt]" syntax diagram above attempts to show as much of the
SELECT statement syntax as possible in a single diagram, as some readers
find this helpful.  Alternative syntax diagrams that break the syntax down
into smaller chunks are shown elsewhere in this document.

<p>Note that there are paths through the "select-stmt" syntax diagram that
are not allowed in practice.  For example, an [ORDER BY] or [LIMIT] clause
can only occur on the right-most SELECT of a [compound select], even though
the diagram shows that they can occur anywhere.  Restrictions such as this
are described in the subsequent text.

<tcl>hd_fragment simpleselect {simple SELECT}</tcl>
<h3>Simple Select Processing</h3>

<p>The core of a SELECT statement is a "simple SELECT" shown by the
[simple-select-stmt] syntax diagram below.  
In practice, most SELECT statements are simple SELECT statements, 
though with the possible addition of an [ORDER BY] clause,
a [LIMIT] clause, and sometimes a [WITH] clause, as shown in 
the [extended-simple-select-stmt] syntax diagram.  The [ORDER BY],
[LIMIT], and [WITH] clauses are omitted from the simple-select-stmt
syntax diagram because simple-select-stmt is used in the definition of
a [compound SELECT] and those extra clauses are not allowed in that
context.

<tcl>
HiddenBubbleDiagram extended-simple-select-stmt
RecursiveBubbleDiagram simple-select-stmt
</tcl>

<p>Generating the results of a simple SELECT
statement is presented as a four step process in the description below:

<ol>
  <li> <p>[FROM clause] processing: The input data for the simple SELECT is
       determined. The input data is either implicitly a single row with 0

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.


<tcl>
hd_fragment compound {compound select} {compound query} {compound SELECT} {compound SELECTs}
</tcl>
<h3>Compound Select Statements</h3>

<p>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:

<tcl>
RecursiveBubbleDiagram compound-select-stmt
</tcl>

<p>^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.

<p>^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

<p>^(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).)^

</p>

<tcl>hd_fragment orderby {order by} {ORDER BY}</tcl>
<h3>The ORDER BY clause</h3>

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

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


<p>^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

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.</p>

<tcl>hd_fragment limitoffset {LIMIT} {OFFSET}</tcl>
<h3>The LIMIT clause</h3>

<p>The LIMIT clause is used to place an upper bound on the number of rows
returned by the entire SELECT statement.

<p>In a [compound SELECT], only the
last (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.

<p>^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

<p>^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 is intentional
- it maximizes compatibility with other SQL database systems.

<tcl>hd_fragment values {VALUES clause} VALUES</tcl>
<h3>The VALUES clause</h3>

<p>The phrase "VALUES(<i>expr-list</i>)" means the same thing
as "SELECT <i>expr-list</i>".  The phrase
"VALUES(<i>expr-list-1</i>),...,(<i>expr-list-N</i>)" means the same
thing as "SELECT <i>expr-list-1</i> UNION ALL ... UNION ALL
SELECT <i>expr-list-N</i>".  There is no advantage to using one form
over the other.  Both forms yield the same result and both forms use