0

I'm using Oracle SQL Developer and after executing this command only names starting with K and L show up. Why names starting with M do not appear?

SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'M%' ORDER BY 1 DESC;

And when I execute:

SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'N%' ORDER BY 1 DESC;

K,L,M appear but names starting with N don't. BETWEEN is inclusive so what's the problem?

This works perfectly:

SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;
Improve this question

3 Answers 3

Reset to default
5

The between predicate does not imply like so you're simply comparing strings. Write this instead

-- Names with starting letters K, L, M, N
names >= 'K' AND names < 'O'

Alternatively, use regular expressions

-- Names with starting letters K, L, M
REGEXP_LIKE (names, '^[K-M]')
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@MarmiteBomber: I answered the question "K,L,M appear but names starting with N don't. BETWEEN is inclusive so what's the problem?"
Sorry, now I re-read the explaining comments and got it!
1

BETWEEN is inclusive so what's the problem?

The wildcard '%' does not work as with LIKE here and lexicographic order applies

SELECT CASE WHEN 'Mango' BETWEEN 'K%' AND 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false

SELECT CASE WHEN 'Mango' >= 'K%' AND 'Mango' <= 'M%' THEN 'true' ELSE 'false' END 
       AS result
FROM dual
--false

SELECT CASE WHEN 'Mango' >= 'K%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- true

SELECT CASE WHEN 'Mango' <= 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false

db<>fiddle demo

This works perfectly:

SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;

Rewriting in similar manner(though it is not SARGable):

SELECT DISTINCT(names) 
FROM STUDENTS 
WHERE SUBSTR(names,1,1) BETWEEN 'K' AND 'M' 
ORDER BY 1 DESC;
Improve this answer

Comments

1

Lukas explained the problem -- confusing LIKE patterns with strings. One other solution is:

WHERE SUBSTR(names, 1, 1) BETWEEN 'K' AND 'M'

Note that of the solutions mentioned, only the direct comparison:

WHERE names >= 'K' AND names < 'O'

will use an index. However, with such a broad range of values, an index may not be useful for the query anyway.

Improve this answer

4 Comments

If there is an index on names, it will definitely be used, regardless of selectivity and also regardless of which predicate is used. Since the query only selects names, everything will be read directly from the index; the table will not be accessed because it doesn't have to be. What may be different is how the predicate is used - full index scan vs. index range scan.
Did you test the index access @mathguy? Basically you are claiming Oracle can use index access (here index_ffs) even if there is a function on the indexed column name (here SUBSTR(names,1,1) ). I know that in theory this could be possible for index_ffs but my tests on 19c show full table scan.
@MarmiteBomber - I said nothing about access (which means: scan the index first, apply some filters, then access rows from the base table). I said index scan - read all the data from the index; the base table doesn't need to be accessed AT ALL. With a function like SUBSTR, Oracle needs to know the column (names) is NOT NULL; I assume that would be true in a table like students. Also, I assume there is additional data (more columns) in the table; otherwise there's no benefit to reading the data from the index (small) vs. the full table (big).
@MarmiteBomber - With these additional assumptions, I just tested, and indeed, the query will be execute via "index fast full scan". I am on Oracle 12.2, but I assume you will find the same in Oracle 19 if you set up the test correctly (as explained above).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.