SQL BETWEEN Operator: How to Match a Range of Values

In SQL, the BETWEEN operator is used with the WHERE clause to match values in a range.

Example

-- select rows where the amount is between 200 and 600

SELECT item, amount
FROM Orders
WHERE amount BETWEEN 200 AND 600;

Run Code

Here, the SQL command selects the item and amount columns from the Orders table where the amount is between 200 and 600 (including 200 and 600).

SQL Between Syntax

The syntax of the SQL BETWEEN operator is:

SELECT column1, column2, ...
FROM table
WHERE column BETWEEN value1 AND value2;

Here,

• column1, column2, ... are the columns you want to filter
• table is the name of the table
• column is the name of the column where we want to specify a range of values
• BETWEEN is an operator used to specify a range of values for the column
• value1 and value2 are the lower and upper bounds of the range

For example,

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

Run Code

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

Example: SQL BETWEEN Operator

SQL NOT BETWEEN Operator

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

-- exclude rows with amount between 300 and 500

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

Run Code

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

Example: SQL NOT BETWEEN Operator

SQL BETWEEN OPERATOR With Texts

The BETWEEN operator also works with texts. For example,

-- select rows where items begin with letters between 'I' and 'L'
-- exclude all items beginning with 'L' followed by other characters

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

Run Code

Here, the SQL command selects all orders where the item names begin with letters between I and L.

Example: SQL BETWEEN Operator With Text

Notice that the endpoint of our range is the letter L. Here is how the above query selects values that begin 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 rows where items begin with letters between 'I' and 'L'
-- include all items beginning with 'L' followed by other characters

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

Run Code

Let's take another example of BETWEEN with texts.

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

Run Code

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

• This blog post was originally published at:https://www.programiz.com/

#sql  #database