# 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