Why does the BETWEEN command for letters differ for numbers in SQL

Question

My question is why when I input the query

SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;

includes the year 2000 however when I input the query:

SELECT * FROM movies WHERE name BETWEEN 'A' and 'J'

movies that begin with J are not included. I don't understand the inconsistency with how the between function works when looking at letters versus numbers. I understand that you could use the % to include it but I would like to know why it works this way.

maybe [**this**](http://stackoverflow.com/questions/26080187/sql-string-comparison-greater-than-and-less-than-operators) help — Juan Carlos Oropeza, May 05 '17 at 18:55
Altering the column in a `WHERE` clause loses the benefits of indexes. Use `name >= 'A' AND name < 'K'` — Stavr00, May 05 '17 at 19:03
Switching to `BETWEEN 'A' and 'J%'` will not work, it's not `LIKE` using a wildcard. — dnoeth, May 07 '17 at 09:48

Stavr00 · Accepted Answer · 2017-05-05T18:56:00.260

2

In short, because J is not equal to Jumanji. The BETWEEN A AND B keyword is equivalent to >= A AND <= B

edited May 05 '17 at 18:56

answered May 05 '17 at 18:52

Stavr00

3,219
1
16
28

1

I think you mean `J < Jumanji` Same as `A < AA` – Juan Carlos Oropeza May 05 '17 at 18:53

score 1 · Answer 2 · answered May 05 '17 at 18:55

1

It has nothing to do with SQL.
Everything starts with J (e.g. JAVA) is alphabetically greater than J itself.

answered May 05 '17 at 18:55

David דודו Markovitz

42,900
6
64
88

1

From SQL point of view there are some considerations. Like Collation or Case Sensitive. – Juan Carlos Oropeza May 05 '17 at 18:58
@JuanCarlosOropeza - Not rellevant to the OP question. You'll get the same results for all collations and cases. – David דודו Markovitz May 05 '17 at 19:06

score 0 · Answer 3 · answered May 05 '17 at 18:59

As Stavr mentions, SQL does not infer additional characters other than the literals you used.

SELECT * FROM
(
    SELECT 'A Few Good Men' As Movie
        UNION ALL 
    SELECT 'Back to the Future' As Movie
        UNION ALL 
    SELECT 'Call of the Wild' As Movie
        UNION ALL 
        SELECT 'Captain America' As Movie
        UNION ALL 
    SELECT 'D Movie' As Movie
        UNION ALL 
    SELECT 'E Movie' As Movie
        UNION ALL 
    SELECT 'F Movie' As Movie
        UNION ALL 
    SELECT 'G Movie' As Movie
        UNION ALL 
    SELECT 'H Movie' As Movie
        UNION ALL 
    SELECT 'I Movie' As Movie
        UNION ALL 
    SELECT 'J Movie' As Movie
        UNION ALL 
    SELECT 'J Movie 2' As Movie
) it 
WHERE Movie BETWEEN 'A' AND 'J%'

Change the last row to `SELECT 'Ja Movie 2' As Movie` and try again. — dnoeth, May 07 '17 at 09:39

Why does the BETWEEN command for letters differ for numbers in SQL

3 Answers3