SQLite syntax for "ALL"

Question

Is there a way to do the following in SQLite?

select name
from   table
where  number >= ALL (<subquery, "such as select x from table ...">)

Specifically, I'm getting a syntax error after the ALL operator, no matter how I format my query. In my googling for a solution, I found some mention that the syntax for ALL is different in SQLite, but I can't seem to figure it out.

Can someone show me how a basic ALL (<subquery>) statement works in SQLite? Or let me know if they don't work at all?

Answer 1

I doubt that there is an ALL() operator in SQLite. However, you can write something that is functionally equivalent using MAX() and MIN().

SELECT name 
FROM table 
WHERE number >= (SELECT MAX(another_number) FROM another_table WHERE ...)

Answer 2

SQLite does have an ALL keyword; but, it does not do what you want it to. (Fortunately, @lawrence's answer does.)

While the ALL keyword is not permitted as part of the WHERE expression, the keyword can appear a couple of other places.

From http://www.sqlite.org/lang_select.html:

SELECT ALL * ...

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.

SELECT ... UNION ALL SELECT ...

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.

Answer 3

SQLite does not have an ALL operator. You might be tempted to write something like this:

select ... where number >= (select max(...) from ...);

However, this is not equivalent to the original query in all cases. If the subquery has no results, then number is greater than all of them (vacuously true). But the maximum of zero numbers is NULL, and number >= NULL is false (or rather, unknown in ternary logic, which gets treated as false in the end).

The following query is equivalent to the one using ALL, including for the empty result case:

select ... where not exists (select * from ... where number < X);