SQL query: BETWEEN Question


#1

Hello,

In the example given for the BETWEEN operator, why are all records A up to but not including J returned. Yet with the year example all records 1990 and including 2000 returned? The two queries look to be written the same. Why with names are J not included but with year 2000 is included?


#2

Please post a link to the exercise. Thanks.


#3

https://www.codecademy.com/courses/learn-sql-queries/lessons/queries/exercises/between?action=lesson_resume&course_redirect=learn-sql


#4

All the documentation I could find says BETWEEN is inclusive of both lower and upper value, but the proof is in the pudding, for text, the upper value is exclusive. Wish I could find some explanation for this, but so far, none. Will need to do more digging. If you find a definitive answer, do please share it with us.

It doesn’t make sense, though. How does one get up to Z?


#5

I will update once I test it out a bit more, strange right?


#6

Yes, has me scratching my head (mind I am no expert).

WHERE name > 'M'

returns everything from M and beyond.

WHERE name BETWEEN 'M' AND 'Z'

does not return ‘Zombieland’ when the genre is ‘horror’ (or no genre specified).


#7

After doing some research it appears to be a much-debated SQL design flaw. Glad I learned it now before it comes up as an issue on the job!


#8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.