Exclusive BETWEEN

William Robertson

Very often when working with ranges - especially dates - we want the value that is greater than or equal to the range start and less than the range end so that multiple ranges do not overlap, i.e.

range_start <= n < range_end

which is almost but (frustratingly) not quite a BETWEEN expression.

What I'd like would be optional inclusive and exclusive modifiers for the start and end expressions (defaulting to "inclusive" to preserve the current behaviour), allowing something like

where d between startdate and enddate exclusive

or in full,

where d between startdate inclusive and enddate exclusive

(Or of course any equivalent syntax, if for example where d between startdate and enddate exclusive was felt to be ambiguous as the exclusive keyword might look as though it applied to the entire expression.)

top.gun

That's what range_start <= n < range_end is for - where you need to deviate from BETWEEN .. AND

William Robertson

That's what range_start <= n < range_end is for - where you need to deviate from BETWEEN .. AND

Unfortunately that is not currently SQL syntax either. Perhaps it should be.

Lothar Flatz

Only agree if the modifier inclusive is kept as default. There is no sense in changing tons of code for a very small advantage. (Avoid writing a second clause in the where condition.)

William Robertson

Only agree if the modifier inclusive is kept as default. There is no sense in changing tons of code for a very small advantage. (Avoid writing a second clause in the where condition.)

Well, obviously

Mike Kutz

For DATES of form start_date <= x < end_date, this has been implemented in 12.1 as "Temporal Validity".

I'd be more interested in an index (Domain or R*Tree) that is specifically designed for temporal periods.

MK

Brad_the_Dazed

For a complex expression of 'x' and exclusive bounds, I have to currently write something like

where (complex-expression-of-x) > my_lower_value

and (complex-expression-of-x) < my_upper_value

... adding this functionality would allow:

     where (complex-expression-of-x) between  my_lower_value  exclusive  and  my_upper_value  exclusive

I'm not sure how the optimizer treats the current case, but the suggested optional exclusive would seem to make the clause simpler.

William Robertson

For a complex expression of 'x' and exclusive bounds, I have to currently write something like

where (complex-expression-of-x) > my_lower_value

and (complex-expression-of-x) < my_upper_value

... adding this functionality would allow:

     where (complex-expression-of-x) between  my_lower_value  exclusive  and  my_upper_value  exclusive

I'm not sure how the optimizer treats the current case, but the suggested optional exclusive would seem to make the clause simpler.

Agreed, there is a potential optimisation whereby the database could avoid evaluating (complex-expression-of-x) twice. At the very least, you wouldn't have to code it twice or embed the expression in an inline view etc.

Peter Hraško

I came here to suggest the same, just with one more syntactic extension...

I tend to keep my "still valid until further notice" records as having a filled in lower validity timestamp and NULL upper validity timestamp. It would be nice instead of

<em>lower_validity</em> <= :<em>my_date_expression</em><br/>and ( :<em>my_date_expression</em> < <em>upper_validity</em> or <em>upper_validity</em> is null )

to write something like

:<em>my_date_expression</em> between inclusive <em>lower_validity</em> and exclusive-or-infinite <em>upper_validity</em>

And while I know that this is exactly what temporal validity in 12c solves, the 12c's way has its drawbacks - for example, you can't use AS OF PERIOD in a trailing table in a JOIN with the :my_date_expression coming from a column from the leading table in a JOIN. Or you can't do temporal validity with AS OF PERIOD in PL/SQL. In these cases one has to fall back to the AND'd pair of comparison predicates.

Sentinel

I came here to suggest the same, just with one more syntactic extension...

I tend to keep my "still valid until further notice" records as having a filled in lower validity timestamp and NULL upper validity timestamp. It would be nice instead of

<em>lower_validity</em> <= :<em>my_date_expression</em><br/>and ( :<em>my_date_expression</em> < <em>upper_validity</em> or <em>upper_validity</em> is null )

to write something like

:<em>my_date_expression</em> between inclusive <em>lower_validity</em> and exclusive-or-infinite <em>upper_validity</em>

And while I know that this is exactly what temporal validity in 12c solves, the 12c's way has its drawbacks - for example, you can't use AS OF PERIOD in a trailing table in a JOIN with the :my_date_expression coming from a column from the leading table in a JOIN. Or you can't do temporal validity with AS OF PERIOD in PL/SQL. In these cases one has to fall back to the AND'd pair of comparison predicates.

I'm up-voting this request but I'd like to further enhance the suggested syntax by including null handling options to either EXCLUDE NULLS the default if not specified or INCLUDE NULLS to allow for open ended ranges e.g.

     WHERE <expression> BETWEEN <expression> [ INCLUSIVE | EXCLUSIVE ] [ INCLUDE NULLS | EXCLUDE NULLS ] 
                            AND <expression> [ INCLUSIVE | EXCLUSIVE ] [ INCLUDE NULLS | EXCLUDE NULLS ]

In all cases the default if not specified would be INCLUSIVE EXCLUDE NULLS which would ensure backwards compatibility with the current operation of the between operator.

With regards to the suggestion that this functionality is already delivered in 12.1 via "Temporal Validity", that's not really true as the temporal validity clause is applied to the table before any join, where, or having predicates, only supports one range dimension at time, and doesn't allow (that I'm aware of) the range validity to be dependent on joined tables.