18.1.4 subqueries and direct comparisons - mergen-sergio/DBest GitHub Wiki

We have seen that some subqueries can be used with relational operators to return a boolean value, such as > ANY or = ALL.

The query below retrieves movies released before any movie titled Casablanca:

SELECT * FROM movie  
WHERE release_year < ANY (SELECT release_year FROM movie WHERE title = 'Casablanca');  

It is also possible to omit the ANY or ALL keywords, leaving only the relational operator.

However, in this case, the subquery must return a single value to allow direct comparison with the outer query's tuple.
If the subquery returns multiple values, the query fails with an error.

The following example demonstrates a valid direct comparison:

SELECT * FROM movie  
WHERE release_year < (SELECT MIN(release_year) FROM movie WHERE title = 'Casablanca');  

Here, the outer query retrieves movies with a release year earlier than the first recorded release year of Casablanca.

The same query can be represented in DBest as follows:

As shown, SQL enforces strict rules to prevent ambiguity in query interpretation. DBest, on the other hand, provides a flexible query language that allows for any logical arrangement of operators, as long as operator arity (nullary, unary, or binary) is respected.

Beyond this, DBest does not impose restrictions on query structure.