Forum Stats

  • 3,824,946 Users
  • 2,260,442 Discussions
  • 7,896,356 Comments

Discussions

Exclusive BETWEEN

William Robertson
William Robertson Member Posts: 9,567 Bronze Crown
edited Jan 2, 2018 12:06PM in Database Ideas - Ideas

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

Martin PreissulohmannctriebAish13pankajrangacaadecarvalhoSven W.Manish ChaturvedihimmyLothar FlatzJitendraBPeaslandDBAKiran Pawarfac586borneselChris HuntJagadekaraPkKim Berg HansenUser910243567kulikouskiApexBinetrentJohn_KFatMartinRRichard Smithglenmuser61925745c6e4cec-787c-4a56-a5ea-4a1afce715d1sensoftjnicholas330ManikMKJ10930279DavidMcWhinnieRaj JamadagnijaramillrobhendrikxBrad_the_DazedCharlesM_DaytonTony Andrews7e5b083a-0e2e-4d59-9461-c2af704dd248Walt Lu-OracleEmad Al-MousaRami_robotAbhinav B.Thorsten KettnerDanilo PiazzalungaPeter Hraškob51f47ad-aa0c-41a6-a081-0bea45bb3910GregVLoïc Lefèvre-OracleSentinelSimon MooreErik van RoonNiels Heckerrober584812sdstuber
61 votes

Active · Last Updated

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

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

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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.

    ApexBineSentinel
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

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

    ApexBine5c6e4cec-787c-4a56-a5ea-4a1afce715d1
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Jan 19, 2016 1:04PM

    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
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Oct 12, 2016 5:13PM

    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

    Sven W.
  • 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 RobertsonCharlesM_DaytonThorsten Kettner
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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
    Peter Hraško Member Posts: 13 Red Ribbon
    edited Jul 12, 2018 8:33AM

    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.

    Sven W.Sentinel
  • Sentinel
    Sentinel Member Posts: 1,284 Silver Badge
    edited Sep 26, 2019 4:48PM

    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.

    William Robertson