Set Quantifiers

Set quantifiers extend spatial operators to work with multiple ranges simultaneously. They allow you to test whether a genomic position matches any or all of a set of specified ranges in a single query.

ANY 

Match if the condition holds for any of the specified ranges.

Description 

The ANY quantifier tests whether a genomic position satisfies a spatial relationship with at least one range from a provided set. It acts as a logical OR across multiple range comparisons.

This is useful for:

Filtering features that overlap any of several regions of interest
Checking membership in a set of genomic windows
Multi-region queries without complex OR clauses

Syntax 

-- With INTERSECTS
interval INTERSECTS ANY('chr1:1000-2000', 'chr1:5000-6000', 'chr2:1000-3000')

-- With CONTAINS
interval CONTAINS ANY('chr1:1500', 'chr1:2500')

-- With WITHIN
interval WITHIN ANY('chr1:0-10000', 'chr2:0-10000')

Parameters 

interval: A genomic column registered with the engine.
ranges: A comma-separated list of genomic range literals.

Return Value 

Boolean: true if the spatial condition holds for at least one of the specified ranges, false otherwise.

Examples 

Match Multiple Regions:

Find variants in any of several regions of interest:

cursor = engine.execute("""
    SELECT * FROM variants
    WHERE interval INTERSECTS ANY(
        'chr1:1000-2000',
        'chr1:5000-6000',
        'chr2:1000-3000'
    )
""")

Check Against Gene Promoters:

Find features overlapping any of a set of promoter regions:

cursor = engine.execute("""
    SELECT * FROM peaks
    WHERE interval INTERSECTS ANY(
        'chr1:11869-12869',   -- Gene A promoter
        'chr1:29554-30554',   -- Gene B promoter
        'chr1:69091-70091'    -- Gene C promoter
    )
""")

Combine with Other Filters:

Filter by multiple regions and additional criteria:

cursor = engine.execute("""
    SELECT * FROM variants
    WHERE interval INTERSECTS ANY('chr1:1000-2000', 'chr2:5000-6000')
      AND quality >= 30
      AND filter = 'PASS'
""")

Multi-Chromosome Query:

Query across different chromosomes efficiently:

cursor = engine.execute("""
    SELECT * FROM features
    WHERE interval INTERSECTS ANY(
        'chr1:100000-200000',
        'chr2:100000-200000',
        'chr3:100000-200000',
        'chrX:100000-200000'
    )
""")

Backend Compatibility 

Backend	Support	Notes
DuckDB	Full
SQLite	Full
PostgreSQL	Planned

Performance Notes 

ANY expands to multiple OR conditions in the generated SQL
For very large sets of ranges, consider using a separate table and JOIN instead
The optimizer may benefit from indexes on chromosome and position columns

ALL 

Match if the condition holds for all of the specified ranges.

Description 

The ALL quantifier tests whether a genomic position satisfies a spatial relationship with every range in a provided set. It acts as a logical AND across multiple range comparisons.

This is useful for:

Finding features that span multiple specific positions
Ensuring complete coverage of a set of points
Strict multi-point containment queries

Syntax 

-- With CONTAINS
interval CONTAINS ALL('chr1:1500', 'chr1:1600', 'chr1:1700')

-- With INTERSECTS (less common, but valid)
interval INTERSECTS ALL('chr1:1000-1100', 'chr1:1050-1150')

Parameters 

interval: A genomic column registered with the engine.
ranges: A comma-separated list of genomic range literals.

Return Value 

Boolean: true if the spatial condition holds for all of the specified ranges, false otherwise.

Examples 

Find Features Containing Multiple Points:

Find genes that contain all specified SNP positions:

cursor = engine.execute("""
    SELECT * FROM genes
    WHERE interval CONTAINS ALL(
        'chr1:1500',
        'chr1:1600',
        'chr1:1700'
    )
""")

Ensure Complete Coverage:

Find intervals that span a set of required positions:

cursor = engine.execute("""
    SELECT * FROM features
    WHERE interval CONTAINS ALL(
        'chr1:10000',
        'chr1:15000',
        'chr1:20000'
    )
""")

Find Overlapping Regions:

Find features that overlap with all specified windows (useful for finding features in the intersection of multiple regions):

cursor = engine.execute("""
    SELECT * FROM features
    WHERE interval INTERSECTS ALL(
        'chr1:1000-2000',
        'chr1:1500-2500'
    )
""")

# This finds features that overlap BOTH ranges
# (i.e., features in the intersection: chr1:1500-2000)

Backend Compatibility 

Backend	Support	Notes
DuckDB	Full
SQLite	Full
PostgreSQL	Planned

Performance Notes 

ALL expands to multiple AND conditions in the generated SQL
Queries with ALL may be more restrictive, potentially reducing result sets
Consider whether ANY might be more appropriate for your use case

Related 

ANY - Match any range (logical OR)
CONTAINS - Base containment operator

Choosing Between ANY and ALL 

Use ANY when you want to find features that match at least one of several criteria:

# Find variants in gene A OR gene B OR gene C
WHERE interval INTERSECTS ANY('gene_a_region', 'gene_b_region', 'gene_c_region')

Use ALL when you want to find features that satisfy all criteria simultaneously:

# Find features that contain ALL of these positions
WHERE interval CONTAINS ALL('pos1', 'pos2', 'pos3')

Common Patterns 

Exclusion with ANY:

Find features that don’t overlap any blacklisted region:

cursor = engine.execute("""
    SELECT * FROM peaks
    WHERE NOT interval INTERSECTS ANY(
        'chr1:1000000-2000000',  -- Centromere
        'chr1:5000000-5500000'   -- Known artifact region
    )
""")

Combining ANY and ALL:

Complex queries can combine both quantifiers:

cursor = engine.execute("""
    SELECT * FROM features
    WHERE interval INTERSECTS ANY('chr1:1000-2000', 'chr1:5000-6000')
      AND interval CONTAINS ALL('chr1:1100', 'chr1:1200')
""")