No no, sorry, it's much simpler (at least in how it works, no guarantees on implementation complexity, of course). It's an issue that comes up in timeseries databases pretty often.
Say I have a table full of quotes and a table full of trades. I want to know what the quote price was at the time the trade occurred. In no-frills SQL, that translates into something like:
select * from t left outer join q on q.time=(select max(time) from q where time<=t.time and sym=s) and t.sym = q.sym where date = d and sym = s
If you have some sort of support for time proximate joining, the query engine only has to perform a binary search (as long as the indices on the symbol and time columns are appropriate, say symbol partitioned, then time sorted within symbol) to find the correct row from quote to join against. If it doesn't have such support, then a scan is required to find the maximum time value from the quote table that satisfies the constraint on the trade time. Presumably, if you do have support, this wouldn't be the exact query syntax, because that would heavily imply that you want to perform a table scan, or that you could change some aspect of the subquery without affecting performance. Maybe you'd have it be something like this:
select * from t left outer join q on before(t.time,q.time) and t.sym = q.sym where date = d and sym = s
If you just use the no-frills SQL query, we're able to optimize it to do a fast index seek (instead of scan) on q.time, because we know we only have to get the max row. This optimization isn't specific to proximate joins, a simple query like:
select max(a) from t where a < 42;
will be optimized by memsql.
