Documentation Source Text

Check-in [182ba19363]
Login

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

Overview
Comment:Changes to SELECT documentation related to compound SELECT operators.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 182ba19363b1fa07351d88456bd21c723c161905
User & Date: dan 2010-09-13 18:56:58.000
Context
2010-09-14
13:25
Add <> to the list of comparison operators in datatype3.html. (check-in: 838bd2b455 user: drh tags: trunk)
2010-09-13
18:56
Changes to SELECT documentation related to compound SELECT operators. (check-in: 182ba19363 user: dan tags: trunk)
12:06
Added documentation on the SQLITE_4_BYTE_ALIGNED_MALLOC compile-time option. (check-in: dc93bef322 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843

2844
2845
2846

2847


2848




2849







2850

2851



2852
2853
2854
2855
2856
2857
2858
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</h3>

<p>^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 or more SELECTs

are connected into a compound, they group from left to right.</p>




<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

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







|
|
|
|
>
|
|
|
>
|
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
|
>
|
>
>
>







2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
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</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
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.

<p>^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 sequences to compare text
values values are determined as if the columns of the left and right-hand
SELECT statements were the left and right-hand operands of the equals (=)
operator. ^No affinity transformations are applied to any values when 
comparing rows as part of a compound SELECT. 

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

<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

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