SQL - BETWEEN operator question


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?

1 Like

Hello :slight_smile:

This statement from the instructions:

Really interesting point to emphasize again:

  • BETWEEN two letters is not inclusive of the 2nd letter.
  • BETWEEN two 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:

  `id` int(6) unsigned NOT NULL,
  `content` varchar(200) NOT NULL
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 -id and content. There are 8 records inside this table.

Let’s execute this query:

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 'Ca' and '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:

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' and 'Ca', because 'C' < 'Ca'.

1 Like

That makes sense! Thank you for the clarification :smiley:

1 Like