CODEWITHSUNDEEP
mysqlsqldatabase
VijayIndia
VijayIndia

Reputation: 91

Using Between Command in SQL

I have the following table

drink_name........cost........calories

Black.............1...........30

Clue..............2...........40

Elephant----------3...........50

When I use the between command for characters (it excludes ending positions) 

select drink_name from drink_info where drink_name between 'B' and 'C';

output:
Black

Note that Clue is omitted.

Now when using between for number comparison (it includes the ending position)

select drink_name from drink_info where cost between 1 and 3

Output:

1
2
3

Note that 3 is included.

Why is there a behaviour difference of the between keyword between integer and characters, because it includes the last number (3) whereas last character (Clue) is excluded

Upvotes: 0

Views: 97

Answers (4)

Alex
Alex

Reputation: 17289

Just another variant to achieve your goal:

SELECT drink_name 
FROM drink_info 
WHERE LEFT(drink_name,1) BETWEEN 'B' and 'C';

Upvotes: 0

PK20
PK20

Reputation: 1066

The difference is because of the characters following 'C'. To test it out create a drink name in your table called 'C' and execute your query and the output will have C in it.

When you used Between against integer field, you specify the whole integer (and not part of it) and hence you got the result including it.

Upvotes: 0

Honeyboy Wilson
Honeyboy Wilson

Reputation: 185

If you want to choose drink names with a beginning character between B and C inclusive:

select drink_name from drink_info where left(drink_name, 1) between 'B' and 'C';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Between works in exactly the same way in both cases. It include both end points.

The difference is in how integers differ from strings (and floats and datetimes).

For this reason, it is often better to use < for the second comparison:

select drink_name
from drink_info
where drink_name >= 'b' and drink_name < 'c';

This will not include 'c'. If the second comparison were <= then 'c' would be included, but nothing else that begins with 'c'.

Upvotes: 1

Related Questions