Using Between Command in SQL

Question

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

between matches values exactly - not just the first character. Check into like which allows wildcards. — Jerry Jeremiah
– Jerry Jeremiah, Commented Oct 20, 2015 at 1:56
@JerryJeremiah even without using wild cards ,its working fine. — VijayIndia
– VijayIndia, Commented Oct 20, 2015 at 2:01

Gordon Linoff · Accepted Answer · 2015-10-20 02:01:13Z

1

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

answered Oct 20, 2015 at 2:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VijayIndia Over a year ago

,If it matches both endpoints ,then the output of Strings must be "Black and Clue" right ,but only "Black" is the output. but if u can ,explain me whats the reason for this to happen

Alex Over a year ago

@VijayIndia Gordon already explained that b < ba < black < c < ca < canyletters so you can do experiment BETWEEN 'b' AND 'cz' just to understand which values are greater than and which are less than

Honeyboy Wilson · Accepted Answer · 2015-10-20 02:07:22Z

0

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';

answered Oct 20, 2015 at 2:07

Honeyboy Wilson

1853 silver badges6 bronze badges

Comments

PK20 · Accepted Answer · 2015-10-20 02:19:17Z

0

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.

answered Oct 20, 2015 at 2:19

PK20

1,0669 silver badges19 bronze badges

Comments

Alex · Accepted Answer · 2015-10-20 02:28:20Z

0

Just another variant to achieve your goal:

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

answered Oct 20, 2015 at 2:28

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Collectives™ on Stack Overflow

Using Between Command in SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related