Documentation Source Text

Check-in [1c8ccd6d21]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional hyperlinks to "compound SELECT", "ORDER BY" and "LIMIT".
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:1c8ccd6d216ccdbd96d1e316338f67f11811da88
User & Date: drh 2014-01-30 11:15:56
Context
2014-01-30
14:11
Add the extended-simple-select-stmt syntax diagram. Update the documentation on SELECT statements to better explain limitations on the use of ORDER BY and LIMIT in compound SELECTs. check-in: c14dc7c23b user: drh tags: trunk
11:15
Additional hyperlinks to "compound SELECT", "ORDER BY" and "LIMIT". check-in: 1c8ccd6d21 user: drh tags: trunk
11:08
Clarification of the role of ORDER BY and LIMIT in compound SELECT statements. check-in: 7b8d43a8ef user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
....
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
....
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
....
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
....
3956
3957
3958
3959
3960
3961
3962
3963
3964
3965
3966
3967
3968
3969
3970
3971
....
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
3983
3984
3985
3986
3987
with a single column. ^The result of the expression is the value of the
only column in the first row returned by the SELECT statement. ^If the SELECT 
yields more than one result row, all rows after the first are ignored. ^If
the SELECT yields no rows, then the value of the expression is NULL.
^(The LIMIT of a scalar subquery is always 1.
Any other LIMIT value given in the SQL text is ignored.)^

<p>^All types of [SELECT] statement, including aggregate and compound SELECT
queries (queries with keywords like UNION or EXCEPT) are allowed as scalar
subqueries.

<h3>Table Column Names</h3>

<p>^A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
................................................................................
<li><p>The second form of the INSERT statement contains a SELECT statement
instead of a VALUES clause. ^A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, 
may be used in an INSERT statement of this form.

<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its default value,
or with a NULL if no default value is specified as part of the column
definition in the CREATE TABLE statement.
................................................................................
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.

<h3>Compound Select Statements
<tcl>hd_fragment compound</tcl>
<tcl>hd_keywords {compound select} {compound query}</tcl>
</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.
^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.  

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

<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}</tcl>
<h3>ORDER BY clauses</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.
In a compound SELECT statement, only the last (right-most) SELECT may have
an ORDER BY clause and that ORDER BY clause will apply across all elements of
the compound.


<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
................................................................................
  expression is used.
  <li><p>^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. 
  <li><p>^Otherwise, the [BINARY] collation sequence is used.
</ol>

<p>In a compound SELECT statement, all ORDER BY expressions are handled
as aliases for one of the result columns of the compound SELECT.
^(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.</p>

<tcl>hd_fragment limitoffset {LIMIT}</tcl>
<h3>LIMIT and OFFSET clauses</h3>

<p>The LIMIT clause is used to place an upper bound on the number of rows
returned by the entire SELECT statement.  In a compound SELECT, only the
last (right-most) SELECT may contain a LIMIT clause.  In a compound SELECT, 
the LIMIT clause applies to the entire compound, not just the final SELECT.

<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,







|







 







|







 







|






|
|







 







|







|







 







|
|







 







|
|







1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
....
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
....
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
....
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
....
3956
3957
3958
3959
3960
3961
3962
3963
3964
3965
3966
3967
3968
3969
3970
3971
....
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
3983
3984
3985
3986
3987
with a single column. ^The result of the expression is the value of the
only column in the first row returned by the SELECT statement. ^If the SELECT 
yields more than one result row, all rows after the first are ignored. ^If
the SELECT yields no rows, then the value of the expression is NULL.
^(The LIMIT of a scalar subquery is always 1.
Any other LIMIT value given in the SQL text is ignored.)^

<p>^All types of [SELECT] statement, including aggregate and [compound SELECT]
queries (queries with keywords like UNION or EXCEPT) are allowed as scalar
subqueries.

<h3>Table Column Names</h3>

<p>^A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
................................................................................
<li><p>The second form of the INSERT statement contains a SELECT statement
instead of a VALUES clause. ^A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its default value,
or with a NULL if no default value is specified as part of the column
definition in the CREATE TABLE statement.
................................................................................
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.

<h3>Compound Select Statements
<tcl>hd_fragment compound</tcl>
<tcl>hd_keywords {compound select} {compound query} {compound SELECT} {compound SELECTs}</tcl>
</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.
^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.  

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

<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>ORDER BY clauses</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.
In a [compound SELECT] statement, only the last (right-most) SELECT may have
an ORDER BY clause and that ORDER BY clause will apply across all elements of
the compound.


<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
................................................................................
  expression is used.
  <li><p>^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. 
  <li><p>^Otherwise, the [BINARY] collation sequence is used.
</ol>

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

<tcl>hd_fragment limitoffset {LIMIT}</tcl>
<h3>LIMIT and OFFSET clauses</h3>

<p>The LIMIT clause is used to place an upper bound on the number of rows
returned by the entire SELECT statement.  In a [compound SELECT], only the
last (right-most) SELECT may contain a LIMIT clause.  In a [compound SELECT], 
the LIMIT clause applies to the entire compound, not just the final SELECT.

<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,