Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Exclusive BETWEEN

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