eXtremeSQL Asof Joins

eXtremeSQL supports asof joins which are normally used with date / time columns to select table rows within specified date or time ranges. The keyword asof can be used for inner joins (returns only matched rows) and outer joins (returns nulls for the inner table columns where no match is found). The syntax is as follows:

 
    outer_table o ASOF [OUTER] JOIN inner_table i 
        ON (o.k1 = i.k1 AND o.k2 = i.k2 AND ... o.kN >= i.kN)
         

The asof join requires the on clause. The on predicate must consist of one or more conjuncts with equality comparisons for columns of the joined tables and exactly one greater-than-or-equal or less-than-or-equal comparison. The equality comparison specifies a normal (strict) join condition - the same as for a normal join.

A single range comparison actually performs the asof join where the value of the outer table corresponds to the interval of the inner table column values.

For example if the outer table contains timestamps ts with values:

     
    10:01, 
            
    10:03, 
            
    10:07, 
            
    10:08  
     

and the inner table contains timestamps:

10:02, 10:04, 10:06, 10:08

then the asof join with predicate outer.ts >= inner.ts would establish the following mapping:

 
    10:01 -> null 
    10:03 -> 10:02 
    10:07 -> 10:06 
    10:08 -> 10:08
     

The asof join is implemented using a merge join; i.e. both tables are sorted and then merged. If there is a proper index, then instead of an explicit sort, an index scan is used.

Note that a join condition >= requires ascending sort order, and <= condition requires descending order.

Examples of asof join usage can be found in samples/xsql/scripts/asof_join.sql