SQL BETWEEN Operator

In this tutorial, we'll learn about the SQL BETWEEN operator with the help of examples.

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.

How to use BETWEEN Operator in SQL
Example: SQL BETWEEN Operator

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.

How to use NOT BETWEEN Operator in SQL
Example: SQL NOT BETWEEN Operator

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.

How to use BETWEEN Operator With Text in SQL
Example: SQL BETWEEN Operator With Text

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

Text Remarks
L selects
Laptop doesn't select
Lan Cable doesn't select
Lamp doesn't select

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

Did you find this article helpful?