Oracle NOT BETWEEN for string comparison does not give same result as <= and >=

Question

Using Oracle 11gR2 Expression Edition. My data looks like following

ordertype
---------
ZOCO
ZOSA
ZOST

We are trying to find out records where the column is not between a certain range of values.

If I run a query with <= and >= operators: SELECT * FROM table where ordertype <= 'ZAAA' OR ordertype >= 'ZZZZ'; then I get 0 results. This is the right answer.

However, if I use NOT BETWEEN: SELECT * FROM table where ordertype NOT BETWEEN 'ZAAA' AND 'ZZZZ'; , then it gives multiple hits.

My understanding is that both syntax should give the same result but they are not. What am I missing? Reason I want to use NOT BETWEEN because a lot of our existing code already has this syntax and I do not want to change it without understanding the reasons.

Thank you.

---

Thanks for all those who posted. I ran the queries again and after fixing the "OR" in the first query, the results are the same. I still have the question of why Oracle character sorting is not recognizing it as expected, but my question which is about difference between NOT BETWEEN and <> was a false alarm. I apologize for confusion.

Answer 1

SELECT * FROM table where ordertype <= 'ZAAA' AND ordertype >= 'ZZZZ';

No string can be <= 'ZAAA' and >= 'ZZZZ'. You need to use a disjunction instead:

SELECT * FROM table where ordertype < 'ZAAA' OR ordertype > 'ZZZZ';

^{BTW, given that BETWEEN is inclusive, NOT BETWEEN is exclusive}

---

This is a common pitfall. you have to remember the De Morgan's Laws:

not (A and B) is the same as (not A) or (not B)

Feel free to experiment with this simple live example to convince yourself that those results are quite coherent: http://sqlfiddle.com/#!4/d41d8/38326

---

That being said, the only way (I can see) for the string like ZOCO for not being between ZAAA and ZZZZ would be:

• having some hidden character just behind the Z (i.e.: 'Z'||CHR(0)||'OCO')
• or using a locale such as Z-something is actually considered as a different letter, with a collation order outside of the given range. I don't know if such locale exists, but for example, in Welch, LL is considered as a single letter that should be sorted after the plain L. See http://en.wikipedia.org/wiki/Alphabetical_order#Language-specific_conventions
• or having homogplyphs such as 0, 𐒠 or О instead of O in your data.

Answer 2

If it's not between the values, it has to be either < OR >, not AND.