1

Please inform me about the difference between

SELECT *
FROM mytable
WHERE mydate BETWEEN mindate AND maxdate

and

SELECT *
FROM mytable
WHERE mydate >= mindate
AND mydate <= maxdate

I know it has some indexing implications but not exactly sure what, perhaps also other things?

Can you guys teach me the difference or give me some pointers? I did a quick search for this on SO, but couldn't find any answer, maybe I'm on the wrong StackExchange site?

dadde
  • 649
  • 1
  • 11
  • 24
  • 4
    They should be exactly the same, even in terms of indexing (assuming that all three columns are of the same type). If there are differences they would be highly database specific, and you don't mention a database. – Gordon Linoff Jan 25 '17 at 15:18
  • @GordonLinoff added tsql as database specification – dadde Jan 25 '17 at 15:20
  • 1
    If you believe that two queries are different, one of the first things you should do is generate *execution plans*. Even if they're textually different, if they're generating *identical* plans then they mean the same thing. As Gordon said, in this case they should be the same. – Damien_The_Unbeliever Jan 25 '17 at 15:23
  • 1
    [What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – Tim Schmelter Jan 25 '17 at 15:23
  • 1
    Caveat to the above - if they generate identical plans then they're the same. If they generate different plans, that doesn't *guarantee* that they're logically different. In some complex circumstances, you may do enough to confuse the optimizer even for two queries which are logically equivalent. But for simple queries such as the above, the optimizer should usually be able to produce identical plans. – Damien_The_Unbeliever Jan 25 '17 at 15:25
  • @Damien_The_Unbeliever ok thanks, did that and the plans look exactly the same – dadde Jan 25 '17 at 15:37

0 Answers0