Between Clause

  • TomThomson

    SSC Guru

    Points: 104772

    kapil_kk (6/27/2013)


    Danny Ocean (6/26/2013)


    I never used between for string operation. I also never seen this kind of condition in working scenario.

    :doze:

    Same here...

    I dont think there is any need of using BETWEEN with aplabets 😎

    I have seen far more order comparisons on strings than on numbers. I agree there is no need for using BETWEEN on strings - but there's no need for it on numbers either, and I think that as BETWEEN is asymmetric in its treatment of the two bounds the language would be better without it.

    Tom

  • perlasathish007

    Valued Member

    Points: 70

    B,BOB,CARL,D,D

    🙂

  • Toreador

    SSChampion

    Points: 11231

    L' Eomot Inversé (6/27/2013)


    I have seen far more order comparisons on strings than on numbers. I agree there is no need for using BETWEEN on strings - but there's no need for it on numbers either, and I think that as BETWEEN is asymmetric in its treatment of the two bounds the language would be better without it.

    Agreed. Ranges on strings aren't uncommon - eg list everyone whose surnames begin with the letters 'A' to 'E'. You wouldn't use BETWEEN or you'd need

    where surname between 'A' and 'EZZZZZZZZZZZZZ'

    so

    where surname >= 'A' and surname < 'F'

  • srienstr

    SSCrazy

    Points: 2251

    L' Eomot Inversé (6/27/2013)


    kapil_kk (6/27/2013)


    Danny Ocean (6/26/2013)


    I never used between for string operation. I also never seen this kind of condition in working scenario.

    :doze:

    Same here...

    I dont think there is any need of using BETWEEN with aplabets 😎

    I have seen far more order comparisons on strings than on numbers. I agree there is no need for using BETWEEN on strings - but there's no need for it on numbers either, and I think that as BETWEEN is asymmetric in its treatment of the two bounds the language would be better without it.

    Coding stored in varchar fields would be an exception there, whether it is parts numbers, medical codes or whatever else where you need to use non-numeric coding for. Medical reporting in particular has a vast number of cases where BETWEEN is perfectly appropriate


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • David Conn

    SSCertifiable

    Points: 5545

    A good addition to the recent questions using BETWEEN.

    The Answer is completely Logical to me and I don't see anything Asymmetrical about the behaviour of the Lower and Upper Bounds.

    David

  • TomThomson

    SSC Guru

    Points: 104772

    David Conn (7/2/2013)


    A good addition to the recent questions using BETWEEN.

    The Answer is completely Logical to me and I don't see anything Asymmetrical about the behaviour of the Lower and Upper Bounds.

    David

    If there wasn't an asymmetry there we would have "between 1 and 10" meaning the same as "between 10 and 1", and the whole point of some of the recent questions is that those mean different things, ie that there is an asymmetry. In other words, "between" in SQL behaves like "-" (invert the operand order and you get a different result) rather than like "+" (invert the operand order and the result is not changed).

    Tom

  • marlon.seton

    SSCrazy

    Points: 2563

    I found this question very straightforward. Agreed the case-insensitive specification was unnecessary, but it's obvious 'D.' is greater than 'D' and 'B.', 'Bob' and 'Carl' are all greater than 'B'.

    BETWEEN does have its problems, though, as noted by others already.

  • sqlnaive

    SSCoach

    Points: 17435

    It was a good question.

  • jfgoude

    SSCrazy

    Points: 2586

    good and easy question

  • Naidu PK

    SSCrazy

    Points: 2444

    If 'order by 1 ' is commented/removed, I still get same result set in different order.

    In other words wrt to performance, does Between puts the column in sorted order n get the result set? or does it check row by row?

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Naveen PK (8/5/2013)


    If 'order by 1 ' is commented/removed, I still get same result set in different order.

    In other words wrt to performance, does Between puts the column in sorted order n get the result set? or does it check row by row?

    SQL Server will NOT create a plan that first sorts the entire table and then uses the sorted list to find the matching "subset" of data.

    However, if there is an index available on the column for the BETWEEN, then SQL Server might decide to use that index to evaluate the BETWEEN quickly (by using a seek to directly locate the first matching row, and then scanning until after the last matching row). Or it might not. That depends on lots of other factors - the optimizer is a complex beast that considers a lot of options, tries to predict their cost, and then picks the one that's probably cheapest.

    Adding an ORDER BY could result in the optimizer changing from a plan that does not use the index to one that does. Or the optimizer could still choose not to use that index, and instead check all rows and then sort the matching rows.

    EDIT: Obviously, in the case of this question, there is no index available. Here, SQL Server will always scan the table, check each row, and then either sort the result if there's an ORDER BY clause, or return it unsorted otherwise.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply