SQL query: BETWEEN Question

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?

Please post a link to the exercise. Thanks.

1 Like

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

1 Like

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?

1 Like

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

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

1 Like

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!

2 Likes

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