Re[2]: [sqlite] Unsupported SQL feature
> > select * from t1 where a >= ALL (select b from t2)
>
> What are these constructs suppose to do?
"""
QUANTIFIED SUBQUERIES
A quantified subquery allows several types of tests and
can use the full set of comparison operators. It has
the following general format:
value-1 {=|>|<|>=|<=|<>} {ALL|ANY|SOME} (query-1)
The comparison operator specifies how to compare value-1
to the single query column value from each subquery
result row. The ANY, ALL, SOME specifiers give the type
of match expected. ANY and SOME must match at least one row
in the subquery. ALL must match all rows in the subquery,
or the subquery must be empty (produce no rows).
"""
The next two WHERE condition are equivalent:
total_price > ALL (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MAX(total_price) FROM items
WHERE order_num = 1023)
The next two WHERE condition are equivalent also:
total_price > ANY (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MIN(total_price) FROM items
WHERE order_num = 1023)
ANY and SOME are synonyms.
The next example show real use-case.
Table: Assemblies
++--+--+
| AssemblyName | PartNumber | PartWeight |
++--+--+
| Assembly1 | 01 | 100 |
| Assembly1 | 02 | 150 |
| Assembly2 | 01 | 120 |
| ... | ... | ... |
Query: Assemblies with max total weight:
Because SQL doesn't allow nested aggregated functions
MAX(SUM(...)), it is easy for some people
write this query as:
select AssemblyName from Assemblies
group by AssemblyName
having SUM(PartWeight) >= ALL (
select SUM(PartWeight)
from T1
group by AssemblyName
)
Of course, this query may be reformulated as:
select TOP 1 AssemblyName from Assemblies
group by AssemblyName
order by SUM(PartWeight) DESC
> x all (select y from t where ...)
>
> is equivalent to
>
> not exists (select y from t where not (x y) and ...)
>
> Any and some are synonyms.
>
> x any (select y from t where ...)
>
> is equivalent to
>
> exists (select y from t where x y and ...)
Nice summary
Best regards,
Alexandermailto:[EMAIL PROTECTED]
Re: [sqlite] Unsupported SQL feature
> What are these constructs suppose to do? x all (select y from t where ...) is equivalent to not exists (select y from t where not (x y) and ...) Any and some are synonyms. x any (select y from t where ...) is equivalent to exists (select y from t where x y and ...) Any can be confusing because of its ambiguity in the English language. If I say 'Is x greater than any y in t?', almost everyone will say the answer is yes if x is greater than one or more y-values in t -- but if I say 'x is greater than any y in t', you may interpret that to mean that x is greater than every y-value in t. This has lead any and all to be deprecated for new code in some circles. Regards
Re: [sqlite] Unsupported SQL feature
On Sun, 06 Nov 2005 15:09:34 -0500, [EMAIL PROTECTED] wrote: > Alexander Kozlovsky <[EMAIL PROTECTED]> wrote: > > I just notice, SQLite don't support this type of queries: > > > > > > select * from t1 where a >= ALL (select b from t2) > > > > > > Of course, queries with ALL, ANY and SOME clauses may be reformulated, > > but this is part of SQL standard. > > What are these constructs suppose to do? [from the sql 1992 standard] 8.7 Function Specify a quantified comparison. Format ::= ::= | ::= ALL ::= SOME | ANY Syntax Rules 1) The shall be of the same degree as the result of the . 2) The data types of the values of the shall be respectively comparable to those of the columns of the . 3) The collating sequence for each pair of respective values in the is determined in the same manner as described in Subclause 8.2, "". Access Rules None. General Rules 1) Let R be the result of the and let T be the result of the . 2) The result of "R T" is derived by the application of the implied "R RT" to every row RT in T: Case: a) If T is empty or if the implied is true for every row RT in T, then "R T" is true. b) If the implied is false for at least one row RT in T, then "R T" is false. c) If the implied is true for at least one row RT in T, then "R T" is true. d) If T is empty or if the implied is false for every row RT in T, then "R T" is false. e) If "R T" is neither true nor false, then it is unknown. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Unsupported SQL feature
Alexander Kozlovsky <[EMAIL PROTECTED]> wrote: > Hi! > > I just notice, SQLite don't support this type of queries: > > > select * from t1 where a >= ALL (select b from t2) > > > Of course, queries with ALL, ANY and SOME clauses may be reformulated, > but this is part of SQL standard. > > I use SQLite for teach SQL to students, and will be very happy > if such feature appears in next version of SQLite > What are these constructs suppose to do? -- D. Richard Hipp <[EMAIL PROTECTED]>
