I was wondering for the BETWEEN operator in SQL, why is it that for letters it’s not inclusive of the second letter but for number it is inclusive of the second letter? Wouldn’t it just be simpler to establish a single rule for both?
This statement from the instructions:
Really interesting point to emphasize again:
BETWEENtwo letters is not inclusive of the 2nd letter.
BETWEENtwo numbers is inclusive of the 2nd number.
is unfortunately not true. Thank you for bringing this up, I will report this as a bug.
You are right, it would be easier to have a single, consistent rule and in fact there is a single, consistent rule -
BETWEEN operator is inclusive, always.
This is a common mistake and let me show you why this is troublesome. Let’s say that this is our schema:
CREATE TABLE IF NOT EXISTS `docs` ( `id` int(6) unsigned NOT NULL, `content` varchar(200) NOT NULL ) DEFAULT CHARSET=utf8; INSERT INTO `docs` (`id`, `content`) VALUES ('1', 'Aa'), ('2', 'B'), ('3', 'Bc'), ('4', 'C'), ('5', 'Ca'), ('6', 'Cb'), ('7', 'D'), ('8', 'De');
So we have a table
docs that has two columns -
content. There are
8 records inside this table.
Let’s execute this query:
SELECT * FROM docs WHERE content BETWEEN 'B' AND 'C';
This is the result:
id content 2 B 3 Bc 4 C
As you can see, record with
content = 'C' was included in the result. This is because the
BETWEEN operator is inclusive. But the question is - why records with content
'Cb' were not included? It’s because
'Ca' > 'C' - in my opinion this makes perfect sense, but I know that for some students this is a hard to grasp concept, so I will show another example.
Schema stays the same, but this time we will execute this query:
SELECT * FROM docs WHERE content BETWEEN 'B' AND 'Ca';
and the result is:
id content 2 B 3 Bc 4 C 5 Ca
Now we got
'C' < 'Ca'.
That makes sense! Thank you for the clarification