18.1.3 ANY and ALL - mergen-sergio/DBest GitHub Wiki

Previous
Next

Using ANY and ALL in SQL Queries

Besides IN and EXISTS, SQL also supports subqueries with ANY (or SOME) and ALL keywords.

Expressing ANY Queries Using EXISTS

Queries using ANY can be rewritten using an EXISTS-based template.
For example, the following query retrieves movies released before any movie titled Casablanca:

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

This query can be expressed using EXISTS as follows:

SELECT * FROM movie m1  
WHERE EXISTS (  
    SELECT 1 FROM movie m2  
    WHERE m2.title = 'Casablanca' AND m1.release_year < m2.release_year  
);

Queries with other comparison types (e.g., < ANY, = ANY, <> ANY) can follow this template,
adjusting the condition based on release_year.

The EXISTS-based SQL query can be represented in DBest as follows:

  • Casablanca movies are materialized.
  • From this materialized list, the movies satisfying the filter condition (m1.release_year < m2.release_year) are selected.
  • The Nested Loop Semi Join ensures that an outer tuple is accepted if at least one relevant movie is found.
Expressing an ANY-based query as an EXISTS-based query

The query plan can be adapted for different comparison types by adjusting the filtering condition based on release_year.

Expressing ALL Queries Using NOT EXISTS

Queries using ALL can also be rewritten but require a different transformation.
Consider the previous example, now using ALL instead of ANY:

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

This can be rewritten using NOT EXISTS with a reversed condition:

SELECT * FROM movie m1  
WHERE NOT EXISTS (  
    SELECT 1 FROM movie m2  
    WHERE m2.title = 'Casablanca'  
    AND (m1.release_year >= m2.release_year OR m1.release_year IS NULL OR m2.release_year IS NULL)  
);

A movie’s release_year being smaller than all Casablanca movies is the same as its release_year not being greater than or equal to any Casablanca movie.

Comparing NULL values is necessary to prevent NULLs from affecting the result.

The NOT EXISTS-based SQL query can be expressed in DBest as follows:

  • Casablanca movies are materialized.
  • From this materialized list, movies that do not satisfy the condition are identified:
    • Movies that match the reverse condition.
    • Movies containing NULL values in the condition columns.
  • The Nested Loop Anti Join ensures that an outer tuple is ignored if any irrelevant movies are found.
Expressing an ALL-based query as an EXISTS-based query

The query plan can be adapted for different comparison types by adjusting the filtering condition based on release_year.

Flexibility in Query Plan Construction

The examples provided illustrate just one way to express these queries. For instance, the last query could be optmized by:

  • Performing NULL checks once and materialize the filtered tuples.
  • Materializing the minimum release year of Casablanca movies to reduce memory usage further.

DBest offers a rich set of operators, allowing queries to be formulated in multiple ways depending on performance goals and query optimization strategies.

Previous
Next
⚠️ **GitHub.com Fallback** ⚠️