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

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

,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
@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

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

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

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.

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

Just another variant to achieve your goal:

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

Collectives™ on Stack Overflow

Using Between Command in SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Related