Database Software

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

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.

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.)

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

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

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.

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.

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

lower_validity <= :my_date_expression
and ( :my_date_expression < upper_validity or upper_validity is null )

to write something like

:my_date_expression between inclusive lower_validity and exclusive-or-infinite upper_validity

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 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

_lower_validity_ <= :_my_date_expression_ and ( :_my_date_expression_ < _upper_validity_ or _upper_validity_ is null )

to write something like

:_my_date_expression_ between inclusive _lower_validity_ and exclusive-or-infinite _upper_validity_

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.