SQL BETWEEN Operator

The BETWEEN operator is used with the WHERE clause to match values in a range. For example,

SELECT item, amount
FROM Orders
WHERE amount BETWEEN 300 AND 500;

Here, the SQL command selects all orders that have amounts between 300 and 500 including 300 and 500.

SQL NOT BETWEEN Operator

The NOT BETWEEN operator is used to exclude the rows that match values in the range. It returns all the rows except the excluded rows. For example,

SELECT item, amount
FROM Orders
WHERE amount NOT BETWEEN 300 AND 500;

Here, the SQL command selects all orders except the rows that have amounts between 300 and 500.

SQL BETWEEN OPERATOR With Texts

The BETWEEN operator also works with texts. For example,

SELECT item, amount
FROM Orders
WHERE item BETWEEN 'I' AND 'L';

Here, the SQL command selects all orders where the item name lies between I and L.

Here, the list of values that the above command selects that starts with L.

It's because Laptop, Lan Cable and Lamp do not lie between I and L.

If we need to include all the words that start with L as well, we can use ~ like this.

SELECT item, amount
FROM Orders
WHERE item BETWEEN 'I' AND 'L~';

Let's take another example of BETWEEN with texts.

SELECT item
FROM Orders
WHERE item BETWEEN 'Key' AND 'Mou';

Here, the SQL command selects Keyboard and Monitor, but not Mouse. It's because Mouse appears after Mou.

Recommended Reading: SQL AND, OR, and NOT Operators